Solved

# Simple Access Query

Posted on 2013-06-13
307 Views
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.
0
Question by:colin911

LVL 28

Expert Comment

ID: 39245447
Aren't you wanting to eliminate line 1 instead of 5?
OM Gang
0

Author Comment

ID: 39245495
Ha!  Yes, Sorry!  Trying to multi-task and I'm not good at that!
0

LVL 19

Expert Comment

ID: 39245550
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?

ET
0

LVL 39

Accepted Solution

als315 earned 500 total points
ID: 39245571
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);
``````
0

Author Closing Comment

ID: 39245600
thanks!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…