By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,705 Members | 1,301 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,705 IT Pros & Developers. It's quick & easy.

Access Date lookup query help

P: 4
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
Mar 30 '07 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...
Expand|Select|Wrap|Line Numbers
  1. SELECT AdminID, DET_ID, Detachment_Name, TYPE,
  2. Max(Start Date) As Start Date, Max(End Date) As End Date
  3. FROM DET_Dates
  4. WHERE TYPE = 'VACATION'
  5. GROUP BY AdminID, DET_ID, Detachment_Name, TYPE;
  6.  
Mary
Apr 1 '07 #2

P: 4
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?

Expand|Select|Wrap|Line Numbers
  1.  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]
  2. FROM DET_Dates INNER JOIN PERSONNEL ON DET_Dates.Admin_ID = PERSONNEL.Admin_ID
  3. WHERE (((DET_Dates.TYPE)='vacation'))
  4. GROUP BY PERSONNEL.Admin_ID, DET_Dates.DET_ID, DET_Dates.Detachment_Name, DET_Dates.TYPE
Photo
Apr 2 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this without adding any other fields.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT PERSONNEL.Admin_ID, DET_Dates.TYPE,
  3. Max(DET_Dates.[Start Date]) AS [Start Date], 
  4. Max(DET_Dates.[End Date]) AS [End Date]
  5. FROM DET_Dates INNER JOIN PERSONNEL 
  6. ON DET_Dates.Admin_ID = PERSONNEL.Admin_ID
  7. WHERE (((DET_Dates.TYPE)='vacation'))
  8. GROUP BY PERSONNEL.Admin_ID, DET_Dates.TYPE
Apr 2 '07 #4

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



Try this without adding any other fields.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT PERSONNEL.Admin_ID, DET_Dates.TYPE,
  3. Max(DET_Dates.[Start Date]) AS [Start Date], 
  4. Max(DET_Dates.[End Date]) AS [End Date]
  5. FROM DET_Dates INNER JOIN PERSONNEL 
  6. ON DET_Dates.Admin_ID = PERSONNEL.Admin_ID
  7. WHERE (((DET_Dates.TYPE)='vacation'))
  8. GROUP BY PERSONNEL.Admin_ID, DET_Dates.TYPE
Apr 2 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Your Awsome, Thanks. That gave me exactly what I needed.
You're welcome.
Apr 2 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.