Keeping a Running Total if the Same Type

[esk10]

Hello! I'm trying to build a query that keeps a running total up to a certain date for each query entry if it is in the same type.

I have built a simple table that illustrates my case:
ID Date Amount Type1 11/14/2012 100 A
2 11/22/2012 200 A
3 11/23/2012 300 B
4 11/26/2012 200 A
5 11/29/2012 400 B


I have created a query with DSum to keep a running total; however I need the running totals to be different based on type. Currently I have this:


ID Type Year MonthDay RunTot
1 A 2012 11 14
2 A 2012 11 22 100
3 B 2012 11 23 300
4 A 2012 11 26 600
5 B 2012 11 29 800

As you can see, the running total does not consider the type into account.

The current RunTot (Running Total) query function is:
RunTot: DSum("Amount","Orders","DatePart('m', [Orders]![Date] )<=" & [Month] & "And DatePart('yyyy',[Orders]![Date])<=" & [Year] & "And DatePart('d',[Orders]![Date])<" & [Day] & "")

Is there a way to fix this in access query? I've been working on this the whole day but without success.

Thanks

[June7]

This is what reports are for. Build report using its Grouping & Sorting with aggregate calcs in group Header/Foother. This will allow display of detail records and summary calcs.

Otherwise, review http://allenbrowne.com/subquery-01.html
especially the example for Aggregation: Counts and Totals

[esk10]

Thank you for the reply.

True - creating a report will give me those numbers; however, I need to use the running total numbers to calculate something else.

The data provided above is actually a dummied down version that I created for this forum. The actual data involves investment securities. Portions of the securities are disposed. I need to figure out the interest earned at each disposal date. The problem is that at each disposal date, I need to figure out the outstanding amount of the security (the portion that we haven't sold back) so that I can use that amount, multiply by the interest rate to get the interest earned.

I'm not familiar with subqueries and SQL (I usually just use the design view access), so I will need some time to look through the link you've provided.

Any thoughts on this situation? Thank you!

[June7]

Try including the Type field in the DLookup as another criteria.

Domain aggregate functions can cause queries to run slow but that's the only other way I can see to get what you want.