Running Total in Calculated field (1 Viewer)

umair434 · Oct 6, 2013

Hi all,

I have been trying to figure this out for a while.

I want to calculate running total and find out the date when that total is greater than a number.

My initial plan was to use Dsum and then use dlookup to find when that Dsum value > [Fixednum].

But when I try Dsum and use Totals in query, access shuts down. maybe because of 15000 rows.

I have attached a sample database that shows what Im working with and what I would like.

Any suggestions?

plog · Oct 6, 2013

What's the fixed number?

Nevermind I see it, give me 5 minutes.

plog · Oct 6, 2013

Your desired results are incorrect. The correct final value (based on your explanation in your first post) is the record with Date = 10/23/2013, not 10/24/2013.

To achieve what you described, you will need a sub query, this is that SQL:

Code:

SELECT Orders.PROD_NBR, Min(Orders.Date) AS FirstDate
FROM Orders
WHERE (((IIf([FixedNum]<=1*DSum("[Payments]","Orders","[PROD_NBR]='" & [PROD_NBR] & "' AND [Date]<=#" & [Date] & "#"),True,False))=True))
GROUP BY Orders.PROD_NBR;

That query finds the earliest date that the running total exceeds its fixed number. Name that query 'sub_ExceedsFixedNum'. Then the below code will produce what you want (per your explanation):

Code:

SELECT Orders.*, 1*DSum("[Payments]","Orders","[PROD_NBR]='" & [Orders].[PROD_NBR] & "' AND [Date]<=#" & [Date] & "#") AS RunningTotal
FROM Orders INNER JOIN sub_ExceedsFixedNum ON (Orders.Date = sub_ExceedsFixedNum.FirstDate) AND (Orders.PROD_NBR = sub_ExceedsFixedNum.PROD_NBR);

A few notes:

~Date is a bad field name. Its a reserved word and makes coding a little more difficult. I'd rename it with a prefix of what the data is for (i.e. OrdersDate)

~If PROD_NBR is actually a number you should use that data type and not text.

~FixedNum shouldn't be a field in your Orders table, doing so makes your tables unnormalized. This value should be in the table that tells you what each Product is (the one where PROD_NBR is the primary key).

Running Total in Calculated field (1 Viewer)

umair434

Registered User.

Attachments

plog

Banishment Pending

plog

Banishment Pending

Similar threads

Users who are viewing this thread