Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

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.
0
colin911
Asked:
colin911
1 Solution
 
omgangCommented:
Aren't you wanting to eliminate line 1 instead of 5?
OM Gang
0
 
colin911Author Commented:
Ha!  Yes, Sorry!  Trying to multi-task and I'm not good at that!
0
 
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?

ET
0
 
als315Commented:
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
 
colin911Author Commented:
thanks!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now