Solved Running Total from Query (1 Viewer)

[ykurnia1]

I'm trying to calculate running total in a simple query, which the field that will be calculated as running total is come from the multiplication of two fields from a table.
In short, this multiplication field will be in the same query with the running total field, let us said two fields as multiplied from a table, it is named QUANTITY and UNITPRICE to result a field named TOTAL (so here TOTAL = QUANTITY * UNITPRICE), then this TOTAL field will be calculated its running total in the next field of the same query. Please kindly peruse my attachment for the dummy data accdb file, the query will be sorted by INVOICE DATE (can be changed at any time by either ascending or descending) based on field selection criteria (here OFFICE and PARTY EVENT field were chosen as the selection criteria in the 2 queries respectively).

Any feedback from you guys will be much appreciated, is it possible to calculate the running total without the VBA?
One more question, how to do a report with this running total? Thank you.

 

[June7]

Running sum is difficult in query. Fairly simple in report because textbox has RunningSum property.

 

[plog]

I got the running total query, but you lost me around OFFICE and PARTY values. So let's talk about a generic running total using just ID_Invoice, Invoice_Date, Quantity and Unit_Price.

We need to use ID_Invoice and Invoice_Date because you need a distinct way to order your data. You do not have that with Invoice_Date alone. That means if you were to use just the Invoice_Date for ordering both records on 1/22/2020 would have the running total value of 220 (30*2 + 100*1). So you also need to use ID_Invoice so that they distinctly order.

