Numbering Query Returns

  • Thread starter Thread starter peteb
  • Start date Start date
P

peteb

Hi,

I have stored my music collection in an Access Database and would like to be
able to automatically number the records that are returned by a particular
query. For example I have a query that will return records that were
released in the seventies, these are then sorted by artist alphabetically.
If for example there were 60 records then I would like to automatically
number them from 1 to 60.

The only way I have managed to successfully do this up to now has been to
create a new table with a maketable query, change the existing record ID
from autonumber to number and then add another field set to autonumber which
is a pain as my collection is constantly being updated. Is there any way
that I can achieve the same without going through this.

Thanks for the help

Pete
 
Pete,

You didn't give us your table structure, so I'll assume
you have the follwing fields (Artist, Title,
ReleaseDate). If you have others, that is OK, just ignore
them for now.

-Add two copies of your query to the query grid (give them
aliases of qry1 and qry2).
-Join the two copies on the ArtistID.
-Select the Artist, Title, and ReleaseDate fields from the
table on the left. Add the ArtistID and the ReleaseDate
fields from the table on the right
-Make the query a totals query, grouping by the three left
most columns. Select Count for the second ArtistID
column, and WHERE for the second ReleaseDate column
-In the criteria of the second ReleaseDate, enter:
<= qry1.ReleaseDate

Your query should look something like:

SELECT qry1.Artist, qry1.Title, qry1.ReleaseDate,
COUNT(qry2.Artist) as Sequence
FROM yourQuery qry1
INNER JOIN yourQuery qry2
ON qry1.Artist = qry2.Artist
WHERE qry2.ReleaseDate <= qry1.ReleaseDate
GROUP BY qry1.Artist, qry1.Title, qry1.ReleaseDate

HTH
Dale
 
If you must do it in the query, the temp table is probably the easiest way.

If you just need it for a print out:
1. Create a report, laid out as you wish.

2. Add a text box with these properties:
ControlSource =1
Running Sum Over Group
 
Back
Top