Numbering grouped records in a select query

[Guest]

I want to add a field to a select query that will number a set of grouped
records from two related tables.

Table A has a list of locations with addresses etc, table B is related via a
lookup field and allows the selection of city names. I want my query to not
only sort the output alphabetically I also want it to number the sites which
are located in that city from 1 to n (n being the total number of locations).
So if city X has five locations city Y has 2 locations the query should
include a field as follows:

City Location #
X 1
X 2
X 3
X 4
X 5
Y 1
Y 2

Is there an expression I can use to calculate this?

 

[Allen Browne]

There's not a simple way to do this and get reliable results, regardless of
how the user filters and sorts the query.

If you can ignore the fact that the user may filter/sort it, you could use a
DCount() expression to count the number of prior locations in the city,
assuming it's sorted by something like the location name. You would type an
expression into the Field row something like this:
Nz(DCount("*", "MyTable", "(City = """ & [City] & """) AND (LocationName
< """ & [LocationName] & """)"),0)+1

It may be faster to use a subquery rather than a DCount() expression:
http://allenbrowne.com/subquery-01.html