Ordering Dates

[Jason]

Folks,

I have a database table that includes a list of dates (weekdays only). I
built a query that returns unique dates only. I now want to add a second
field that numbers the dates with the most recent date being 1, the day
before 2, etc.....

The challenge is that I do not have weekends included, so I want the
numbering to be like this:

Jul 25th = 1
July 24th = 2
July 23rd = 3
July 20th = 4
July 19th = 5
etc...

Any ideas how I can add the 1, 2, 3, 4, 5, etc.......?

Much appreciated.

 

[Dale Fye]

Jason,

One method would be:

Select qryDates.DateField, Count(qryDates2.DateField)
FROM qryDates, qryDates as qryDates2
WHERE qryDates2.DateField >= qryDates.DateField
GROUP BY qryDates.DateField

I think you could also do this as:

Select qryDates.DateField, Count(qryDates2.DateField)
FROM qryDates
INNER JOIN qryDates as qryDates2
ON qryDates2.DateField >= qryDates.DateField
GROUP BY qryDates.DateField

The down side of this latter method is that you cannot display the query in
the query grid because of the non-equal join (don't know what the "correct"
term is for that).

You might want to try both to see which is faster. My thought is that the
second method would be faster because the JOIN limits the number of records
whereas in the first query, Access actually has to evaluate the where clause
of all of the combinations of dates that come from the Cartesian join of the
two tables.

HTH
Dale

 

[Jamie Collins]

The challenge is that I do not have weekends included, so I want the
numbering to be like this:

Jul 25th = 1
July 24th = 2
July 23rd = 3
July 20th = 4
July 19th = 5
etc...

Any ideas how I can add the 1, 2, 3, 4, 5, etc.......?

Something like this:

SELECT T1.effective_date, COUNT(*)
FROM Test AS T1, Calendar AS C1
WHERE C1.dt BETWEEN T1.effective_date AND
(
SELECT MAX(T2.effective_date)
FROM Test AS T2
)
AND C1.isWeekday = 'Y'
GROUP BY T1.effective_date;

For the Calendar table, see:

"Why should I consider using an auxiliary calendar table?"
http://www.aspfaq.com/show.asp?id=2519

Jamie.

--