Running Count Over Group in Query

sadist

Registered User.
Local time
Today, 13:58
Joined
Mar 2, 2010
Messages
11
I'm attempting to do a running count on records in a query and reset the count starting again at 1 for each group of records displayed. Following are my fields and sample date.

printref - primary key AutoNumber
TProdRef1 - Product reference

I do have other fields in the query, 6 in total but the two fields above are the ones I'm interested in working with.

Sample Data

printref TProdRef1 Running count
89 2 1
90 2 2
91 2 3
92 5 1
93 5 2
94 6 1

I have tried many variants of the following but am n ot getting the result i'm looking for. RunningCount: Val(DCount("printref","TPrint Data Store","TprodRef1 <= " & [TProdRef1]))

would appreciate any suggestions. Regards :)
 
Thanks for your reply

I have tried your recomendation
RunningCount: Val(DCount("printref","TPrint Data Store","TprodRef1 <= " & *))
but the formula automatically reverts to the previous version.
An example of the actual data result I am getting from the above expression is:

printref TProdRef1 Runningcount
81............3............3
82............3............3
83............3............3
84............5............9
85............5............9
86............4............7
87............4............7
88............4............7
89............4............7
Whereas the result I'm trying to acheive is:
printref TProdRef1 Runningcount
81............3............1
82............3............2
83............3............3
84............5............1
85............5............2
86............4............1
87............4............2
88............4............3
89............4............4

Regards
Dermot
 
Apols left out data type in my reply
TProdRef1 is also numerical
 
I'm sorry, I meant the first field. Try this:

RunningCount: DCount("*","[TPrint Data Store]","TprodRef1 <= " & TprodRef1)
 
If your data is typical then you could treat it as a ranking excercise on printref.

Code:
SELECT x.printref, x.tprodref, (select count(*) + 1
           from tblrank
          where tprodref = x.tprodref
            and printref < x.printref ) AS runningcount
FROM tblrank AS x
ORDER BY x.printref, x.tprodref;

Brian

Edit Came across this when searching, should have said that tblRank should be substituted in all cases by the actual table name , which I didn't know when writing the code.
 
Last edited:
Paul thanks for your suggestion but I'm still getting the same unwanted result shown in my example above.

Brian unfortunatly my knowledge of SQL is not educated so would appreciate a little more guidance. The following is the code behind my query as it stands, how do I use your suggested code?
SELECT [TPrint Data Store].PrintRef, [TPrint Data Store].TProdRef1, [TPrint Data Store].Product, [TPrint Data Store].[Batch Date], [TPrint Data Store].SSCC, [TPrint Data Store].[No Boxes], [TPrint Data Store].[Best Before Standatd Format], DCount("*","[TPrint Data Store]","TprodRef1 <= " & [TprodRef1]) AS RunningCount
FROM [TPrint Data Store];

Dermot
 
Sorry, that was a bit of a duh on my part. See if this gets what you want:

DCount("*","[TPrint Data Store]","TprodRef1 = " & [TprodRef1] & " AND printref <= " & [printref])
 
Bingo!! Paul your a gent, thanks a mil

I'd still like to understand and learn more about how you came up with that and more on SQL so would appreciate a pointer to a good practical course

Dermot
 
I went to the "school of hard knocks", so not sure about any courses. The problem was I never really looked at the data, just copied your formula and adjusted it. Based on your sample data, you didn't want the "less than or equal" on the TprodRef1 field, because that was the field you wanted grouped on. I changed that to equals and added the "less than or equal" on the printref field, which based on your examples was the field the ranking was based on.

I just noticed Brian's post, which would also work. It uses a subquery instead of the DCount(). Neither would be terribly efficient, but I can't say if one would be better than the other on a large dataset.
 
Ya know that place as well, still just learned another step. Again thanks

Dermot
 
Hi pbaldy

I was referred to this thread by someone and this seems to be exactly what I am looking for. I substituted the particulars in Sadist's original post and though it seems like it wants to work, I keep getting #Error. I used the following formula:

DATE COUNT: DCount("*","RESULTS1","MEMNO = " & [MEMNO] & "AND [uniq dos] <=" & [uniq dos])

and attached is what I am trying to accomplish. The column "Date Count" is what I am trying to populate. I started doing it in excel but the file is so large it crashes on me so I thought I would see if there was a way to do it in access.






Thanks
 

Attachments

If either field is text or date/time, it would require delimiters:

http://www.theaccessweb.com/general/gen0018.htm

Also, using [uniq dos] wouldn't gain you anything, since it always has a value of 1 (in the example anyway). You'd want to use the BEGDATE field, or an ID field that was unique to each record.
 
Thanks Pbaldy

I went to the suggested site and applied the logic I thought was appropriate and still get the #Error. I used:

DCount("*","RESULTS1","MEMNO = " & [MEMNO] & " AND BEGDATE <= #" & [BEGDATE] & "#").

What am I doing wrong?
 
Is MEMNO text? It has leading zeros, though that could be formatting. Can you attach the db here?
 
ahh...yes Memno IS text. I toally forgot that. I would need delimiters on that as well. I looked at the site that you posted and I see the delimiters for the date but it looks like I just need the dbl qoutes for the MEMNO which I already have.
 
No, you're missing the single quotes. Try

DCount("*","RESULTS1","MEMNO = '" & [MEMNO] & "' AND BEGDATE <= #" & [BEGDATE] & "#")
 
Thanks again Pbaldy

Unfortunately this is not working for me. I keep getting a message that the formulas-excel is not working and to switch to fix, but once I do that the whole program locks up and I have to cntrl+alt+del to close it down. I will have to find another way to accomplish what I need to do but I appreciate your help and guidance.
 
Excel or Access? That was intended for Access. Can you attach the db here?
 
Your DCount() will work if you just add an AutoNumber column with a little tweak in your criteria. Suppose, you added a column called ID which is an AutoNumber field. So, all you have to do is add another criteria into your DCount function.
DCount("printref","[TPrint Data Store]","TprodRef1 = " & [TProdRef1] & " and ID <= " & [ID])

I wish this was helpfull.
 

Users who are viewing this thread

Back
Top Bottom