How to pull records BEFORE 1/1/2008

[jmarcrum]

Hi everyone,

I have a Budget table with two fields that I am concerned about (StartDate and CompleteDate). Items can be entered into the table more than once. Some items have StartDate = 3/4/2005 and CompleteDate = 4/4/2005, THEN, another record in the table lists that SAME itemID and lists its StartDate = 3/4/2008 and CompleteDate = 4/4/2008. So, there are two separate records!

What I want to do, is...If the StartDate >= 1/1/2008. then DON'T pull that StartDate = 3/4/2008 and CompleteDate = 4/4/2008 entry, but pull the MOST RECENT entry BEFORE that entry (in this case, it would be StartDate = 3/4/2005 and CompleteDate = 4/4/2005).

However, some itemID's have 4 or 5 multiple entries and not just 2. One has a StartDate = 6/9/1997 and CompleteDate = 7/9/1997, then the next entry is StartDate = 4/9/1999 and CompleteDate = 5/9/1999, and the NEXT entry is StartDate = 6/9/2003 and CompleteDate = 7/9/2003, and the LAST entry is StartDate = 6/9/2008 and CompleteDate = 7/9/2008.

Therefore, I only want to query the StartDate = 6/9/2003 and CompleteDate = 7/9/2003 entry!

Can this be done in a table such as I have?

Thanks for your help!!!

[FishVal]

Hi, jmarcrum.

I guess you need to select record with Max([CompleteField]) from those where [CompleteField]<#1/1/2008#

Regards,
Fish

[jmarcrum]

Hey Fish!

I try what you are suggesting in an Access query...

SELECT tblTreeTrimming .StartDate, tblTreeTrimming .CompleteDate, Max([StartDate]) AS MaxStartDate FROM tblTreeTrimming WHERE (([StartDate]<#1/1/2008#));

But it says that StartDate and CompleteDate are not part of an aggregate function?

[jmarcrum]

I used StartDate because it's more important than CompleteDate for the company