Select Query Joined on Two Fields?

Question

I've got a few tables in an access database:

ID | LocationName
1  | Location1
2  | Location2

ID | LocationID | Date  | NumProductsDelivered
1  |     1      | 12/10 |      3
2  |     1      | 01/11 |      2
3  |     1      | 02/11 |      2
4  |     2      | 11/10 |      1
5  |     2      | 12/10 |      1

ID | LocationID | Date  | NumEmployees | EmployeeType
1  |     1      | 12/10 |      10      |      1 (=Permanent)
2  |     1      | 12/10 |       3      |      2 (=Temporary)
3  |     1      | 12/10 |       1      |      3 (=Support)
4  |     2      | 10/10 |       1      |      1
5  |     2      | 11/10 |       2      |      1
6  |     2      | 11/10 |       1      |      2
7  |     2      | 11/10 |       1      |      3
8  |     2      | 12/10 |       2      |      1
9  |     2      | 12/10 |       1      |      3

What I want to do is pass in the LocationID as a parameter and get back something like the following table. So, if I pass in 2 as my LocationID, I should get:

Date  | NumProductsDelivered | NumPermanentEmployees | NumSupportEmployees
10/10 |                      |           1           |           
11/10 |          1           |           2           |          1
12/10 |          1           |           2           |          1

It seems like this should be a pretty simple query. I really don't even need the first table except as a way to fill in the combo box on the form from which the user chooses which location they want a report for. Unfortunately, everything I've done has resulted in me getting a lot more data than I should be getting. My confusion is in how to set up the join (presumably that's what I'm looking for here) given that I want both the date and locationID to be the same for each row in the result set.

Any help would be much appreciated.

Thanks.

EDIT: Ok - the answer below didn't quite work, but it did set me on the right track and I was able to use the following query:

SELECT t1.Date, t2.NumProductsDelivered, 
  (SELECT t1a.NumEmployees
    FROM table3 t1a
    WHERE t1a.EmployeeType=1 AND t1a.LocationID=t1.LocationID AND t1a.Date= t1.Date)
  AS "PermEmps", 
  (SELECT t1b.NumEmployees
    FROM table3 t1b
    WHERE t1b.EmployeeType=3 AND t1b.LocationID=t1.LocationID AND t1b.Date=t1.Date)
  AS "SupportEmps"
FROM table3 AS t1 LEFT JOIN table2 AS t2 ON (t2.Date=t1.Date) 
  AND (t2.LocationID=t1.LocationID)
WHERE t1.LocationID=2
GROUP BY t1.Date, t1.LocationID, t2.NumProductsDelivered;

This is getting me the results I was looking for. However, in a case where the location has a break between products being delivered, I don't see the correct results. It seems that the recordset stops as soon as there's an empty row and then never picks back up again. So, where I might expect to see this:

Date  | NumProductsDelivered | NumPermanentEmployees | NumSupportEmployees
10/10 |                      |           1           |           
11/10 |          1           |           2           |          1
12/10 |          1           |           2           |          1
01/10 |          2           |                       |          1
06/10 |          1           |                       |

I only see this:

Date  | NumProductsDelivered | NumPermanentEmployees | NumSupportEmployees
10/10 |                      |           1           |           
11/10 |          1           |           2           |          1
12/10 |          1           |           2           |          1
01/10 |          2           |                       |          1

Answer 1

Something like this should work:

[deleted original]

Try this instead (untested):

select t3.date, t2.numproductsdelivered, 
  (select sum(t3.numemployees)
   from table3 t3a
   where t3a.locationid = t3.locationid and t3a.date = t3.date and t3a.employeetype = 1
  ) as numpermanentemployees,
  (select sum(t3.numemployees)
   from table3 t3b
   where t3b.locationid = t3.locationid and t3b.date = t3.date and t3b.employeetype = 3
  ) as numsupportemployees
from table3 as t3
left join table2 as t2 on t2.locationid = t3.locationid and t2.date = t3.date
where t3.locationid = 2
group by t3.date, t2.numproductsdelivered

If you didn't mind having separate rows for each employee type it could be simplified:

select t3.date, t2.numproductsdelivered, t3.employeetype, sum(t3.numemployees) as numemployees
from table3 as t3
left join table2 as t2 on t2.locationid = t3.locationid and t2.date = t3.date
where t3.locationid = 2 and t3.employeetype in (1, 3)
group by t3.date, t2.numproductsdelivered, t3.employeetype

Edit: Try this query:

SELECT t1.Date
FROM table3 AS t1
WHERE t1.LocationID=2
GROUP BY t1.Date

...and see if you get all the dates.

Then add the left join:

SELECT t1.Date, t2.NumProductsDelivered
FROM table3 AS t1 LEFT JOIN table2 AS t2 ON (t2.Date=t1.Date) 
  AND (t2.LocationID=t1.LocationID)
WHERE t1.LocationID=2
GROUP BY t1.Date, t1.LocationID, t2.NumProductsDelivered;

If it's doing a left INNER join, then it will remove rows from t1 that don't have a matching row in t2. Try explicitly setting a left OUTER join and see if that works. The RDBMS I've used the most defaults to outer, but maybe yours (Access) defaults to inner.

So I am thinking the following will work (add "OUTER" and remove "t1.LocationId"):

SELECT t1.Date, t2.NumProductsDelivered, 
  (SELECT t1a.NumEmployees
    FROM table3 t1a
    WHERE t1a.EmployeeType=1 AND t1a.LocationID=t1.LocationID AND t1a.Date= t1.Date)
  AS "PermEmps", 
  (SELECT t1b.NumEmployees
    FROM table3 t1b
    WHERE t1b.EmployeeType=3 AND t1b.LocationID=t1.LocationID AND t1b.Date=t1.Date)
  AS "SupportEmps"
FROM table3 AS t1 LEFT OUTER JOIN table2 AS t2 ON (t2.Date=t1.Date) 
  AND (t2.LocationID=t1.LocationID)
WHERE t1.LocationID=2
GROUP BY t1.Date, t2.NumProductsDelivered;

Answer 2

I think this would work:

DECLARE @LocationId int

SET @LocationId=2

SELECT L2.LocationId, L2.Date, COUNT(DISTINCT NumProductsDelivered) as NumProductsDelivered,
 SUM(case when L2.EmployeeType =1 then NumEmployees else 0 end) as NumPermanentEmployees,
 SUM(case when L2.EmployeeType =3 then NumEmployees else 0 end) as NumSupportEmployees
FROM L1 
    RIGHT JOIN L2
        ON L1.LocationID=L2.LocationID
        AND L1.Date=L2.Date
WHERE L2.LocationId=@LocationId
GROUP BY L2.LocationId, L2.Date