adding a ranking column in query

[giz]

Hi,
I have created a query that accumulates various columns of information from
different tables. In this query the user selects, as criteria for a "musym"
field, a value (i.e. 38E) of interest for subsequent export of the accumulated information for that specific value into excel. However, before export I would like to add a column to the query that will contain a "ranking" number for every unique "siteiid" value in the query, listed sequentially. Since the number will be based on a particular record id, in this case "siteiid", and many rows may have the same ID,this is not really a row count. For example, my query result may look like this

siteiid musym
450 38E
450 38E
450 38E
450 38E
450 38E
451 38E
451 38E
451 38E
451 38E
451 38E
604 38E
604 38E
604 38E
604 38E
604 38E
610 38E
610 38E
610 38E
610 38E
610 38E
611 38E
611 38E
611 38E
611 38E

There are 24 rows in this example, but only 5 different, unique "siteiid"
designations. So what I need is new column that displays the a "ranking" of those ID's. So the new column will look something like this:

Rank siteiid musym
1 450 38E
1 450 38E
1 450 38E
1 450 38E
1 450 38E
2 451 38E
2 451 38E
2 451 38E
2 451 38E
2 451 38E
3 604 38E
3 604 38E
3 604 38E
3 604 38E
3 604 38E
4 610 38E
4 610 38E
4 610 38E
4 610 38E
4 610 38E
5 611 38E
5 611 38E
5 611 38E
5 611 38E


So now I have one number for all unique, siteiid labels regardless of how
many times each one is duplicated in the table. Thanks for any help.

 

[Louie]

Seems to me that the siteiid field is already you ranking. Are you just requiring that your ranking must start with one?

 

[giz]

yes, that is what I need to do. The current siteiid value is actually the ranking of that siteiid field in one of the original tables (table:site) being queried, of which there are over 900 records. That is why values are 450, 451, etc. . The query takes a subset from the "site" table, and info from other joined tables, from which there are multiple records per siteiid. That is why there is more than one record per each unique siteiid value. Basically, I just need to re-rank the siteiid field once a subset has been taken from my query. Thanks

 

[Louie]

Assume sTable Cantained data simular to:


Siteiid Musym
450 38E
450 38E
450 38E
450 38E
450 38E
451 38E
451 38E
451 38E
451 38E
451 38E
452 38E
452 38E
452 38E
604 38E
604 38E
604 38E
604 38E
610 38E
610 38E
610 38E

and xTable was defined as

Rank autoNumber
Siteiid number
musym text

SELECT xTable.* INTO rTable FROM xTable;

The above Select would make a new table calld rTable resetting the auto number to 1

INSERT INTO rTable ( Siteiid, Musym )
SELECT sTable.Siteiid, sTable.Musym
FROM sTable
GROUP BY sTable.Siteiid, sTable.Musym;

This select groups and appends from sTable filling rTable with the desired results. Hope this helps.

Louie ...

 

[mresann]

You Need Two Queries

You need two queries to achieve your function. Use the first query to set your parameters, and the second query to display the correct rankings (See notes below).

I will use your fields, incidentally, but since you didn't include a table name, I'll call it tbleRank. Substitute your table name where you see it in either query below.

First, create a new query and call it InterimQuery. Enter the following query into the SQL window:

SELECT DISTINCT tblRank_1.musym, tblRank_1.siteiid, (Select Count(*) from tblRank Where [siteiid] < tblRank_1.siteiid) AS InterimRank
FROM tblRank AS tblRank_1
WHERE (((tblRank_1.musym)="38e"))
ORDER BY tblRank_1.siteiid;

Next, create another new query and save it as FinalRank. Enter the following query into the SQL window:

SELECT (Select Count(*) from InterimQuery Where [InterimQuery].[InterimRank] < InterimQuery_1.InterimRank)+1 AS FinalRank, InterimQuery_1.siteiid, InterimQuery_1.musym
FROM tblRank INNER JOIN InterimQuery AS InterimQuery_1 ON tblRank.siteiid = InterimQuery_1.siteiid
ORDER BY InterimQuery_1.InterimRank;

(If the syntax messed up due to posting formatting, adjust in SQL box).

The results of FinalRank should be what you are looking for.

NOTE 1: Use SELECT DISTINCT in the InterimQuery if you wish to restrict siteiid to one unique occurance in the recordset.

NOTE 2: Please note the WHERE clause to identify the musym field is in the InterimQuery as well. If you use VBA or a parameter input, that is were the parameter goes.

 

[giz]

Thanks. This seems to work OK.