Solved

Simple Access Query

Posted on 2013-06-13
5
313 Views
Last Modified: 2013-06-13
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
Comment
Question by:colin911
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 28

Expert Comment

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

Author Comment

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

Expert Comment

by:Eric Sherman
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 40

Accepted Solution

by:
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);

Open in new window

0
 

Author Closing Comment

by:colin911
ID: 39245600
thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question