Numbering Rows on the fly in query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Numbering Rows on the fly in query

Author

Topic

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-06-26 : 13:29:27

I have the following access97 query that works fine and simply gives a calculated score for each company that meets certain conditions. I want to also be able to provide a rank column if possible. i.e. the highest score is rank 1, 2nd highest is rank 2 and so on down the list. Is there a way of doing this within the select statement such as in this post http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14758 (SQL Server post i know). I tried this but kept getting an illegal JOIN message and figured that perhaps Access97 wasn't able to cope with it.

Or am i better off appending the results of this query into a table ordered by score and then altering the table to add an autonumber field for the rank. The trouble with this method is if 2 scores are equal.

Any ideas greatly appreciated - many thanks

SELECT
[Categories with defaults].Custodian,
Avg([Categories with defaults].Score1) AS AvgOfScore1,
qryQualifiedCustodians.CountOfCustodyRelationshipID,
Regions.Region

FROM
(Regions
RIGHT JOIN Country
ON Regions.RegionID = Country.RegionID)
RIGHT JOIN ([Categories with defaults]
INNER JOIN qryQualifiedCustodians
ON [Categories with defaults].Custodian = qryQualifiedCustodians.Custodian)
ON Country.CountryID = [Categories with defaults].Survey.CountryID

GROUP BY
[Categories with defaults].Custodian,
qryQualifiedCustodians.CountOfCustodyRelationshipID,
Regions.Region

HAVING
(((Regions.Region)=[Region?]));

Paul

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-06-26 : 20:23:00

Hi Knookie

I think you're right about Access not being able to do this on the fly. Personally I'd head straight to VBA, called from a macro - which you can call from ASP - just before you query the results....

you'll find an in the access.application object you should be able to do acc.docmd.runmacro "macroname".

Sorry I don't have more time, I'll try to come back and explain some more later but I gotta go now....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-06-27 : 04:40:01

Yep - i've started heading down the VBA route already.

Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources