I have a large number of records (30,000+) which fall into multiple (650+) categories. I need to number these records within their respective categories.
So if I have for example:
Record Category
1 A
2 B
3 A
4 A
5 B
I would like to add a field to number the records within each category so that I get:
Record Category CategoryCount
1 A 1
2 B 1
3 A 2
4 A 3
5 B 2
How do I go about accomplishing this?
Can this be accomplished through queries or is VBA required?
Two queries and you’re good. Assume your table’s called Test.
Query 1:
SELECT Test.Category, Count(Test.Category) AS CountOfCategory
FROM Test
GROUP BY Test.Category;
Query 2:
SELECT Test.Record, Test.Category, Query1.CountOfCategory
FROM Test INNER JOIN Query1 ON Test.Category = Query1.Category;
Now, if you actually want to store the count in the original table, that’s a little trickier. Query 2 looks like what you want, but it’s a separate object. Is that good enough?
Couple of ways to do this. One is programatic…(which I don’t know). The other is more manual, but would be faster for me.
Add a column in your table something like “count of group”.
Copy that table structure (right click on table, select copy, then right click in database window, and choose paste…then from the advanced options, choose “structure only”.).
Now edit the copied version, and make the new field an autonumber field.
open original table, right click, and filter by selection the group that you want to work on. Once that filter is in place, select all the records. Copy all the records, then paste them in the new table…bingo, the autonumber field generates a value for you. Now do it in reverse…copy all the records from the new table with the autonumber value, and past them right overtop of the records in the original table.
Repeate as required. True this is a bit time consuming, and there are other ways of doing it…but if you’re not familiar with SQL enough to do it, and you need those values in your original table in a static format, then this is one way. Good luck, and if it wasn’t clear, just lemme know.
ultrafilter, I’m actually trying to assign a unique incrementing sequence number to each member of the group, not the count of records in the group.
Atrael, I think what you’re suggesting will work, but I can’t do 650 groups in this manner in the time required. As you already guessed I need an automated process.
Thanks for the help though! 
Any other thoughts?
That’s a little trickier. Is it absolutely necessary that you have this information in the original table?
Pretty close. After I get the sequence number I have to perform operations on it. As I said I have about 650 groupings of data. I really don’t like the thought of writing 650 SETS of 10 or so queries to get through the next steps.
Maybe I could do it in VB? Any thoughts?
Yeah, VB (or any programming language) is the best option here. Do you write VB?
Here’s a very inelegant (but quick to code) VB solution to your problem. This uses ADO recordsets to update your table. The biggest problem with this is it is not dynamic and would have to be run to “update” your table after data entry.
Dim Category As New ADODB.Recordset
Dim Count As New ADODB.Recordset
Category.Open "Select Distinct Category From Test", Application.CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
Do Until Category.EOF
Count.Open "Select * From Test Where Category = '" & Category.Fields("Category").Value & "'", Application.CurrentProject.Connection, adOpenKeyset, adLockPessimistic
Do Until Count.EOF
Count.Fields("CategoryCount") = Count.AbsolutePosition
Count.MoveNext
Loop
Count.Close
Category.MoveNext
Loop
Category.Close
This assumes that your table is named test. If you use this make sure to add a reference to ADO in the VBA IDE. If you have no idea what any of these means, reply and I’ll walk through it better.
I wonder if you’d mind elaborating a little on why it is you want to do this; it seems fairly clear that you’re doing it toward some end (my guess would be that you want to be able to pick out the Nth occurrence of any given category in some later query) - it may be that there is some simpler route to achieving your desired end result…
zoid, the code solution given above will do the trick for you with a little fanaglaing on your part. Keep in mind though, that you may be making a mistake a lot of people do…in that you’re spending more time looking for a solution programatically, than you would spend manually doing it. 650 sounds like a lot, but really you’re talking about 3 mouse clicks, and a copy and paste. You could probably get through them all in an hour or so. About the same amount of time you’ll spend trying to the the VB to work if you’re not familiar with it.
Also, keep in mind these will be static numbers. If you’re going to be adding more records at a later date, and need the catagory number updated, you’ll have to do it by hand. You may want to look at normalizing that data out, to make things a bit easier in the long run.
And the next time around, when zoid has 6500 categories to handle, the code will work just fine with no modifications. If she tries to do those manually, that’s 10 hours of work.
A little investment up front can save a lot of time later.
In this case, a little investment would be to normalize the data correctly, then this wouldn’t come up again, and you wouldn’t need code to update it manually at all, now would you? Zoid could also redesign whatever front end they’re using to add these number incrimentally upon data entry. My point was that right now, if it’s something that needs to be done in a hurry, zoid’s spending more time looking for answers, and modifying someone else’s code than it would take to just get a workable solution in place.
Atrael, I completely agree that normalizing the data would be the correct solution. However the reason I’m doing this is to update a Telco system. The data in this system is FAR from normalized, in fact the data is sh*t. I can’t see doing it manually. I would have to re-crate around 10 queries for each category, so even though it’s only a couple mouse clicks for each one, doing that 6,500 times just doesn’t seem to be the proper path (and given my lack of attention to detail it would just introduce too much risk for error.
Mangetout, the reason I need to do this is that the Telco system I’m updating assigns a reference number to each record. That reference number contains a component which is a sequence number, but there is a separate set of sequence numbers for each classification of records.
This is an OLD system. Telcos have been using it for YEARS. It is definitely not relational and the data is definitely not normalized. I agree that the way things are currently done is not optimal (hell it’s not even rational).
larsenmtl, thanks for the code! I’ll get back to you and let you know if I got it to work. I appreciate the help!
ultrafilter, I write a little VB. I’ve actually been leaning that way for a while now on this problem… I’m pretty sure with the code larsenmtl provided I can muddle through it. Oh and BTW it’s “he” (I’m a guy) 
Huh. Where the hell did I get that idea? I’ll keep that in mind.
zoid,
One addition to my code: If Record is not a key field you might want to add an “Order By” clause to the second SQL statement just take make sure it opens the recordset in the correct order.
As in:
Count.Open "Select * From Test Where Category = '" & Category.Fields("Category").Value & "' Order By Record", Application.CurrentProject.Connection, adOpenKeyset, adLockPessimistic
I’m currently working on a pure SQL solution, although my SQL is very rusty and I don’t think Access will take advanced SQL. I’ll let you know what I can come up with.
All right, I take it back, it’ll never work in Access. Without Cursors or Triggers, I just don’t think Access SQL can handle it.
:smack: