Max Date Query

[gnortenjones]

I have a problem I'm trying to solve, but for the life of me, cannot figure out how to get it to work.

I have two tables, each with a "Last Update Date" field. The query I want to create would match each "Last Update Date" field for table one, with the most recent date prior to that from table two.

For example. Table one would have records for 5/1/07, I would want the most recent date prior to that from table two (which aren't necessarily the day before).

I've used the "max" function, but can't get more than one date to display. I would need the prior date for every record in table one.

Thanks.

[Rabbit]

Just set the criteria for the date in Table 2 to < Table1's Date.

[gnortenjones]

Just set the criteria for the date in Table 2 to < Table1's Date.

That pulls in ALL the dates prior to the table 1 date. I only want the one date that's immediately prior to it.

Sorry if I didn't make that clear.

[Rabbit]

Then SELECT TOP 1 and order it by descending date.

[gnortenjones]

Then SELECT TOP 1 and order it by descending date.

That only gives me the date for one record. I needs the immediately prior date for EACH record from table one. Let me give you an example of the dataset:

table1 table2
5/1/07 5/1/07
5/1/07 4/30/07
5/1/07 4/15/07
5/1/07 4/14/07
4/30/07 4/30/07
4/30/07 4/26/07
4/30/07 4/24/07
4/26/07 4/26/07
4/26/07 4/19/07
4/26/07 4/16/07

So the records I would want returned would be
5/1/07 4/30/07
4/30/07 4/26/07
4/26/07 4/19/07

Does that help?

[Rabbit]

Now you're getting into complicated territory. You're going to have to SELECT DISTINCT and use a subquery or DMax to achieve what you want.

[gnortenjones]

Now you're getting into complicated territory. You're going to have to SELECT DISTINCT and use a subquery or DMax to achieve what you want.

Yep. Already figured out how complicated this is turning out to be. :)

[Rabbit]

Yep. Already figured out how complicated this is turning out to be. :)

Let us know if you get stuck on any part of that. First you have to decide if you want to go the DMax route or the subquery route.

DMax is easier but not as efficient.