Running count in query

loki419

New member
Local time
Today, 08:39
Joined
Dec 24, 2006
Messages
3
I have a query with 40 fields and 16,000 records. Field 1 is Location, of which I have 197 unique values. What I would like to do is have a running count for each unique location and when the location changes start the count over again.

Example

Count Location Function
1 100 1111
1 105 1111
2 105 1121
3 105 1131
1 110 1151
2 110 1151

I am using Order By to get the proper sort. In some other instances I may want the count to be dependent on the combination of Location and Function codes. Can someone provide some SQL tip(s) that would allow me to accomplish this.

Thanks

Loki
 
Use a GROUP BY query (the sigma sign in the query builder's toolbar). It would look a little something like the attached.

~Moniker
 

Attachments

  • groupby.jpg
    groupby.jpg
    24.4 KB · Views: 367
Last edited:
Thanks. That seems to get me part of the way there. That technique gives me a "1" for each record. What I am trying to accomplish is a running talley of location. So if I have 5 locations = 001, then I would like to see the number column show 1-5. If I reload my source file and now there are 10 locations = 001, then I would like to see the count increase from 1-10.

Any ideas?

Thanks

Loki
 
So, going with your original example:

Code:
Count Location Function
1     100      1111
1     105      1111
2     105      1121
3     105      1131
1     110      1151
2     110      1151

What is the expected outcome of that? What I provided you was something that would show the something like this:

Code:
CountOfLocation Location
1               100
3               105
2               110


Are you wanting something like this?

Code:
CountOfLocation Location Function
1               100      1111
3               105      1111
3               105      1121
3               105      1131
2               110      1151
2               110      1151

If the latter is the case, that's a pair of queries to do that in Access. (In DB2, it's much easier, but that's a different story. :P) If I missed it on both accounts, then describe better what you want.

Honestly, it sounds like you want a Form/Subform setup where on the mainform, you select one location and then in the subform, all the correlating locations get displayed.

~Moniker
 
Last edited:
In the original example I should have been more clear. My source file has Location and Function as the first two fields. I have imported this into an Access table and will have to reload several times a year. Original source is sorted by Location then function and looks something like this.

Location Function
100 1111
105 1111
105 1121
105 1131
110 1151
110 1151

What I would like the query to do is give a result that looks like this

Count Location Function
1 100 1111
1 105 1111
2 105 1121
3 105 1131
1 110 1151
2 110 1151

So if I reload my source file and now location 105 has 4 records, I would like to see the count go 1-4. One other caveat is that the Location field is text because some of my locations have leading zeros (e.g.001,002, etc)

Thank you for taking time out of your holiday

Loki
 
Last edited:
I need this exact same thing. Did anyone find an answer?

Thanks
Kim
 

Users who are viewing this thread

Back
Top Bottom