ranking a query based on unique symbol

G

Guest

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
 
M

Marshall Barton

Such a strange thing to do! And it deserves a strange query
to get those results ;-)

SELECT
(SELECT Count(*)
FROM [SELECT DISTINCT siteiid FROM yourquery AS Y]. AS X
WHERE X.siteiid <= yourquery.siteiid
) AS Rank,
yourquery.musym,
yourquery.siteiid,
FROM yourquery
 
G

Guest

yes, it does seem like it is a strange thing to do, but believe it or not it
will come in functional later on for myself...

It looks like this is a new query to be conducted on the original query
which obtains the desired data, rather than a subquery to be written in the
"Field" box within the original query design view. Correct? I assumed that
was the case and opened a new query, then inserted the code in the SQL view,
changed the necessary parameters, etc. Upon running it received the error
dialog

"The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect."

The last "FROM" in the statement was then highlighted, indicating (I think)
that the query ran fine until that point, then bogged down. My version is
below:

SELECT
(SELECT Count(*)
FROM [SELECT DISTINCT siteiid FROM simple_excel_export AS Y]. AS X
WHERE X.siteiid <= simple_excel_export.siteiid
)AS Rank,
simple_excel_export.musym,
simple_excel_export.siteiid,
FROM simple_excel_export

I can't seem to figure it out...


Marshall Barton said:
Such a strange thing to do! And it deserves a strange query
to get those results ;-)

SELECT
(SELECT Count(*)
FROM [SELECT DISTINCT siteiid FROM yourquery AS Y]. AS X
WHERE X.siteiid <= yourquery.siteiid
) AS Rank,
yourquery.musym,
yourquery.siteiid,
FROM yourquery
--
Marsh
MVP [MS Access]


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
Click to expand...
Click to expand...
 
M

Marshall Barton

I don't see anything wrong either. The same query worked
fine for me (using AXP/JET) except my query name was shorter
without the underscores.

Unlikely, but maybe there was a paste error, so, to grasp at
a straw, try retyping it???

Maybe we've run into a blind spot and we need someone else
to spot the problem.
--
Marsh
MVP [MS Access]

It looks like this is a new query to be conducted on the original query
which obtains the desired data, rather than a subquery to be written in the
"Field" box within the original query design view. Correct? I assumed that
was the case and opened a new query, then inserted the code in the SQL view,
changed the necessary parameters, etc. Upon running it received the error
dialog

"The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect."

The last "FROM" in the statement was then highlighted, indicating (I think)
that the query ran fine until that point, then bogged down. My version is
below:

SELECT
(SELECT Count(*)
FROM [SELECT DISTINCT siteiid FROM simple_excel_export AS Y]. AS X
WHERE X.siteiid <= simple_excel_export.siteiid
)AS Rank,
simple_excel_export.musym,
simple_excel_export.siteiid,
FROM simple_excel_export


Marshall Barton said:
Such a strange thing to do! And it deserves a strange query
to get those results ;-)

SELECT
(SELECT Count(*)
FROM [SELECT DISTINCT siteiid FROM yourquery AS Y]. AS X
WHERE X.siteiid <= yourquery.siteiid
) AS Rank,
yourquery.musym,
yourquery.siteiid,
FROM yourquery

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

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

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

calculating count in textbox 4
Select data and sum by weeks in a month 3
sum column a if column b, c, d, and e are equal 4
Rank records in a query - crashes Access? 2
Combining same data in multiple rows 4
Ranking (Look for previous ranking) 3
wish there was a CountDistinct() function 6
Query that will assign a sequence number 1

Top