Query 3 tables and populate records

[raghuram.star]

Can you please help me query 3 tables and return the records.

Table 1 : Employee Data

Emp No  Name  Dept
123       Raghu  Fin
124       Ram    Hr
125       Cris   Ops
126       Giri   IT

Table 2 : Activity Data

Emp No    Name  Date     Hours
123       Raghu  1/2/2015   5
123       Raghu  1/3/2015   6
123       Raghu  1/4/2015   4
124       Ram    1/2/2015   5
124       Ram    1/3/2015   6
125       Cris   1/2/2015   5
125       Cris   1/3/2015   6
125       Cris   1/4/2015   2

Table 3 : Work Days

Date
1/2/2015
1/3/2015
1/4/2015

Relation between tables
Table 1 : Table 2
[Emp No]

Table 2 : Table 3
[Date]

I would love for a query to populate employe records who did not work in working days or having less work hours (less than 5 hours).

Final Table: Work Log

Emp No    Name   Dept  Date    Hours
123       Raghu   Fin  1/4/2015   4
124       Ram     Hr   1/4/2015   0
125       Cris    Ops  1/4/2015   2
126       Giri    IT   1/2/2015   0
126       Giri    IT   1/3/2015   0
126       Giri    IT   1/4/2015   0

Thank you very much in advance. I'm excited to hear what is possible!

Ram

 

[Joe4]

Here is query code that will return what you want:

SELECT X.*, Nz([Activity Data].Hours,0)+0 as Hours
FROM 
(SELECT [Emp No], Name, Date
 FROM [Employee Data], [Work Days])  AS X
LEFT OUTER JOIN [Activity Data]
ON X.[Emp No]=[Activity Data].[Emp No]
AND X.[Date]=[Activity Data].[Date]
WHERE (Nz([Activity Data].Hours,0)+0) < 5;

A few database design notes/tips:

1. You should not have the Name field in both the Employee Data and Activity Data tables. It should only Appear in the Employee Data table. If you are looking at the Activity Data table and want to see what the Employees name is, you can find that by linking to the Employee Data table on the Emp No field.

2. You should not used reserved words like "Name" and "Date" as field names. This ambiguity can lead to errors and unexpected results (Access may have trouble deciphering if you are referencing a field name you created or a method, property, function).

 

[raghuram.star]

Thanks Joe!