X
Xcelsoft
I have a question regarding joining two queries/tables
when transactions don't always exist in both tables.
Table1 has the Employee_ID Timeclock entry for the day,
i.e. Clock In, Clock Out, Hours Worked etc.
Table2 has the Employee_ID detail transactions for the day,
Project, Client and detail of work performed for the day.
The query to create the recordsource for the report will
be joined on the Employee_ID field but the problem is,
there will be days when Table2 will have detail
transactions and no Table1 timeclock entry because a
salaried employee contributed to the project and logged
his work against a project for tracking purposes. There
also will be days when Table1 will have a timeclock entry
with no corresponding Table2 detail transactions due to
vacation, Holiday and sick pay.
Currently, I created a Union Query "UnionQuery1" to select
the unique Employee_ID from Table1 and Table2. I then
used this Union Query and created an Outer Join on Table1
to include all records from UnionQuery1 and only those
from Table1 where the join fields are equal. Another
Outer Join on Table2 to include all records from
UnionQuery1 and only those from Table2 where the join
fields are equal.
This works and I don't see any problems.
Question: I this the suggested approach to accomplishing
this or can this be done all in one query???
Thanks
Xcelsoft
when transactions don't always exist in both tables.
Table1 has the Employee_ID Timeclock entry for the day,
i.e. Clock In, Clock Out, Hours Worked etc.
Table2 has the Employee_ID detail transactions for the day,
Project, Client and detail of work performed for the day.
The query to create the recordsource for the report will
be joined on the Employee_ID field but the problem is,
there will be days when Table2 will have detail
transactions and no Table1 timeclock entry because a
salaried employee contributed to the project and logged
his work against a project for tracking purposes. There
also will be days when Table1 will have a timeclock entry
with no corresponding Table2 detail transactions due to
vacation, Holiday and sick pay.
Currently, I created a Union Query "UnionQuery1" to select
the unique Employee_ID from Table1 and Table2. I then
used this Union Query and created an Outer Join on Table1
to include all records from UnionQuery1 and only those
from Table1 where the join fields are equal. Another
Outer Join on Table2 to include all records from
UnionQuery1 and only those from Table2 where the join
fields are equal.
This works and I don't see any problems.
Question: I this the suggested approach to accomplishing
this or can this be done all in one query???
Thanks
Xcelsoft