Aggregate subtraction with a few rules

[MikeLeBen]

After following all the mandatory steps in my "before posting steplist" (research, experiment, further research, frustration, further experiment, utter failure), I decided I'd ask you guys.

The task at hand is the following:

table1 AS t1 has the presWorth (currency) field;
table2 AS t2 has the payAmount (currency) field;

They both have fkCustID to relate records between each other.

I'm looking for a query that will return a calculated field with the result of the subtraction of t2.payAmount from t1.presWorth; if t1.presWorth goes down to zero, it will lookup presWorth in another record (the routine will always check if SUM(presValue) is >= to SUM(payAmount) and prompt the user to do other things if said condition is not met).

An example to break it down:

2 records in t1 each with presWorth of € 45
1 record in t2 with payAmount of 89

the query should return something like

1.'Prescription Residue' = € 1
2.'Prescription Residue' = € 0

What I have so far:

Nothing worthwhile, my attempt at the SQL is pathetic but here you go

SELECT t1.presID, t1.presMonth+'-'+t1.presYear, t1.pres, t1.DataAdded, 
(SELECT SUM(t1.presWorth-t2.payAmount) FROM Cust_Pres AS t1, Payments AS t2 WHERE t1.presID <= t1.presID AND t1.custID =t2.custID) AS 'Prescription Residue'
FROM Cust_Pres AS t1
WHERE t1.custID = x  -- x is fed by VBA
ORDER BY t1.DataAdded DESC;

 

[Guus2005]

Me thinks you don't need a complicated subquery.

Perhaps your SQL statement should be something like this:

SELECT t1.presID, t1.presMonth+'-'+t1.presYear, t1.pres, t1.DataAdded, SUM(t1.presWorth)-SUM(t2.payAmount)  AS 'Prescription Residue'
 FROM Cust_Pres AS t1, Payments AS t2 WHERE t1.presID <= t1.presID AND t1.custID =t2.custID) FROM Cust_Pres AS t1
WHERE t1.custID = x  -- x is fed by VBA
GROUP BY t1.presID, t1.presMonth+'-'+t1.presYear, t1.pres, t1.DataAdded

more or less

The result is a set of data grouped by "t1.presID, t1.presMonth+'-'+t1.presYear, t1.pres, t1.DataAdded" to sum up presWorth and payAmount.

If the result for the Prescription Residue ends up negative, people payed to much. Use another query based on this one to solve that problem. Don't put everything in one complicated query but split the problem into smaller pieces. This will give you better understanding of what you are doing.

You might want to debug the query.

HTH

 

[jzwp22]

I'm looking for a query that will return a calculated field with the result of the subtraction of t2.payAmount from t1.presWorth; if t1.presWorth goes down to zero, it will lookup presWorth in another record (

The part I am concerned with above is how do you know what record to look for when the presWorth goes down to zero? Are you actually running an update query to adjust the value of presWorth with each new prescription? I would probably just have the presWorth be fixed and just enter a new record for each additional presWorth allocation then use the sum approach discussed below.

If the ultimate goal is to find out the net funds available, then I think the best approach is to calculate the sum of all presWorths for the client as well as the sum of all payAmounts. You can then reference the difference (via a Dlookup() function) in a form where you might be entering a new prescription that is being picked up to judge whether there is enough funding to pay for that prescription. If the funding is available then allow the record to be entered. If not enough funding is avilable prevent the record from being entered until something else is done...

 

[MikeLeBen]

Thanks for your reply Guus2005, though I'm not sure I can get there so easily.

jzpw22, I agree with your approach, and am thinking that if this feature is such a pain I'll just stick with the subtraction of sumPayments from sumPresWorths and cut to the chase.

If the prescription worth doesn't cover the amount of a payment, usually clients pay the difference by cash, hence the user will be prompted what to do and possibly run some updates to make the math work.

 

[jzwp22]

If the prescription worth doesn't cover the amount of a payment, usually clients pay the difference by cash, hence the user will be prompted what to do and possibly run some updates to make the math work.

Based on the above, it sounds like you have multiple payment methods and amounts that make up a total payment. Is your table structure set up to handle that?

 

[MikeLeBen]

My payment table has a "mode" field set up for the purpose, I hope it's enough to get the job done

 

[jzwp22]

If the mode field allows you to distinguish a cash payment from payments attributable from the prescription plan then that should allow you to construct the aggregate query correctly.

I did not know if it was important to your application to relatedthe two subpayments (cash portion + prescription plan portion) to a main payment record (one main payment-to-many subpayments relationship). If you treat each payment (whether cash or prescription plan payment) as separate transactions and don't care whether the two payments were made together then it would not be an issue.