H
Harry Adney via AccessMonster.com
My database, (Access 2k), has a form with several subforms, one of which
queries all of the values for a specific field in a table. I have a
calculated field that calculates the amount remaining from a given total.
So:
Company A has several transactions, each one a new record in the database.
They are allowed to spend as much as they like but can receive a cashback
up to an agreed limit. The amount of the cashback is a percentage of each
transaction, for the sake of argument, let's say 10%. If they spend 1000,
they get a cashback of 100. Their maximum cashback, over all of their
transactions will be, say 500. They could spend 1 million in one
transaction, or 50,000 over 50,000 transactions, if they want, but they
still only get a cashback of the agreed limit (500).
A subform keeps track of their spending, adding up all their spending over
each of their transactions. A calculated field works out how much the
cashback will be for each transaction, and a second calculated field, based
on a query, calculates how much cashback they have left.
Cool. But, if they spend an amount which takes them over the limit, then
calculated field, shows that they have nothing left, rather than showing
how much they have left. I think this is because the query selects all of
the records. What I seem to need is for the query to select all but the
last record if the company goes over their agreed limit (500). I hope this
lot makes sense! I can see where it's going wrong, but lack the knowledge
to work it out. For clarity(!) the following formulae show how the fields
currently work:
This is on a subform and is the total (txtTotal) for this transaction:
=[txtQuote1]+[txtQuote2]+[txtQuote3]+[txtQuote4]+[txtQuote5]
This calculates the cashback, for each transaction:
=IIf(([txtTotal])*0.1<=Forms!frmCompanies!txtMaxAssistance,Round(([txtTotal]
)*0.1,2),Forms!frmCompanies!txtMaxAssistance)
A subform on this subform uses a query to select all the records
(transactions) for this particular company and adds them all up. Another
calculated field works out the percentage cashback given so far.
I can get the fields to show the %age calculation, no problem, but across
the whole of the transactions, the total cashback will still be over the
agreed limit. This is seriously driving me nuts.
queries all of the values for a specific field in a table. I have a
calculated field that calculates the amount remaining from a given total.
So:
Company A has several transactions, each one a new record in the database.
They are allowed to spend as much as they like but can receive a cashback
up to an agreed limit. The amount of the cashback is a percentage of each
transaction, for the sake of argument, let's say 10%. If they spend 1000,
they get a cashback of 100. Their maximum cashback, over all of their
transactions will be, say 500. They could spend 1 million in one
transaction, or 50,000 over 50,000 transactions, if they want, but they
still only get a cashback of the agreed limit (500).
A subform keeps track of their spending, adding up all their spending over
each of their transactions. A calculated field works out how much the
cashback will be for each transaction, and a second calculated field, based
on a query, calculates how much cashback they have left.
Cool. But, if they spend an amount which takes them over the limit, then
calculated field, shows that they have nothing left, rather than showing
how much they have left. I think this is because the query selects all of
the records. What I seem to need is for the query to select all but the
last record if the company goes over their agreed limit (500). I hope this
lot makes sense! I can see where it's going wrong, but lack the knowledge
to work it out. For clarity(!) the following formulae show how the fields
currently work:
This is on a subform and is the total (txtTotal) for this transaction:
=[txtQuote1]+[txtQuote2]+[txtQuote3]+[txtQuote4]+[txtQuote5]
This calculates the cashback, for each transaction:
=IIf(([txtTotal])*0.1<=Forms!frmCompanies!txtMaxAssistance,Round(([txtTotal]
)*0.1,2),Forms!frmCompanies!txtMaxAssistance)
A subform on this subform uses a query to select all the records
(transactions) for this particular company and adds them all up. Another
calculated field works out the percentage cashback given so far.
I can get the fields to show the %age calculation, no problem, but across
the whole of the transactions, the total cashback will still be over the
agreed limit. This is seriously driving me nuts.