option button

[britesynth]

hello!


i have an option button for a field [Fully Paid] (Yes/No) in my LoanT and i want to know if it's possible for my database to automatically choose an option depending on a calculated field

(Calculated field from another query) = [Outstanding Balance]

say... if [outstanding balance] is = 0 or negative (refundable), then Yes should be On, if not it should remain No/off so that i wouldn't have to go over every record and manually choose the option

the option button is very helpful when i want to display loans which are Paid and/or still active

how do I go about this?

thank you in advance!
-jake

 

[Uncle Gizmo]

Do you mean an "option group"? In your case this would be two buttons one named "on" and one named "off". You want one of the buttons to be automatically set correctly according to the calculated field?

 

[britesynth]

its a field which data type is Yes/No and i want to set its value depending on a value from a query [Outstanding Balance]

if the value of [Outstanding Balance] is 0 or negative i want the option to be set to Yes,

Field name is Fully Paid, so if outstanding balance = 0, then [Fully Paid] will be checked

 

[Galaxiom]

In a normalized structure one would not record this value in a table since it is derived from other data in the database. Calculate it as required on any query where you need to know the status of a loan.

 

[britesynth]

hello Mr. Galaxiom

hmmmm i think you're right, thank you, been thinking about moving it in a query

let say i move it on a query

now again the only thing i need to figure out is how to toggle the [Status] from "Active" to "Fully Paid"

i'm thinking....

[sum of payments] - [actual return] = [outstanding balance]

if [outstanding balance] = 0, [satus] should be "Fully Paid"
if [outstanding balance] = negative value, [satus] should be "Please Refund"
if [outstanding balance] = > 0 [status] should be "Active"

can i use a criteria to do this?


[Status] will determine the records to display on one of my Query which will display the weekly collection, which client to be displayed (only Active loans should be displayed on this query)

BTW this is my setup:
ClientsT 1 client many Loan

LoanT 1 Loan many Payments

PaymentsT

thanks!

 

[Galaxiom]

now again the only thing i need to figure out is how to toggle the [Status] from "Active" to "Fully Paid"

i'm thinking....

[sum of payments] - [actual return] = [outstanding balance]

if [outstanding balance] = 0, [satus] should be "Fully Paid"
if [outstanding balance] = negative value, [satus] should be "Please Refund"
if [outstanding balance] = > 0 [status] should be "Active"

can i use a criteria to do this?

You certainly could do this with an update query but you have missed the main point.

Don't have a Status field in the table at all. Normal database procedure is to calculate OutstandingBalance every time you need to know the status of any loan.

If you rely on a stored Status field it will always be lagging behind the payment data until you run the query to update it. Worse still, a loan that had been flagged as Fully Paid could have a payment cancelled and might go unnoticed forever.

Personally I would display the OutstandingBalance and use Conditional Formatting to highlight it depending on the outcome.

If you want to show the status in its own field you could use one of several ways to calculate what should be shown. One of my favourites is the Format Property.

The following in the Format property of a field or control with a ControlSource as OutstandingBalance will show what you want.

"Active";"Please Refund";"Fully Paid"

 

[Libre]

If I can add, it is more efficient to use a simple check box for a YES/NO Boolean data type (either checked or not checked) than an option group with two buttons, one for YES and one for NO. I certainly find it useful to see a box either checked or unchecked for fully paid vs not, or item shipped or not, or anything like that. But I do agree that it's better to calculate the value of the checkbox when you open the form the checkbox is on, rather than having the value stored in a table. You can write a function in a code module called something like GetOutstandingBal(iCustID) or something like that. Then you supply the argument (the customer id) to the function which will then run the necessary queries and perform the calculation and will return the outstanding balance. If negative or zero, the box stays unchecked. If positive, check the box. This should run on the Open Event of the form so the checkbox will always update when the form is opened. The function can also be used strictly with code and not interacting with forms at all, whenever you just want to check the balance of a customer.

 

[britesynth]

@Galaxiom
thank you again for your reply sir

the reason why I placed it in a form ([Fully Paid] = Yes/No) the first time I tried the set up is because i use it for a query, as a criteria to display a collection schedule (criteria: False),

if a client's status is = Yes (means fully paid) it won't show up on the next collection schedule

and you're right about cancelled payments going unnoticed
(happened to me before... client cancelled 2 payments and status is still "Fully Paid" and was not billed)
this is why i'm thinking on how I can program a sort of ON/OFF switch for this which is dependent totally on the outstanding balance of a client

 

[Galaxiom]

i'm thinking on how I can program a sort of ON/OFF switch for this which is dependent totally on the outstanding balance of a client

You have the right idea now. Unless the task is utterly monumental, always work from the fundamental information every time.

New developers often underestimate how much work a computer can do in a fraction of a second. A task like calculating the current balance of hundreds of thousands of accounts seem appallingly large so they store the current balance when the job is actually trivial to the computer.

Even subtracting dozens of payments for each client from the original principal to calculate the balance every time it is required is quite manageable. It just doesn't fit with the human concept of avoiding calculating something that has been done before.

When this is realised we begin to think Access is remarkably fast. And then we discover database servers. I still remain in awe of their capabilities to manipulate and summarise data.

 

[britesynth]

If I can add, it is more efficient to use a simple check box for a YES/NO Boolean data type (either checked or not checked) than an option group with two buttons, one for YES and one for NO.

You can write a function in a code module called something like GetOutstandingBal(iCustID) or something like that. Then you supply the argument (the customer id) to the function which will then run the necessary queries and perform the calculation and will return the outstanding balance. If negative or zero, the box stays unchecked. If positive, check the box. This should run on the Open Event of the form so the checkbox will always update when the form is opened. The function can also be used strictly with code and not interacting with forms at all, whenever you just want to check the balance of a customer.

