Running Total in Union Query

[Freddie81]

Hi again Guys



I'm creating a union qury from 3 iffernt queries to calculate the runing balance of a speciic customer. I cant seem to get the running baance to work and my Sql is poor to say the least.

Any advice on how to do this or what to add in my SQl statement?

[ranman256]

easy to do in a report.
add Running total field.

[Freddie81]

Its not a report tho, i'm usin it is a subform displaying the query

[JoeM]

How about creating another query which just aggregates (totals) the Union Query you just built, and add that total query as another subform under the first one?

[Freddie81]

I trid creatng the second query for the dsumn and get this when I run it. The econd ic is the code Im sing in the qery builder.......

[JoeM]

Whoops, I am sorry. I misread your question. My solution is for a totals row at the bottom of the screen, not for a running total.

Maybe look at some of the options presented here, and see if you can adapt to work for you:
https://support.microsoft.com/en-us/...crosoft-access
https://theaccessbuddy.wordpress.com...m-part-1-of-2/
https://www.techrepublic.com/article...-access-query/

Hope that helps!

[JSR]

The only thing I can think of is to append your data to an empty table with the same fields plus an autonumber. Then create a query which has as its input the table you just populated, twice, with no join. Give them an alias of table1 and table2. Group by every field from table1 (except autonumber) and add to the query Sum([table2].[Amount]) Where table2.AutoNumField<=table1.AutoNumField.

It's messy but I can't think of a better way to do this without using a report.

[ArviLaanemets]

Create a saved query like your QustomersTransactionQ, but with an additional field for customer ID, and without WHERE clauses (e.g. QustomerTransactionsUQ).

SELECT "Invoice" AS [Entry], inv.InvoiceID AS [Document], inv.InvoiceDate AS [Transaction Date], cust.CustomerID, inv.InvoiceTotal AS [Amount] FROM CustomersT cust INNER JOIN InvoiceT inv ON cust.CustomerID = inv.Customer
UNION ALL
SELECT pay.PaymentMethod AS [Entry], pay.InvoiceID AS [Document], pay.PaymentDate AS [Transaction Date],  cust.CustomerID, -1*pay.Amount AS [Amount] FROM CustomersT cust INNER JOIN PaymentsT pay ON cust.CustomerID = pay.Customer
UNION ALL
SELECT "Credit Note" AS [Entry], cred.CreditNoteID AS [Document], inv.CreditDate AS [Transaction Date], cust.CustomerID, -1*cred.TotalCredit AS [Amount] FROM CustomersT cust INNER JOIN CreditNoteT cred ON cust.CustomerID = cred.Customer

Now you can create a query like (really you can have many different queries with different grouping rules)

SELECT SUM(Amount) FROM QustomerTransactionsUQ WHERE CustomerID = [Forms]![CustomerDataEntryF]![CustomerID]