Ranking Dates

  • Thread starter Thread starter ekomsky via AccessMonster.com
  • Start date Start date
E

ekomsky via AccessMonster.com

I have a table called MLS DATA i am trying to rank the dates in it. i just
want a value 1 being the highest date and or the lowest, and so forth.
Which way to ranking goes doesn't matter. Here is my SQL Statement. It
won't work. Thank you in advace for any help.

DateRank: (SELECT COUNT(*) FROM [MLS DATA] WHERE [Invoice_Date]<=[MLS DATA].
[Invoice_Date])


[Invoice_Date] is thefield i'm trying to rank
 
ekomsky said:
I have a table called MLS DATA i am trying to rank the dates in it. i just
want a value 1 being the highest date and or the lowest, and so forth.
Which way to ranking goes doesn't matter. Here is my SQL Statement. It
won't work. Thank you in advace for any help.

DateRank: (SELECT COUNT(*) FROM [MLS DATA] WHERE [Invoice_Date]<=[MLS DATA].
[Invoice_Date])

[Invoice_Date] is thefield i'm trying to rank
Click to expand...


All get in return is a count of all the dates ?
 
I have a table called MLS DATA i am trying to rank the dates in it. i just
want a value 1 being the highest date and or the lowest, and so forth.
Which way to ranking goes doesn't matter. Here is my SQL Statement. It
won't work. Thank you in advace for any help.
Click to expand...

This isn't a SQL statement - it's a calculated field in a SQL statement. I'm
confused about which field is which - could you post the complete statement?

John W. Vinson [MVP]
 
I have a table called MLS DATA i am trying to rank the dates in it. i just
want a value 1 being the highest date and or the lowest, and so forth.
Which way to ranking goes doesn't matter. Here is my SQL Statement. It
won't work. Thank you in advace for any help.

DateRank: (SELECT COUNT(*) FROM [MLS DATA] WHERE [Invoice_Date]<=[MLS DATA].
[Invoice_Date])


[Invoice_Date] is thefield i'm trying to rank
Click to expand...

Hah. Second looks are always useful...

This subquery is returning the count of records for which the invoice date is
less than or equal to itself... i.e. every record in the table. If you were to
change the <= to < then you would get zero hits, since no date is less than
itself!

You need to reference the [MLS DATA] table with an alias, so that you have two
instances of it - the one in the "outer" query and the one in the subquery.
Try

DateRank: (SELECT COUNT(*) FROM [MLS DATA] AS SQ WHERE
[SQ].[Invoice_Date]<=[MLS DATA].[Invoice_Date])


John W. Vinson [MVP]
 
John said:
This isn't a SQL statement - it's a calculated field in a SQL statement. I'm
confused about which field is which - could you post the complete statement?

John W. Vinson [MVP]
Click to expand...


All i'm trying to do is rank dates in a query, that i'm pulling in form MLS
DATA, which is a table. Do you know of a way to do that ?
 
It kinda worked, why does it skip 1, and 11. It starts with 2 and goes to 10
then 12 and so on
I have a table called MLS DATA i am trying to rank the dates in it. i just
want a value 1 being the highest date and or the lowest, and so forth.
Click to expand...
[quoted text clipped - 5 lines]
[Invoice_Date] is thefield i'm trying to rank
Click to expand...

Hah. Second looks are always useful...

This subquery is returning the count of records for which the invoice date is
less than or equal to itself... i.e. every record in the table. If you were to
change the <= to < then you would get zero hits, since no date is less than
itself!

You need to reference the [MLS DATA] table with an alias, so that you have two
instances of it - the one in the "outer" query and the one in the subquery.
Try

DateRank: (SELECT COUNT(*) FROM [MLS DATA] AS SQ WHERE
[SQ].[Invoice_Date]<=[MLS DATA].[Invoice_Date])

John W. Vinson [MVP]
Click to expand...
 
It kinda worked, why does it skip 1, and 11. It starts with 2 and goes to 10
then 12 and so on
Click to expand...

There are probably ties - two records with the same date. Is there some other
field (an autonumber perhaps?) which could be used as a tiebreaker, or which
you can be sure is unique and in strictly ascending order?

John W. Vinson [MVP]
 
Back
Top