Running totals are done with a correlated sub-query (https://en.wikipedia.org/wiki/Correlated_subquery). That means the subquery and its outer query are able to "communicate" . The subquery is able to access data in the outer query and use it. This is the SQL for a basic running total on your data:

SELECT mainQ.Invoice_Date, mainQ.ID_Invoice, mainQ.Quantity, mainQ.Unit_Price,
  (SELECT SUM(Quantity*Unit_Price)
  FROM Table_Invoice 
  WHERE mainQ.Invoice_Date >= Table_Invoice.Invoice_Date AND mainQ.ID_Invoice>=Table_Invoice.ID_Invoice)
  AS RunningTotal
FROM Table_Invoice AS mainQ
ORDER BY mainQ.Invoice_Date, mainQ.ID_Invoice;

Paste that into your database and run it. It will give you a jumping off point.

 

[ykurnia1]

Hello June7 and Plog, highly appreciated for your reply; OFFICE and PARTY values are just such event category to organize the product, both using Short Text Data Type

 

[ykurnia1]

I got the running total query, but you lost me around OFFICE and PARTY values. So let's talk about a generic running total using just ID_Invoice, Invoice_Date, Quantity and Unit_Price.

We need to use ID_Invoice and Invoice_Date because you need a distinct way to order your data. You do not have that with Invoice_Date alone. That means if you were to use just the Invoice_Date for ordering both records on 1/22/2020 would have the running total value of 220 (30*2 + 100*1). So you also need to use ID_Invoice so that they distinctly order.

Running totals are done with a correlated sub-query (https://en.wikipedia.org/wiki/Correlated_subquery). That means the subquery and its outer query are able to "communicate" . The subquery is able to access data in the outer query and use it. This is the SQL for a basic running total on your data:

SELECT mainQ.Invoice_Date, mainQ.ID_Invoice, mainQ.Quantity, mainQ.Unit_Price,
  (SELECT SUM(Quantity*Unit_Price)
  FROM Table_Invoice
  WHERE mainQ.Invoice_Date >= Table_Invoice.Invoice_Date AND mainQ.ID_Invoice>=Table_Invoice.ID_Invoice)
  AS RunningTotal
FROM Table_Invoice AS mainQ
ORDER BY mainQ.Invoice_Date, mainQ.ID_Invoice;

Paste that into your database and run it. It will give you a jumping off point.

Thank you for the SQL code Plog, unfortunately I got syntax error popup message when putting the code (screenshots as attached).

 

[ykurnia1]

I got the running total query, but you lost me around OFFICE and PARTY values. So let's talk about a generic running total using just ID_Invoice, Invoice_Date, Quantity and Unit_Price.

We need to use ID_Invoice and Invoice_Date because you need a distinct way to order your data. You do not have that with Invoice_Date alone. That means if you were to use just the Invoice_Date for ordering both records on 1/22/2020 would have the running total value of 220 (30*2 + 100*1). So you also need to use ID_Invoice so that they distinctly order.

Running totals are done with a correlated sub-query (https://en.wikipedia.org/wiki/Correlated_subquery). That means the subquery and its outer query are able to "communicate" . The subquery is able to access data in the outer query and use it. This is the SQL for a basic running total on your data:

SELECT mainQ.Invoice_Date, mainQ.ID_Invoice, mainQ.Quantity, mainQ.Unit_Price,
  (SELECT SUM(Quantity*Unit_Price)
  FROM Table_Invoice
  WHERE mainQ.Invoice_Date >= Table_Invoice.Invoice_Date AND mainQ.ID_Invoice>=Table_Invoice.ID_Invoice)
  AS RunningTotal
FROM Table_Invoice AS mainQ
ORDER BY mainQ.Invoice_Date, mainQ.ID_Invoice;

Paste that into your database and run it. It will give you a jumping off point.

If I add TOTAL as a calculated field in Table_Invoice, will it simplify the case as the total number will be called from the table (not calculated in the same query with running sum field)?

 

[plog]

Here's the database I did it in.

 

[plog]

It won't simplify it in any meaningful way. Still gonna need the correlated sub-query.

 

[ykurnia1]

Here's the database I did it in.

Thank you Plog, i tried to select based on event inherited from your code, unfortunately still not giving correct runningsum amount (please see the attached files for screenshots and database files).

 

[plog]

For any field you want to limit your running total to, you must add to the SELECT of the main query and the WHERE of the subquery.

 

[ykurnia1]

For any field you want to limit your running total to, you must add to the SELECT of the main query and the WHERE of the subquery.

Have included both in the SQL Code with no luck, screenshoots as attached

 

[ykurnia1]

Have included both in the SQL Code with no luck, screenshoots as attached

Have tried also the following code but still unsuccessful :
SELECT mainQ.Event, mainQ.Invoice_Date, mainQ.ID_Invoice, mainQ.Quantity, mainQ.Unit_Price, (SELECT SUM(Quantity*Unit_Price)
FROM Table_Invoice
WHERE mainQ.Event>= Table_Invoice.Event AND mainQ.Invoice_Date >= Table_Invoice.Invoice_Date AND mainQ.ID_Invoice>=Table_Invoice.ID_Invoice) AS RunningTotal
FROM Table_Invoice AS mainQ
WHERE (((mainQ.Event)=3))
ORDER BY mainQ.Event, mainQ.Invoice_Date, mainQ.ID_Invoice;

 

[onur_can]

You must first create a query that takes the sum. You should then include this query in another query that contains all the fields.

SELECT Sum([Query Office].RunningTotal) AS ToRunningTotal
FROM [Query Office];

SELECT [Query Office].Event, [Query Office].Invoice_Date, [Query Office].ID_Invoice, [Query Office].Quantity, [Query Office].Unit_Price, [Query GeneralTotal].ToRunningTotal
FROM [Query Office], [Query GeneralTotal];

 

[arnelgp]

I also made some Query (query1).
based on Query1, create a query with your condition.

 

[CJ_London]

based on the data provided in post#1 this works - it doesn't use a subquery so should be faster. Note it uses a non standard join that cannot be viewed in the query builder. If you open the query builder you will lose the join.

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.ID_Invoice >= TT.ID_Invoice) AND (T.Invoice_Number = TT.Invoice_Number)
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.Event
HAVING (((T.Event)=1))
ORDER BY T.Invoice_Date DESC, T.ID_Invoice;

The join in question is T.ID_Invoice >= TT.ID_Invoice. So just make it in the query, then go to sql view and add the>

Alternative is to use a where clause instead - but that will potentially be slower

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.Invoice_Number = TT.Invoice_Number
WHERE (((TT.ID_Invoice)<=[t].[id_invoice]))
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.Event
HAVING (((T.Event)=1))
ORDER BY T.Invoice_Date DESC, T.ID_Invoice;

 

[ykurnia1]

I also made some Query (query1).
based on Query1, create a query with your condition.

Thank you Arnelgp, I have tested with every event, it will fail for any descending sort combination in Invoice_Date and ID_Invoice, but I'm sure it is almost there, kindly see my attached screenshots using PARTY event selected.

 

[ykurnia1]

You must first create a query that takes the sum. You should then include this query in another query that contains all the fields.

SELECT Sum([Query Office].RunningTotal) AS ToRunningTotal
FROM [Query Office];

SELECT [Query Office].Event, [Query Office].Invoice_Date, [Query Office].ID_Invoice, [Query Office].Quantity, [Query Office].Unit_Price, [Query GeneralTotal].ToRunningTotal
FROM [Query Office], [Query GeneralTotal];

Thank you for your feedback, how for the ascending/descending sort in Invoice_Date and ID_Invoice, and also event criteria, can just set it in designview or must accomodate it in the sql code?

 

[ykurnia1]

based on the data provided in post#1 this works - it doesn't use a subquery so should be faster. Note it uses a non standard join that cannot be viewed in the query builder. If you open the query builder you will lose the join.

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.ID_Invoice >= TT.ID_Invoice) AND (T.Invoice_Number = TT.Invoice_Number)
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.Event
HAVING (((T.Event)=1))
ORDER BY T.Invoice_Date DESC, T.ID_Invoice;

The join in question is T.ID_Invoice >= TT.ID_Invoice. So just make it in the query, then go to sql view and add the>

Alternative is to use a where clause instead - but that will potentially be slower

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.Invoice_Number = TT.Invoice_Number
WHERE (((TT.ID_Invoice)<=[t].[id_invoice]))
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.Event
HAVING (((T.Event)=1))
ORDER BY T.Invoice_Date DESC, T.ID_Invoice;

I run both of the code and highlighted the query as runningtotal not giving the correct total at the end.

 

[ykurnia1]

based on the data provided in post#1 this works - it doesn't use a subquery so should be faster. Note it uses a non standard join that cannot be viewed in the query builder. If you open the query builder you will lose the join.

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.ID_Invoice >= TT.ID_Invoice) AND (T.Invoice_Number = TT.Invoice_Number)
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.Event
HAVING (((T.Event)=1))
ORDER BY T.Invoice_Date DESC, T.ID_Invoice;

The join in question is T.ID_Invoice >= TT.ID_Invoice. So just make it in the query, then go to sql view and add the>

Alternative is to use a where clause instead - but that will potentially be slower

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.Invoice_Number = TT.Invoice_Number
WHERE (((TT.ID_Invoice)<=[t].[id_invoice]))
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.Event
HAVING (((T.Event)=1))
ORDER BY T.Invoice_Date DESC, T.ID_Invoice;

When I try to run in ascending order it also still fail

 

[CJ_London]

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)