Union query with running total 1

[JITA]

I have a union query (UqryDagMutaties) on two tables that returns the following fields
BankNr (text)
DatumMutatie (date)
GrbNr (number)
Bedrag (number)
RekOmschr (text)
Omschrijving (text)

Based on this union query I have created a Totals query to show filtered records. The records are shown on ascending order by DatumMutatie (date) and filtered on GrbNr. I would like to add a field that shows the running total of "Bedrag" per unique GrbNr. I have tried with the DSUM function but have not succeeded so far. My try is:
RunTot: DSum("[Bedrag]";"UqryDagMutaties")(as expression in the total row of the query tool of Access)

I have tried to include various criteria but nothing has worked so far. I would appreciate any thoughts that could help me with this problem

 

[dhookom]

The where argument of the function would need to filter the domain for a specific set of records. Try something like

RunTot: DSum("[Bedrag]";"UqryDagMutaties";"GrbNr=" & [GrbNr] & " AND DatumMutatie<=#" & DatumMutatie & "#")

Duane
Hook'D on Access
MS Access MVP

 

[JITA]

Hi Duane
Thanks for your input. Unfortunately this does not work either. It resembles some of my own tries. More thoughts?

 

[dhookom]

does not work" doesn't mean anything to anyone. We can't see your results. If you really want help, you have to provide enough information so someone can help.

Duane
Hook'D on Access
MS Access MVP

 

[JITA]

Sorry, you're right. I was not clear and I now see I also have made a mistake that I have now repaired. I do see running totals now.
Yet I have some problems with the outcome of the query.
The running total order seems odd. For instance the very first record (sorted in ascending order by date (DatumMutatie) shows a figure of -3,570 whereas the running total shows -6,330. Recordnr 5 and recordnr 43 (belonging to the same GrbNr) show the same running total of -6,330. The three records together add up to -6,330. However I would expect the following running totals:
record 1 -3,570, record 5 -4,490 and record 43 -6,330

A probable cause is the date, although these records show different dates(2nd of Jan, 3rd of Jan and 15th of Jan), they all occur in the same month of the same year. I don't understand why (my date field is defined as dd-mm-yyyy)

Furthermore I now see that when I want a limited number of records (for instance one GrbNr within one year (filter on GrbNr and DatumMutatie)) I stil see the running total of all the records in the datebase for that particular GrbNr. So I probly have to adjust the where clause regarding the date but I have no clue how.

I hope this is more explaining for you.

 

[dhookom]

You are experience date format issues. You must use m/d/y formats in queries. Try something like:

RunTot: DSum("[Bedrag]";"UqryDagMutaties";"GrbNr=" & [GrbNr] & " AND DatumMutatie<=#" & Format(DatumMutatie,"m/d/yyyy") & "#")

If you need to limit by start date, then you must set a starting date in the DSum() and use "BETWEEN...."

Duane
Hook'D on Access
MS Access MVP

 

[JITA]

It works fine.
Thanks for your support