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.