ms access query to join date table to Timesheet table

Question

My end result is a report of multiple employees that includes days worked in a date range. In my report, I need to have all dates in the range showing on the report whether the employee worked it or not.

So I have a table with dates worked and hours for each empl. I also created a table with dates far into the future. I think I need a query that joins the two tables and puts all dates for the date range - that will be prompted. added to the table of employee time worked.

Does anyone have any ideas how to join these tables and get all dates for each employee in the two week range that will be prompted?

First table Dates has only 1 column with dates way into the future.

Second table has Empl Name, Date worked, and hours worked.

Answer 1

You will need to use a nested query (aka. sub query) to create all combinations of employees and calendar days then you can do a left to your second table (timesheet table).

select *
from (select distinct empl_name, cal_date from first_tbl, second_tbl
      where cal_date between [start date] and [end date]) as nested
left join second_tbl
    on  nested.empl_name = table_2.empl_name
    and nested.cal_date = table_2.work_date