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!!!