How to compare combination of date and time in 2 tables

[accessLearn]

I have 2 tables in ms access, one with employee information with employeeid, date_work, time_work and joining_dt
Employee can work in different departments any-day and anytime
Other table is department table which shows on a particular date and time the department employee was in with Employeeid, and departmentname,effective_dt ,effective_tm fields. so this table shows the department name for a particular date and time.
This table has several records for each employee for different dates with different department




I need to write a query in ms access 2003 to display for each employee for the combination of (date_work and time_work), the department employee was working.
How can I do that?


Thanks!!

[CJ_London]

struggling to understand what data you have in your tables - can you provide an example together with the required output from that example.

Best I can suggest at the moment is look at using Between

effective_dt between date1 and date2

[accessLearn]

The data format is below:


employeeid effective_dt effective_tm department


123 12/28/2014 2:24:00 PM Finance


123 12/31/2014 6:54:00 PM SharedServices


123 01/02/2015 10:31:00 AM IT


123 01/04/2015 11:59:00 PM Education


246 12/29/2014 12:24:00 PM IT


246 12/31/2014 2:54:00 PM Systems


246 01/01/2015 11:31:00 AM Education


246 01/03/2015 10:10:00 Pm Education


246 01/05/2015 9:45:00 AM SharedServices


the service date and time for 123 is 01/02/2015 9:30:00 am


so I want the department to be SharedServices as at 10:31:00 Am, he moves to It.


The service date and time for 246 is 01/01/2015 3:45 pm , so I want to get the department as


Education. if the services date would have been 01/04/2015 , 3:35 pm, the department I want to


be Shared services.

How can I do that in the query?

Thanks!!

[CJ_London]

It would be better if your date and time were combined in your table to simplify things a bit

But try something like

SELECT employeeid, department
FROM department_table
WHERE employeeid=123 and [effective_dt]+[effective_tm] =(SELECT Max([effective_dt]+[effective_tm]) FROM department_table as T WHERE employeeid=123 and [effective_dt]+[effective_tm]<[enter date]+[enter time])