Re: Rank records in a query - crashes Access?
- From: jrtwynam@xxxxxxxxx
- Date: Mon, 17 Nov 2008 13:02:53 -0800 (PST)
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;
On Nov 17, 2:44 pm, "Michel Walsh"
<vanderghast@VirusAreFunnierThanSpam> wrote:
Remove the semi colon ending the sub-query..
Why the sub-query uses tbl1000_CompareRates while the main query uses
rank_qry0100 ?
- References:
- Rank records in a query - crashes Access?
- From: jrtwynam
- Re: Rank records in a query - crashes Access?
- From: Michel Walsh
- Rank records in a query - crashes Access?
- Prev by Date: Re: Expression when one portion may have no data
- Next by Date: Re: Calculating a week ending date
- Previous by thread: Re: Rank records in a query - crashes Access?
- Next by thread: Combining Queries
- Index(es):
Relevant Pages
|
