Running Total / Count In A Query (dsum / Dcount), Access 2010

[ryanjames17]

Hi there, I have a question about setting up a running total or count in an access query. I know this isn't the most efficient way to run things, but I trying it anyways, because it could automate a manual process for me.



A little background, I have a Monthly Production table (this is the base dataset, see 'Monthly Production Table Fields' image which is attached) which stores monthly production data for a number of wells. I have built a series of queries which pick the peak production rate in the first X months (user defined with a prompt) for each well and then normalizes that data, or rather it takes the monthly production from that month forward and reports it for all of the wells in the Monthly production table. The end result or query ( PBOE_XMo_Norm_Prod ) is shown in the attached image 'Query Results'. The problem is that the producing month starts at whatever is the peak month for each well, in the attached image it is month 4.

What I want to do is ad a running count that starts at 1 and counts each month consecutively but then resets to 1 when the well or category changes. I've done a bunch of research, but I can't seem to get the right results. I tried using a DSum initially as that was what I could find the most examples of. Regardless, what I seem to constantly get is a count or a sum of all the records in that field (in the example: Liquids) in the original Monthly Production Table. Any tips on what to do? Is it because I am prompting for data in the first query? Please advise, I've attached screenshots of some of the DB as well as the SQL text. Any advice or suggestion is appreciated.

Thanks,

SQL:

SELECT PBOE_XMo_Norm_Prod.Entity AS EntityID, PBOE_XMo_Norm_Prod.Prod_Mo AS NormMo, PBOE_XMo_Norm_Prod.Liquid, DCount("[Monthly Production].[Liquid]","[monthly production]","[monthly production].[Producing Month]=" & [NormMo] And "[monthly production].[entity]='" & [EntityID] & "'") AS Expr1
FROM PBOE_XMo_Norm_Prod;

[pbaldy]

Your concatenation is off. Try

DCount("[Liquid]","[monthly production]","[Producing Month]=" & [NormMo] & " And [entity]='" & [EntityID] & "'")

[ranman256]

Your runng Sum (Dsum) must have a WHERE clause to differentiate each record (and before it). (else youll get the same # for every record)

Dsum([fld],[tbl], where)
the 1st rec is the 1st rec
for the 2nd rec MUST be = 2nd rec and all the ones before it. , so where = "[entity] = [entid] and [normMo]<= [normMo] )

[ryanjames17]

Thanks Paul, when I tried that, all I got was the answer 1. The concantentation is set up the way it is I think, because one data type is text and the other is numeric. But maybe I did that wrong.

[pbaldy]

The delimiters are because one field is text, but your concatenation was off. That said, I missed that you need a <. Try


DCount("[Liquid]","[monthly production]","[Producing Month]<=" & [NormMo] & " And [entity]='" & [EntityID] & "'")

[ryanjames17]

ranman256,

Thanks but where to I stick the WHERE clause? Is it part of the criteria in the Dsum formula? I tried doing this: Expr1: DCount("[Monthly Production].[Liquid]","[Monthly Production]","'Where [Entity]='" & "'[EntityID]'" And "[NormMo]<=" & [Prod_Mo]) with the single apostrophe in front of and behind the WHERE but it didn't change the result. Also should I be referring to the Monthly Production Table or the Query I'm running this on?

[ryanjames17]

That worked! Thankyou... well sort of work. The query is running properly, but it is starting at 4 or the peak production month, which matches the NormMo in this query. I think this is because it's running it on the original data table and not the Query that I am running this query on. Any idea how to get it to work on this query?

Thanks for your help though, this is a breakthrough.