I am seeking help on a Union Query. I am looking to do a running total within this query.
First, my project is a personal one relating to a share portfolio management tool. The reason for the union query is to combine four separate tables respectively holding data relating to share purchases, funding, dividends and interest charges.
The union query gives me [When] – date of transaction, [Notes], and [Amount] with the [Amount] column to be used for the running total where each value is either positive or negative according to the type of transaction that caused it eg a share purchase would be a debit/negative and a dividend would be a credit/positive. The desired result of this query would be to have a statement presentation similar to that one might receive from a bank.
But second, I have searched extensively for information on running totals within a Union query and have found virtually nothing – thus presuming my searching has been good enough – Can a Union Query actually be used to give a running total? If it can I would certainly welcome any guidance on how to go about it. I have constructed running totals directly from tables using Dsum and making use of the autonumber key field within the tables, but this union query has no individual autonumber field of its own (all the tables supporting this query have their autonumber key fields) and whether a running total process within a union requires an autonumber field I do not know. If a unique value field is required the [When] or date values are not unique and so, if autonumber is needed, then I presume an autonumber column will be required within the union query as a first step.
So an amateur here and enjoy the challenge that Access provides but this one is beyond me. Thus any advice much appreciated.
Using Access 2010.
If required I can certainly provide the SQL of the union query.
barkly