Ranking Dates

[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 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

All get in return is a count of all the dates ?

 

[John W. Vinson]

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.

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]

 

[John W. Vinson]

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

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]

 

[ekomsky via AccessMonster.com]

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]

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 ?

 

[ekomsky via AccessMonster.com]

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.

[quoted text clipped - 5 lines]

[Invoice_Date] is thefield i'm trying to rank

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 W. Vinson]

It kinda worked, why does it skip 1, and 11. It starts with 2 and goes to 10
then 12 and so on

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]