Name: Date:
RyanCorp. 2003
RyanCorp. 2008
ChetCorp. 2020
ChetCorp. 2015
VinceCorp. 2002
VinceCorp. 2003

Is there any way to query these 6 records to only show the 3 different corporations AND only show the one that has a DATE CLOSEST TO 5 YEARS FROM NOW??
for example, to get:
Ryancorp. 2008
ChetCorp 2015
VinceCorp. 2003

I am a new Access user and any help would be greatly appreciated.
Thank you very much.

Okie Dokie - Nested queries, meaning, first, we have to find out what the difference is between the dates and 5 years from NOW (figuring NOW is the current date). In the same query, we will find the minimum absolute value of the difference.
I created a table called Table1. Do the same and copy this code into the SQL view in the design view and save it as Query6

SELECT Table1.Field1, Min(Abs([field2]-DatePart('yyyy',DateAdd('yyyy',5,Date())))) AS TheDiff
FROM Table1
GROUP BY Table1.Field1;

We then have to find the dates that are the ones we just calculated:

Create a new query and paste this code in.

SELECT Table1.Field1, Table1.Field2, Query6.TheDiff
FROM Table1 INNER JOIN Query6 ON Table1.Field1 = Query6.Field1
WHERE (((Table1.Field2)=DatePart('yyyy',DateAdd('yyyy',5 ,Date()))-[thediff])) OR (((Table1.Field2)=DatePart('yyyy',DateAdd('yyyy',5 ,Date()))+[thediff]));


HTH



[This message has been edited by pdx_man (edited 10-25-2001).]