Rank records in a query - crashes Access?

[jrtwynam]

Hello.

I have a table that contains the following fields:

ORIGIN_ID (numerical ID for a given city)
DEST_ID (numerical ID for a given city)
ROUTE_RATE (dollar value)
CAT_ID (numerical ID for a given category)

I would like to create a query that ranks each category name within
each origin/destination combination by the route rate from lowest to
highest. If my data looks like this, for example:

ORIGIN_ID DEST_ID CAT_ID ROUTE_RATE
1 1 199 $82.74
1 1 224 $54.91
1 1 142 $56.62
1 1 767 $28.29
1 1 317 $40.45
1 1 196 $17.77
1 2 436 $10.50
1 2 255 $69.76
1 2 75 $76.66
1 2 610 $79.85
1 2 544 $96.22
1 2 470 $29.79


I would like to see these results:

ORIGIN_ID DEST_ID CAT_ID ROUTE_RATE RANK
1 1 196 $17.77 1
1 1 767 $28.29 2
1 1 317 $40.45 3
1 1 224 $54.91 4
1 1 142 $56.62 5
1 1 199 $82.74 6
1 2 436 $10.50 1
1 2 470 $29.79 2
1 2 255 $69.76 3
1 2 75 $76.66 4
1 2 610 $79.85 5
1 2 544 $96.22 6



I've tried to accomplish this using this query:

SELECT A.ORIGIN_ID, A.DEST_ID, A.ROUTE_RATE, A.CAT_NAME, (SELECT COUNT
(B.ORIGIN_ID) FROM tbl1000_CompareRates AS B WHERE B.ORIGIN_ID =
A.ORIGIN_ID AND B.DEST_ID = A.DEST_ID AND B.ROUTE_RATE <=
A.ROUTE_RATE AS RANK
FROM rank_qry0100 AS A;

But every time I run the query, it crashes Access. I can't figure out
why. I originally thought maybe I had used some reserved words for
field names (and it turns out I had - the ROUTE_RATE field was
formerly just called RATE), but changing those made no difference. Any
ideas?

 

[Michel Walsh]

Remove the semi colon ending the sub-query.

Why the sub-query uses tbl1000_CompareRates while the main query uses
rank_qry0100 ?


Vanderghast, Access MVP

 

[jrtwynam]

I just tried removing the semicolon from the subquery, but it didn't
make any difference.

rank_qry0100 just sorts the data into the appropriate order so I can
visually see what the ranking should be - originally, the both the
main query and the sub query were based on that, but I had changed the
subquery to use the source table when I was trying to figure out why
it was crashing.

The previous method I was using to do this, which involved writing
everything to temporary tables and looping through the tables to
update the rank, required the data to be in the proper order. Looking
at this method again, I realized that the data doesn't need to be
ordered properly for this to work, so there's no point in using
rank_qry0100. I changed that so that it reads from the main table, and
that seemed to solve the problem. I have no idea why, but it seemed to
work. This is my working SQL:

SELECT A.ORIGIN_ID, A.DEST_ID, A.ROUTE_RATE, A.CAT_NAME, ((SELECT COUNT
(B.ORIGIN_ID) FROM tbl1000_CompareRates AS B WHERE B.ORIGIN_ID =
A.ORIGIN_ID AND B.DEST_ID = A.DEST_ID AND B.ROUTE_RATE < A.ROUTE_RATE)
+ 1) AS RANK
FROM tbl1000_CompareRates AS A ORDER BY A.ORIGIN_ID, A.DEST_ID,
A.ROUTE_RATE;