exclude the most recent date

[crowegreg]

I have a query which returns DISTINCT dates in descending order. Here is an example:
10/18/13
10/04/13


09/20/13
09/06/13
08/23/13

In two weeks from now, the results will be:
11/01/13
10/18/13
10/04/13
09/20/13
09/06/13
08/23/13


How do I exclude the most recent date? In the second example, I would not like 11/01/13 to be a part of the results.


Thanks!!

[JoeM]

You should be able to use the same subquery logic posted here in this response today: https://www.accessforums.net/queries...ory-38751.html

The only difference is, instead of returning records where the date is equal to the Max Value, you want to return the records where it is NOT equal to the Max Date value.

Give it a try, and see if you can figure it out. If not, post back here.

[crowegreg]

Thanks for the suggestion. Here's what I came up with.

SELECT DISTINCT PC_LOG.ReportDate
FROM PC_LOG
WHERE (((PC_LOG.ReportDate)<>(SELECT DISTINCT TOP 1 PC_LOG.ReportDate
FROM PC_LOG
ORDER BY PC_LOG.ReportDate DESC)))
ORDER BY PC_LOG.ReportDate DESC;

It works!!