Solved Running Total from Query (1 Viewer)

[CJ_London]

OK try this one

SELECT T.ID_Invoice, T.Invoice_Date, T.Supplier, T.Payment_Term, T.Invoice_Number, T.Product, T.Quantity, T.Unit_Price, t.[Quantity]*t.[Unit_Price] AS Total, Sum([TT].[Quantity]*[TT].[Unit_Price]) AS RunTot, T.Product_Category
FROM Table_Invoice AS T INNER JOIN Table_Invoice AS TT ON T.Event = TT.Event
WHERE (((T.Event)=1) AND (([tt].[invoice_date]+([tt].[ID_Invoice]/1000))<=[t].[invoice_date]+([t].[ID_Invoice]/1000)))
GROUP BY T.ID_Invoice, T.Invoice_Date, T.Supplier, T.Payment_Term, T.Invoice_Number, T.Product, T.Quantity, T.Unit_Price, t.[Quantity]*t.[Unit_Price], T.Product_Category, T.Invoice_Date, T.ID_Invoice
ORDER BY T.Invoice_Date, T.ID_Invoice;

Note that order is important - this is ordered earliest date to latest date and then by ID. You cannot have effectively a random order - even excel doesn't work like that.

Note if this is going into a report, you can do your running sum there

 

[ykurnia1]

OK so the running sum is not based on invoice?

And the other issue is that ID_Invoice is not in the same order as invoice date (last record ID 5 is dated earlier than ID's 1 and 2)

ID_Invoice is autonumber generated by Access everytime a record is entered into the database, in one invoice there is a big possibility to have 5 items listed so it means 5 records will be entered; suppose i entered bunch of invoice papers where in unsorted date condition, so i can just started to enter by picking up randomly and let the Access sort for me later on from the invoice date (which i would like to program it both ways ascending and descending and i will select it from the designview unless it is impossible to do so and must just selected one of it) Second sort option is based on the ID_Invoice, for instance one invoice has five items, from top to bottom of the invoice (written as item A, B, C, D, E) i then entered in the database from top which is A sequentially until last item E, then the ID_Invoice from A to E will be definitely increasing, then I can still choose to sort it in ascending or descending order in the query.
Hope it is clear now to make it possible in coding this situation. Thank you CJ.

 

[arnelgp]

here is the modified Query1.
view in Design view to change the Sort Order, but make sure to include Event first on the sort Order, coz the function is based on that.
when you done making changes to the sort order, Save the Query first before running.

 

[ykurnia1]

OK try this one

SELECT T.ID_Invoice, T.Invoice_Date, T.Supplier, T.Payment_Term, T.Invoice_Number, T.Product, T.Quantity, T.Unit_Price, t.[Quantity]*t.[Unit_Price] AS Total, Sum([TT].[Quantity]*[TT].[Unit_Price]) AS RunTot, T.Product_Category
FROM Table_Invoice AS T INNER JOIN Table_Invoice AS TT ON T.Event = TT.Event
WHERE (((T.Event)=1) AND (([tt].[invoice_date]+([tt].[ID_Invoice]/1000))<=[t].[invoice_date]+([t].[ID_Invoice]/1000)))
GROUP BY T.ID_Invoice, T.Invoice_Date, T.Supplier, T.Payment_Term, T.Invoice_Number, T.Product, T.Quantity, T.Unit_Price, t.[Quantity]*t.[Unit_Price], T.Product_Category, T.Invoice_Date, T.ID_Invoice
ORDER BY T.Invoice_Date, T.ID_Invoice;

Note that order is important - this is ordered earliest date to latest date and then by ID. You cannot have effectively a random order - even excel doesn't work like that.

Note if this is going into a report, you can do your running sum there

Thank you CJ, the code is now running with ascending order; to have it programmed and run correctly by selecting the sorting method in ascending or descending combination order from the Design View seems to be very challenging so far.

 

[CJ_London]

if you view the sql in the query design view, you will see the sorting order specified on the right, but the show box unticked.

if you want to show a different order (e.g. date and ID descending), you need to change the < to >

(([tt].[invoice_date]+([tt].[ID_Invoice]/1000))<=[t].[invoice_date]+([t].[ID_Invoice]/1000))

Also note the /1000 - this assumes that ID does not exceed 9999. If your ID's are likely to do that then you need to increase to 100000 or similar

 

[ykurnia1]

here is the modified Query1.
view in Design view to change the Sort Order, but make sure to include Event first on the sort Order, coz the function is based on that.
when you done making changes to the sort order, Save the Query first before running.

Great Arnelgp, it works as supposed to be; here I want to copy the modified Query1 to accommodate another event with just using one created module (here you created and named as Module1), so every event will have its own query, is it possible to do that way? When I just rename the query from Query1 to Query_Party it gave me run-time error 3265 (screenshot as attached). Also will it ok to rename the module as well? Once again thank you.

 

[arnelgp]

open the renamed query in design view. on thw Calculated running total column, put the New name of your query.

 

[ykurnia1]

if you view the sql in the query design view, you will see the sorting order specified on the right, but the show box unticked.

if you want to show a different order (e.g. date and ID descending), you need to change the < to >

(([tt].[invoice_date]+([tt].[ID_Invoice]/1000))<=[t].[invoice_date]+([t].[ID_Invoice]/1000))

Also note the /1000 - this assumes that ID does not exceed 9999. If your ID's are likely to do that then you need to increase to 100000 or similar

Yes I have tested changing the sorting order specified on the right and noted now working with ascending-ascending order (when using <) and descending-descending order (when using >), any possibility to sort in ascending-descending or descending-ascending order as these combination still incorrect by using < or >. Thank you CJ.

 

[ykurnia1]

open the renamed query in design view. on thw Calculated running total column, put the New name of your query.

Oh sorry i didn't check it there, now working as great charm, thank you so much Arnelgp.

 

[CJ_London]

the query is the view that is built either ascending-ascending or descending-descending, the ID field should be irrelevant but a) is the only field which uniquely identifies a row and b) is not in the same order as the dates so you have to force an order on both fields and then combine both orders to determine a final order. Subsequent sorting or filtering will mess that up. Sounds like you have a working solution from Arnel so I'm going to leave it there.

 

