Re: Ranking Dates

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Fri, 24 Aug 2007 18:37:07 GMT, "ekomsky via AccessMonster.com" <u36881@uwe>
wrote:

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

Relevant Pages

  • Re: Ranking Dates
    ... Which way to ranking goes doesn't matter. ... This isn't a SQL statement - it's a calculated field in a SQL statement. ...
    (microsoft.public.access.gettingstarted)
  • RE: Subquery help
    ... one semicolon and that is at the very end of the SQL statement. ... shouldn't need one in the subquery. ... Jerry Whittle, Microsoft Access MVP ... The query should be bringing back multiple references between the specified ...
    (microsoft.public.access.queries)
  • Re: Using Execute SQL Task to insert rows
    ... I still can't see why this error message is relevant to my SQL. ... > www.SQLDTS.com - The site for all your DTS needs. ... >> Subquery returned more than 1 value. ... >> Here's my SQL statement: ...
    (microsoft.public.sqlserver.dts)
  • Re: Another sequential query field problem... are you out the Duan
    ... You seem to have seen some solutions that involve a subquery. ... attempted in terms of your SQL statement and results? ... >> Duane Hookom ... >>> What I need to do is creat a field in my query that creates a sequence ...
    (microsoft.public.access.queries)
  • Re: Ranking performance is slow
    ... You can change the subquery with a Inner Join ... Even after completing the query, ... I created a ranking subquery that assigns 1-502 to each main ...
    (microsoft.public.access.queries)