Simple Access Query

I have a table with the following data describing the schedule for a certain employee.  

ID* EmpID      EffectiveDate    InSched     Weekday
1    123           1/1/2000           9:30AM      Mon
2    123           1/1/2000           9:30AM      Tue
3    123           1/1/2000           9:30AM      Wed
4    123           1/1/2000           9:30AM      Thu
5    123           1/1/2000           9:30AM      Fri
6    123           1/1/2001           9:15AM      Mon    

I'd like the query to eliminate line 5 because line 6 shows a newer schedule for employee 123 for Mondays.

EffectiveDate and InSched fields are Date/Time fields.  The Weekday field is just short text.

Any help will be much appreciated.
Who is Participating?
You need 2 queries or query-in-query (you table is Table1 - replace with real name):
SELECT Table1.ID, Table1.EmpID, Table1.EffectiveDate, Table1.InSched, Table1.Weekday
FROM Table1 INNER JOIN (SELECT Table1.EmpID, Max(Table1.EffectiveDate) AS MaxOfEffectiveDate, Table1.Weekday
FROM Table1 GROUP BY Table1.EmpID, Table1.Weekday
ORDER BY Max(Table1.EffectiveDate)) as Query0 ON (Table1.Weekday = Query0.Weekday) AND (Table1.EffectiveDate = Query0.MaxOfEffectiveDate) AND (Table1.EmpID = Query0.EmpID);

Open in new window

omgangIT ManagerCommented:
Aren't you wanting to eliminate line 1 instead of 5?
OM Gang
colin911Author Commented:
Ha!  Yes, Sorry!  Trying to multi-task and I'm not good at that!
Eric ShermanAccountant/DeveloperCommented:
A bit confused by your data examples ...

Will each weekday share the same effective date?
Will it always be 5 days (Mon thru Fri) for each employee?

colin911Author Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.