Ranking Dates in a Query Help

  • Thread starter Thread starter Lou
  • Start date Start date
L

Lou

Hello;

I have a [DateReceived] field in my query, I would like to
rank the dates starting at "1" in the query itself, how
would I go about this..?

Thank you
Lou
 
Lou,

If you are only going to have one value in the table for any
particular date, you can add a computed column to your query. You
will need to assign an alias for the table (I'll call my T).

DateRank: (SELECT COUNT(*) FROM yourTable WHERE DateReceived <=
T.DateReceived)

This could be slow, depending on how many records you have in your
query. If you have more than one record with the same DateReceived,
you will get multiple records with the same DateRank, and then will
skip the associated values (eg 1, 2, 2, 4, 5, 6, 6, 6, 9).


--
HTH

Dale Fye


Hello;

I have a [DateReceived] field in my query, I would like to
rank the dates starting at "1" in the query itself, how
would I go about this..?

Thank you
Lou
 
Heya Dale;

Can you put that in non SQL Terms as my SQL is null
really. Thank you for the help also. I just need it in a
query window look.

Thank you.
Lou
 
Hi,


SELECT COUNT(*) FROM yourTable
WHERE DateReceived <= T.DateReceived

spelled out as:

Count the number of records in table "yourTable" having the value in
their field DateReceived less than or equal to the value in the field
DateReceived of the record actually displayed (or actually "computed", or
actually under considerations for some reasons), in the table T (probably an
alias of the table "yourTable" ).

If we use language abusively, without all the precisions of the details,
in short, it is: having the number or records less or equal to the actual
record, that gives the rank (position) of the actual record (if position is
determined by the DateReceived value).



Hoping it may help,
Vanderghast, Access MVP

Lou said:
Heya Dale;

Can you put that in non SQL Terms as my SQL is null
really. Thank you for the help also. I just need it in a
query window look.

Thank you.
Lou
-----Original Message-----
Lou,

If you are only going to have one value in the table for any
particular date, you can add a computed column to your query. You
will need to assign an alias for the table (I'll call my T).

DateRank: (SELECT COUNT(*) FROM yourTable WHERE DateReceived <=
T.DateReceived)

This could be slow, depending on how many records you have in your
query. If you have more than one record with the same DateReceived,
you will get multiple records with the same DateRank, and then will
skip the associated values (eg 1, 2, 2, 4, 5, 6, 6, 6, 9).


--
HTH

Dale Fye


Hello;

I have a [DateReceived] field in my query, I would like to
rank the dates starting at "1" in the query itself, how
would I go about this..?

Thank you
Lou


.
Click to expand...
Click to expand...
 
Thank you, yes.
-----Original Message-----
Hi,


SELECT COUNT(*) FROM yourTable
WHERE DateReceived <= T.DateReceived

spelled out as:

Count the number of records in table "yourTable" having the value in
their field DateReceived less than or equal to the value in the field
DateReceived of the record actually displayed (or actually "computed", or
actually under considerations for some reasons), in the table T (probably an
alias of the table "yourTable" ).

If we use language abusively, without all the precisions of the details,
in short, it is: having the number or records less or equal to the actual
record, that gives the rank (position) of the actual record (if position is
determined by the DateReceived value).



Hoping it may help,
Vanderghast, Access MVP

Heya Dale;

Can you put that in non SQL Terms as my SQL is null
really. Thank you for the help also. I just need it in a
query window look.

Thank you.
Lou
-----Original Message-----
Lou,

If you are only going to have one value in the table
Click to expand...
for
any
particular date, you can add a computed column to your query. You
will need to assign an alias for the table (I'll call
Click to expand...
my
T).
DateRank: (SELECT COUNT(*) FROM yourTable WHERE DateReceived <=
T.DateReceived)

This could be slow, depending on how many records you have in your
query. If you have more than one record with the same DateReceived,
you will get multiple records with the same DateRank,
Click to expand...
and
then will
skip the associated values (eg 1, 2, 2, 4, 5, 6, 6, 6, 9).


--
HTH

Dale Fye


Hello;

I have a [DateReceived] field in my query, I would like to
rank the dates starting at "1" in the query itself, how
would I go about this..?

Thank you
Lou


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top