Running Total within a Union Query

[barkly]

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

[June7]

Running totals are not easy to do in query and can perform slowly. This is easy to do in a report, because then you have RunningSum property in textbox.

You would have to use the UNION query as the source for another query.

Review https://support.microsoft.com/en-us/...crosoft-access

Google: Access query running sum

[barkly]

Thanks Jun7 for your response.

As this a private database it will never be so large as to have slowness be an issue, so if a union query solution can be found that is the way I would prefer to go. Your suggestion of using a union query as the source for another query appeals to me but I shall need a starter on this and if you can provide some guidance on this it would be appreciated.

And yes made extensive use of Google: Access union query running sum’ – found only one or two offerings in these searches but the solutions were quite specific eg use of just two tables as the basis of the union query where I have four and could not see how the examples given could be adjusted to handle the extra tables.

barkly

[June7]

Doesn't matter how many tables are in the UNION. Use the UNION query like a table (except cannot edit data through the query). Use the UNION as source for another query or a report.

Why do you have 4 tables to begin with? Why not 1 table?

[barkly]

Thanks jun7,

Why four tables? – well it started out that way several years ago and the rest of the program in the meantime has grown around these four tables and works perfectly. I agree one table would provide a simple approach in allowing the generation of a running sum. Initial assessment indicates some 28 fields – perhaps a few less - required in a single table to cover all of dividends, funds, interest and stock transactions of purchase and sale. I guess that isn't too many for a single table however this would require a virtual rewrite of the program so shall keep it in mind if no other solution to achieving a running sum out of a union query is not available.


I have placed my union query within another query as source as you suggest. This I presume leads to the use of Dsum over the Amount column to generate the running total but given there is no unique key for Dsum to work off (and Dates are not unique) I just do not see what the next step is to be.


Cheers

barkly

[June7]

Next step - Build a report and use RunningSum property of textbox?


Without a unique ID in the UNION, calculating running sum in query may not be possible. It might be possible to calculate an ID in the UNION or in a query that uses the UNION with a DCount() but really need a better understanding of your db structure to provide more help. If you want to provide db for analysis, follow instructions at bottom of my post.

An example of running sum in query can be found at http://www.rogersaccesslibrary.com/forum/forum1.html

[barkly]

If you want to provide db for analysis, follow instructions at bottom of my post.

Thanks June7 for this offer. I am vacation for a couple of weeks. I would like for you to have a look at my db on my return if that is suitable with you then.

Thanks again

barkly

[barkly]

Hi june7,

Returned from vacation and have also managed to solve my problem viz:

Included the union query into a standard select query and then included another column in this select query to hold an autonumber which is driven by a Module (create autonumber) - which I had found previously via Google - and then put all that into a Make table - as the autonumber was volatile - and then the make table becomes the basis of another query where I have been able to apply this DSum function - Balance: CCur(DSum("[Amount]","[tbl_StateFin]","[Finstatekey]<=" & [Finstatekey])) and there it is. The running sum. Beautiful. Perhaps this approach not all that suitable if the db was of some hundreds of thousands of lines but does the job perfectly for a small db.

Thanks for your initial interest.

barkly