Sam said:
Is there a formula or easy way to add a record counter in Access to a
query?
I have a field that contains a file number this file. I want to add a
counter like below.
Field Name Field Name (B)
FileNumber ElCounter
9565845 1
9565845 2
9552456 1
9552456 2
9552456 3
5695695 1
5695695 2
5695695 3
5695695 4
5695695 5
Thanks for your assistance.
In addition to Allen's excellent advice,
In:
http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/b24f513ff5abf10a
The query qryRankForMedian has a calculated field called
RankingWithinGroup. Adding an AutoNumber field to the original table
allowed the creation of the subqueries used to make that calculation.
It is likely possible to combine the two subqueries into a single
subquery, but having them separate preserves more intuition value for me.
For the 'Value' you can use the AutoNumber ID since the value used for
ranking is arbitrary in your example. So your query might look
something like:
qryFileNumberCounter (A97 version, changing '[' to '(' and '].' to ')'
should allow it to work in later versions):
SELECT * FROM [SELECT FileNumber, (SELECT Count(A.ID) FROM
tblFileNumbers AS A WHERE A.ID < tblFileNumbers.ID AND A.FileNumber =
tblFileNumbers.FileNumber)+(SELECT Count(A.ID) FROM tblFileNumbers AS A
WHERE A.ID = tblFileNumbers.ID AND A.ID < tblFileNumbers.ID AND
A.FileNumber = tblFileNumbers.FileNumber)+1 AS ElCounter, Data FROM
tblFileNumbers]. qryCount ORDER BY FileNumber, ElCounter;
The query got a little convoluted because I needed to sort on the
subquery results and didn't want to use two queries to do that.
I tested that query on the following table:
tblFileNumbers
ID AutoNumber
FileNumber Text
Data Text
ID FileNumber Data
1 9565845 A
2 9552456 B
3 5695695 C
4 9552456 D
5 5695695 E
6 9565845 F
7 5695695 G
8 9552456 H
9 5695695 I
10 5695695 J
and got the following results:
!qryFileNumberCounter
FileNumber ElCounter Data
5695695 1 C
5695695 2 E
5695695 3 G
5695695 4 I
5695695 5 J
9552456 1 B
9552456 2 D
9552456 3 H
9565845 1 A
9565845 2 F
If no sorting on ElCounter is required then it would have sufficed to
use (should work in all versions of Access):
qryFileNumberCounter:
SELECT FileNumber, (SELECT Count(A.ID) FROM tblFileNumbers AS A WHERE
A.ID < tblFileNumbers.ID AND A.FileNumber =
tblFileNumbers.FileNumber)+(SELECT Count(A.ID) FROM tblFileNumbers AS A
WHERE A.ID = tblFileNumbers.ID AND A.ID < tblFileNumbers.ID AND
A.FileNumber = tblFileNumbers.FileNumber)+1 AS ElCounter, Data FROM
tblFileNumbers;
which produced:
!qryFileNumberCounter:
FileNumber ElCounter Data
9565845 1 A
9552456 1 B
5695695 1 C
9552456 2 D
5695695 2 E
9565845 2 F
5695695 3 G
9552456 3 H
5695695 4 I
5695695 5 J
In calculating the median of GROUP BY values I needed to order each
group so that middle value(s) could be determined. It's likely that you
don't actually need a record counter unless you need a reliable way to
cite each individual record with the same FileNumber; perhaps you just
need a count of each FileNumber group in a separate query. BTW, using
ElCounter as a field name seems smart-alecky to me unless you're in,
say, Spain or Mexico.
For other approaches, see:
http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/af2b15d854ef18a9
http://groups.google.com/group/comp.databases.ms-access/msg/b2d6c149a09b37f7
http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/db227be5c3a2d2bb
James A. Fortune
(e-mail address removed)
I learned how to do subqueries mostly from information Allen supplied.
Getting a SQL query to calculate medians was the result of my acting on
an instinct that it was possible to dream up a SQL method to calculate
them using subqueries instead of using VBA.
