Total of previous records

[kkbharat]

Hi everyone,

I am trying to figure out a way to sumup amount of same field from previous records for customers so that I could know how much amount I have received from customers towards a particular account head and any excess amount to be transferred to different account head.



I tried using DSUM but I get circular reference error. Any suggestions as to how I can achieve this.

Thanks & regards,

Bharat

[rpeare]

are you doing this in a report? if so it's pretty easy to use the RUNNING SUM property.

If you are doing this in a query, don't. Domain functions within queries can really get bogged down especially if your tables get large. If you are looking to evaluate a total on a form I would create a custom function to do the DSUM based on the customer, order or whatever your 'base unit' is.

dsum("[AmountField]", "tblTest", "[Order_ID] = " & [forms]![formname]![Order_ID_Field])

[kkbharat]

Thanks for your response. No I am trying to do it in a
query.

[rpeare]

don't do running sums in queries! They are possible but they are extremely computing power intensive over large datasets and if you are not super familiar with the dsum function and how to correctly add criteria it can be a big pain to get working the way you want. Please have a look at the running sum ability of reports, it's far easier to manage. Alternately you could also create an aggregate query by account to show the balances in total.

[ArviLaanemets]

In case you have order date registered, something like

SELECT CustomerID, SUM(AmountField) AS SumAmount FROM tblTest WHERE OrderDate < #1/31/2019#

When you haven't order date, but Order_ID values are ordered (i.e. older Order_ID values are always less or always bigger than previous ones), then you can use them as criteria instead.

When you haven't any field for orders, which allow to decide which record from any pair is older, then you are out of luck. In relational databases the table rows are not numbered in any way (the only exception I am aware of is FoxPro, and it is not a typical relational database). The order in which table rows are displayed, is determined by primary key when no specific order is applied, or by applied order. And you can't say, that one such order is right one, and all others are only temporary - they all are egual!

[kkbharat]

I think my situation needs an explanation. I am into sales of products which attract duties and taxes for which customers make multiple payments. The duties are very small part of the total payment and customers may make one time or multiple payments towards it. If a one time payment is made then it would pose no problem, however, if multiple payments at multiple times are made then all the payment needs be progressively calculated/summed and any excess payment at the final payment of the duty will need to go towards the payment of the product purchased by the customers.

I was trying the following in my query but it is not working

STDty: DSum("[stDtyAmount]","[qryCustReceipts]","[CustID]=" & [CustID] & "AND [RcptID]<" & [RcptID])

Example with notional values:


Please note the last entry.

Please suggest the solution to my problem

Thanks

[rpeare]

what about your data structure.

If you have this set up in a properly normalized structure, you can take the total of the purchase and sum the total of all the payments in your payments received table as a subquery then link it back to your main table and get the 'remaining balance' by subtracting the total of the payment due from the total of the payments made.

[ArviLaanemets]

How do you get 10000+20000+2000 = 100000?

And as other people her did say, your database structure is is a bit weird. From your example I can see, that duty balance is calculated from some total, but you haven't said from where you read this total! When as sum of duty amounts from same tblTest, then it does change with every new entry with duty amount in it!

Normal approach would be, that you have tables e.g. tblOrders and tblOrderRows, where full order info - various totals included - is stored. And then you have table e.g. tblPaments with OrderID saved for every payment (full or partial - it's egal which one).

[kkbharat]

How do you get 10000+20000+2000 = 100000?

And as other people her did say, your database structure is is a bit weird. From your example I can see, that duty balance is calculated from some total, but you haven't said from where you read this total! When as sum of duty amounts from same tblTest, then it does change with every new entry with duty amount in it!

Normal approach would be, that you have tables e.g. tblOrders and tblOrderRows, where full order info - various totals included - is stored. And then you have table e.g. tblPaments with OrderID saved for every payment (full or partial - it's egal which one).

10000+20000+2000 <> 100000, we would expect further payments towards the product. I did not continue because my main purpose was to be able to calculate Duty payment made in part and like in Excel, where I can define the range of preceding rows and sum the values with criteria, in the same way I want to be able to calculate/sum the duty payment previously made check for balance remaining and based on result decide whether the payment would go towards duty in part or in full.

Could you tell me whether it is possible to some how sum the amount from previous records for the same field.

However your suggestion

SELECT CustomerID, SUM(AmountField) AS SumAmount FROM tblTest WHERE OrderDate < #1/31/2019#

looks like something I can try

[kkbharat]

what about your data structure.

If you have this set up in a properly normalized structure, you can take the total of the purchase and sum the total of all the payments in your payments received table as a subquery then link it back to your main table and get the 'remaining balance' by subtracting the total of the payment due from the total of the payments made.

I am sorry being a beginner I couldn't understand your recommendation. I would appreciate if somebody could suggest a way to sum the same field from previous records in a query.

[ArviLaanemets]

Could you tell me whether it is possible to some how sum the amount from previous records for the same field.

As I wrote in my 1st answer, you can query the sum of field(s) where row's date field has date less or equal (otherwise the current Duty paymet is left out) than date in current row. Probably something like (on fly)

SELECT 
t.OrderNo, 
t.ReceiptNo, 
t.ReceiptDate, 
t.RecieptAmount,
t.Purpose, 
t.PartPament, 
t.Duty, 
(SELCT SUM(00.Duty) FROM tblTest t00 WHERE t00.OrderNo = t.OrderNo) - (SELECT SUM(t0.Duty) FROM tblTest t0 WHERE t0.OrderNo = t.OrderNo AND t0.ReceiptDate <= t.ReceiptDate) As DutyBalance
FROM tblTest t
ORDER BY t.OrderNo, t.ReceiptDate

[kkbharat]

As I wrote in my 1st answer, you can query the sum of field(s) where row's date field has date less or equal (otherwise the current Duty paymet is left out) than date in current row. Probably something like (on fly)

SELECT 
t.OrderNo, 
t.ReceiptNo, 
t.ReceiptDate, 
t.RecieptAmount,
t.Purpose, 
t.PartPament, 
t.Duty, 
(SELCT SUM(00.Duty) FROM tblTest t00 WHERE t00.OrderNo = t.OrderNo) - (SELECT SUM(t0.Duty) FROM tblTest t0 WHERE t0.OrderNo = t.OrderNo AND t0.ReceiptDate <= t.ReceiptDate) As DutyBalance
FROM tblTest t
ORDER BY t.OrderNo, t.ReceiptDate

I tried going by your suggestion but since I am not yet well versed with the SQL statements I tried doing it in access query builder which has not worked as yet. I shall keep on trying but in the meantime could you please tell me how can I use the SQL statement in the brackets with the help of query builder as field expression ?

[ArviLaanemets]

I'm afraid you can't design such query in query builder - you have to type it in in SQL view.

[kkbharat]

I tried going by your suggestion but since I am not yet well versed with the SQL statements I tried doing it in access query builder which has not worked as yet. I shall keep on trying but in the meantime could you please tell me how can I use the SQL statement in the brackets with the help of query builder as field expression ?

Thank you very much. Your suggestion worked like a charm. I worked out the SQL query as follows -

SELECT ct.CustID, ct.CustName, ct.StDty, (Select sum(st1.StDty) from TrialCustomer st1 where st1.CustID = ct.CustID and ct.ID <= st1.ID) AS TotStDtyFROM TrialCustomer ct
ORDER BY ct.CustName;

with output exactly like I wanted as follows -


Thank you very much for your extremely apt recommendation. I took some time to wrap my head around it but I could solve my problem.

I thank everyone for your valuable input.

Thanks & regards,

Bharat