Access nested select with left join question

[shawnash]

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 name Dates has only 1 column with dates way into the future.

Second table name TotHoursQuery (which is acutally a query) has Empl Name, Date worked, and hours worked.

The code I have so far works sort of, but prompts for empl_name and cal_date (for some reason) - and the expected end date, but no start date. If I put nothing in the prompts except put date in end date, it comes back with the original data that is in TotHoursQuery and adds the empl_name and cal_date fields to the view, with nothing in them. My code below..

SELECT * 
FROM (SELECT DISTINCT Nested.empl_name, Nested.cal_date 
FROM Dates, TotHoursQuery 
      WHERE Nested.cal_date between [start date] and [end date]) AS Nested 
LEFT JOIN TotHoursQuery 
      ON (Nested.cal_date = TotHoursQuery.[Start Date]) 
      AND (Nested.empl_name = TotHoursQuery.[Full Name]);

[NeoPa]

Hi Shawn.

I think you need to get the basics right first before trying to move on.

When you have a set of dates in one table that reflect your date range, plus more at each end possibly, then you start with a simple query filtered (WHERE clause) by the date range. If you want to add to that the data from another table that may or may not overlap with your selected dates then you add it as a LEFT JOIN to the date table.

Your existing attempt seems to employ the cartesian approach. I see no reason you'd want that, but maybe I don't understand your situation clearly enough.

[shawnash]

Thanks for the reply. I started over with the code below which almost works. It only brings back record of dates that are not used by any employee. What I need is for each employee, I need all dates in the range. Even if the employee did not work that date. So basically, I need the equivalent of a For loop in a sql statement. This is for a time sheet that will be printed for each employee.

SELECT a.Field1, b.[Start Date], b.[Full Name], b.[Employee ID], b.[Location], b.[Sum Of Duration]
FROM Dates AS a LEFT JOIN TotHoursQuery AS b ON b.[Start Date] = a.Field1
WHERE a.Field1 >= [Enter Start Date: - yyyy-mm-dd] AND  a.[Field1] <= [Enter End Date: - yyyy-mm-dd]
ORDER BY b.[Full Name];

[NeoPa]

Oh. You want a full set of dates for each employee, where each employee has data covering a whole set of dates itself. That's a whole different thing from what I understood first time round.

First of all, let's introduce you to the Between construct as used in WHERE clauses.

WHERE [DateField] <= X AND [DateField] >= Y

is equivalent to :

WHERE [DateField] Between X And Y

Now, your requirement means you need a set of data on the LEFT side of your JOIN which contains a set of dates for each employee. Once you have that you can follow the same principle as before. LEFT JOIN that source of data, that is probably going to be two tables linked together by an INNER JOIN and tied together using parentheses, to the data containing your employee dates (probably [TotHoursQuery]) and you should have what you require.

[shawnash]

NeoPa, thank you so much! It took me a little while, but I now have what I need.

[NeoPa]

Pleased to help Shawn :-)