Access Date lookup query help

[Photo]

Hello everyone this is my first post so as you could guess I need some help. Here is my situation, I have 2 tables, one caled personnel and one called DET_Dates, DET_Dates is linked to Personnel on Admin_ID. The Perrsonnel table has a list of names and dates pertaining to that person, the DET_Date table contains events and dates of events that each person in the Personnel table participated in. What I need to do and have been trying to do is build a query to single out the last time VACATION took place for each indifudual and calculate how long that event was. The sample DET_Date table is below and what I think I need the query return.

I could do this by going to the table and adding some type of identifer to the type like L_VACATION but that would mean more work for me or someone else down the road when that particular VACATION was no longer the last one.

I need some help, I already have some gray hair but getting more from this.

Thanks,
Photo

Outcome I need
Admin_ID******* ******* DET_ID***** Detachment_Name *************** Start Date ***End Date *******TYPE**** *Length
12 *************** ********240 *************** **C************ ************ 23-Jan-06* ***03-Feb-06**** VACATION****10
14 *************** ******** 67 *************** ** F************** *********** 01-Jan-07* ** 29-Jan-07**** VACATION****29
33 *************** ********48 *************** ** H************** *********** 01-Jan-07* ** 10-Jan-07**** VACATION*****10

Sample DET_Dates
Admin_ID******* ******* DET_ID***** Detachment_Name *************** Start Date ***End Date *******TYPE
1 *************** ******** *44 *************** ** A************** *********** 13-Feb-06* ** 15-Mar-06**** SCHOOL
12 *************** ********238 *************** * B************** *********** 01-Jul-05* ** 30-Jul-05******* VACATION
12 *************** ******** 240 *************** *C *************** ********* 23-Jan-06* ** 03-Feb-06**** VACATION
12 *************** ******** 250 *************** *D************* ************ 01-Mar-06* ** 05-Mar-06**** TEACH
14 *************** ******** 63 *************** **E *************** ********** 23-Jan-06* ** 29-Jan-06**** VACATION
14 *************** ******** 67 *************** **F *************** ********** 01-Jan-07* ** 29-Jan-07**** VACATION
33 *************** ******** 49 *************** **G *************** ********** 20-Jan-06* ** 31-Jul-06**** VACATION
33 *************** ********48 *************** ***H*********** ************** 01-Jan-07* **10-Jan-07**** VACATION
33 *************** ********46 *************** ** I *************** ********** 20-Jan-06* ** 31-Jul-06**** SCHOOL

[MMcCarthy]

Try this ...

SELECT AdminID, DET_ID, Detachment_Name, TYPE,
Max(Start Date) As Start Date, Max(End Date) As End Date
FROM DET_Dates
WHERE TYPE = 'VACATION'
GROUP BY AdminID, DET_ID, Detachment_Name, TYPE;
 

Mary

[Photo]

Thanks for the help, I tried the code but I had to add some additional info to get it to do anything at all. Now I runs but list every instance of "vacation" for each person and I just need the very last one. Any suggestions?

 SELECT PERSONNEL.Admin_ID, DET_Dates.DET_ID, DET_Dates.Detachment_Name, DET_Dates.TYPE, Max(DET_Dates.[Start Date]) AS [Start Date], Max(DET_Dates.[End Date]) AS [End Date]
FROM DET_Dates INNER JOIN PERSONNEL ON DET_Dates.Admin_ID = PERSONNEL.Admin_ID
WHERE (((DET_Dates.TYPE)='vacation'))
GROUP BY PERSONNEL.Admin_ID, DET_Dates.DET_ID, DET_Dates.Detachment_Name, DET_Dates.TYPE

Photo

[MMcCarthy]

Try this without adding any other fields.

 
SELECT PERSONNEL.Admin_ID, DET_Dates.TYPE,
Max(DET_Dates.[Start Date]) AS [Start Date], 
Max(DET_Dates.[End Date]) AS [End Date]
FROM DET_Dates INNER JOIN PERSONNEL 
ON DET_Dates.Admin_ID = PERSONNEL.Admin_ID
WHERE (((DET_Dates.TYPE)='vacation'))
GROUP BY PERSONNEL.Admin_ID, DET_Dates.TYPE

[Photo]

Your Awsome, Thanks. That gave me exactly what I needed.

Try this without adding any other fields.

 
SELECT PERSONNEL.Admin_ID, DET_Dates.TYPE,
Max(DET_Dates.[Start Date]) AS [Start Date], 
Max(DET_Dates.[End Date]) AS [End Date]
FROM DET_Dates INNER JOIN PERSONNEL 
ON DET_Dates.Admin_ID = PERSONNEL.Admin_ID
WHERE (((DET_Dates.TYPE)='vacation'))
GROUP BY PERSONNEL.Admin_ID, DET_Dates.TYPE

[MMcCarthy]

Your Awsome, Thanks. That gave me exactly what I needed.

You're welcome.