By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,052 Members | 1,456 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,052 IT Pros & Developers. It's quick & easy.

Access nested select with left join question

P: 3
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..

Expand|Select|Wrap|Line Numbers
  1. SELECT * 
  2. FROM (SELECT DISTINCT Nested.empl_name, Nested.cal_date 
  3. FROM Dates, TotHoursQuery 
  4.       WHERE Nested.cal_date between [start date] and [end date]) AS Nested 
  5. LEFT JOIN TotHoursQuery 
  6.       ON (Nested.cal_date = TotHoursQuery.[Start Date]) 
  7.       AND (Nested.empl_name = TotHoursQuery.[Full Name]);
Apr 3 '17 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. WHERE [DateField] <= X AND [DateField] >= Y
is equivalent to :
Expand|Select|Wrap|Line Numbers
  1. 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.

Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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.
Apr 4 '17 #2

P: 3
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT a.Field1, b.[Start Date], b.[Full Name], b.[Employee ID], b.[Location], b.[Sum Of Duration]
  2. FROM Dates AS a LEFT JOIN TotHoursQuery AS b ON b.[Start Date] = a.Field1
  3. WHERE a.Field1 >= [Enter Start Date: - yyyy-mm-dd] AND  a.[Field1] <= [Enter End Date: - yyyy-mm-dd]
  4. ORDER BY b.[Full Name];
Apr 4 '17 #3

NeoPa
Expert Mod 15k+
P: 31,494
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.
Expand|Select|Wrap|Line Numbers
  1. WHERE [DateField] <= X AND [DateField] >= Y
is equivalent to :
Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 5 '17 #4

P: 3
NeoPa, thank you so much! It took me a little while, but I now have what I need.
Apr 5 '17 #5

NeoPa
Expert Mod 15k+
P: 31,494
Pleased to help Shawn :-)
Apr 6 '17 #6

Post your reply

Sign in to post your reply or Sign up for a free account.