[ykurnia1]

the query is the view that is built either ascending-ascending or descending-descending, the ID field should be irrelevant but a) is the only field which uniquely identifies a row and b) is not in the same order as the dates so you have to force an order on both fields and then combine both orders to determine a final order. Subsequent sorting or filtering will mess that up. Sounds like you have a working solution from Arnel so I'm going to leave it there.

Duly noted CJ for the explanation; i will test thoroughly yours and Arnels working solution with real data in sufficient numbers and report here. By the way can suggest me with the thread link on simple example in how to use the RunningSum in the report?
Thank you all friends who have helped me in this running sum case, i indeed learn a lot here.

 

[CJ_London]

depends on your version of access but this link should help

Summing in reports - Microsoft Support

Learn how to use aggregate functions in your Access reports.

support.microsoft.com

running sum is just over halfway down

 

[ykurnia1]

depends on your version of access but this link should help

Summing in reports - Microsoft Support

Learn how to use aggregate functions in your Access reports.

support.microsoft.com

running sum is just over halfway down

Thank you CJ for the link, will look through it; btw i'm using office365.

 

[ykurnia1]

as the table grows up, how if i want to accomodate date of period in the query as i entered this >=[Enter the Start Date] And <=[End Date] in the criteria in design view, tried but get incorrect running sum result (cjlondon version) and run-time 3061 (arnelgp version)

 

[ykurnia1]

Generally speaking, can we create one field in our query which contain autogenerated id number start from #1 from first row (that correspond to sorting and filtering results of some fields from a table)?

 

[arnelgp]

add Parameter on query design:

[Enter Start Date] date/time
[Enter End Date] date/time

Field: Invoice_Date
...
...
Criteria: Between [Enter Start Date] And [Enter End Date]