the yes/no check box was the first set up i tried, worked fine but it's within a table
actually what you're trying to explain (codes to keep box checked and unchecked automatically) is what i'm aiming for but i just don't know how to go about it
i'm still new to access and don't know much about codes

thanks!

 

[Libre]

the yes/no check box was the first set up i tried, worked fine but it's within a table
actually what you're trying to explain (codes to keep box checked and unchecked automatically) is what i'm aiming for but i just don't know how to go about it
i'm still new to access and don't know much about codes

thanks!

Access has plenty of punch right out of the box, but to fully tap into its power you'll need to learn some SQL commands and how to code in VBA. There is plenty of instruction out there - a lot of it free. If you gain a basic understanding of programming logic and data structures, you will open a new world for yourself.

 

[Galaxiom]

actually what you're trying to explain (codes to keep box checked and unchecked automatically) is what i'm aiming for but i just don't know how to go about it

Sorry but you still don't understand what I have tried to explain in my previous posts.

There should NOT be a Status checkbox in the table at all.

Use a query to calculate the OutstandBalance and hence their status EVERY time you need to know the result.

 

[britesynth]

hello sir galaxiom

i removed the check box on my table and made a query, but i encountered a problem.....

my query looks something like this (from loanQ and paymentsQ joined by LoanID, 1 loan many payments)

[Loan Folder] = 3 col LoanID,File As,Folder Number = group by

[Amount of Payment] = group by SUM

[Actual Return] = group by

[Outstanding Balance] = expression:[Actual Return]-[Sumof Amount of Payments]

[Status] = Status: IIf([Outstanding Balance]>0,"Active",IIf([Outstanding Balance]<0,"Please Refund","Fully Paid"))

everything is good until i checked on my collectiblesQ which i filtered using [Status]
only "Active" loans should be displayed

i made dummy loans which are fully paid and the problem is that now the query is only displaying 1 name for each client, i think this is caused by the group by,

without group by it should look like this (used this in the check box setup - loans to be displayed are filtered by True or False)

001 Smith, John 1 Fully Paid
002 Ten, Ben 1 Fully Paid
023 Smith, John 2 Active
026 Ten, Ben 2 Active

and when filtered:
023 Smith, John 2 Active
026 Ten, Ben 2 Active

but whenever i set the query to group by it will only show this:
(randomly it will only pick 1 name regardless of which loan folder i think)

001 Smith John 1 Fully Paid
026 Ten Ben 2 Active

this is now making my collectibles query wrong and unreliable, why is this so?

thanks!
-jake

 

[britesynth]

I think I have figured out why this is happening....

when there are NO recorded payments yet for a particular LoanID it does not show up on the query

[Actual Return] - [Sumof Amount of Payment] = [Outstanding Balance]

it does not compute the [outstanding balance] because there are no payments made yet, nothing to sum up obviously, 0 value is way different from no recorded value

my collectibles table is dependent on [Outstanding Balance]

I think I have to find another filter/criteria to use for the Loans I need to bill

 

[britesynth]

how do I tell my database that there is an existing loan (with no payments yet)
and it's [outstanding balance] should also be computed for it's [status] to be determined

 

[gemma-the-husky]

just an observation.

although you do not need to store a field in your table for a "fully paid" item, you may want a visual display on the form.

Let's say you have a field in your table called "invoice amount", and another field in another table called "payment total" (this may be the sum of payments allocated to this invoice).

Then you may have these statuses for the invoice

Unpaid
Part Paid
Fully paid
Over paid

now you could have an option group with these four alternatives, numbers 1,2,3,4.

In the current event for the record, you could compare "invoice amount" with "payment total", and depending on the answer, set the option group to the appropriate value.

Is that the sort of thing you want to do?

 

[britesynth]

hello gemma!

I can give your suggestion a try
thanks!

for now I'll wait for sir galaxiom's reply

 

[Galaxiom]

Use a subquery to select the LoanIDs that have an OutstandingBalance. It would be constructed something like this:

SELECT whatever fields you want
FROM Some_Table_Containing_LoanID
WHERE LoanID IN (a subquery that returns the just the LoanIDs that are not fully paid)

The subquery must return only the LoanID field.
The outer query can have multiple joined tables with any information you require so long as one of them contains LoanID.

I hope this helps. However, if you can't understand my explanation, please post a simple version of your database with a few fake records in the tables for Clients, Loans and Payments and I will post back with an example of an actual query.

 

[britesynth]

good day!

here's a copy of my db with just the basic tables

 

[britesynth]

Use a subquery to select the LoanIDs that have an OutstandingBalance. It would be constructed something like this:

SELECT whatever fields you want
FROM Some_Table_Containing_LoanID
WHERE LoanID IN (a subquery that returns the just the LoanIDs that are not fully paid)

The subquery must return only the LoanID field.
The outer query can have multiple joined tables with any information you require so long as one of them contains LoanID.

I hope this helps. However, if you can't understand my explanation, please post a simple version of your database with a few fake records in the tables for Clients, Loans and Payments and I will post back with an example of an actual query.

The problem is that only LoanIDs with recorded payments will appear to have an [Outstanding Balance]

LoanID which i haven't recorded any payments yet appears to have no [outstanding balance] (because [Actual Return] - [Sumof Amount of payment] = [Outstanding Balance])

so when i filter loans with [outstanding balance] and say i want to bill them... new loans with no recorded payments yet wouldn't show up on my query