Struggling with SQL statement

[toast]

Hello all,

I'm struggling to get an SQL statement that works. Most of my (very limited) SQL knowledge comes from using query design and then switching to SQL view. That has got me through most things, but this current table design is stumping me.

I've attached a simplified copy of the table relationships.

What I would like to achieve is as follows:
A continuous form showing all the employees. Each row will show a total of the hours worked by that employee within the last 28 days (as either Loader or Driver). I'm doing this by setting the control source of a textbox to a function which returns the value of a SQL statement.

The bit I am struggling with is getting the SQL syntax to work. I have tried countless variations on the syntax, but I either get ambiguous outer join error messages, or the SQL runs but generates incomplete results.

If anybody could offer any pointers on what the SQL statement should look like (with any related queries required to overcome any ambiguous outer join issues) that would be fantastic.

 

[GohDiamond]

Please post the SQL statement that came closest to your desired results.
-Goh

 

[toast]

Edited because I found a solution

Here is the test data:

Employees (companyID, LastName)
001, PersonA
002, PersonB
003, PersonC

Shifts (shiftID, Driver, Loader, ShiftDuration,DaySheetID)
01, 001 (PersonA), 002 (PersonB), 01:30, 1
02, 003 (PersonC), 001 (PersonA), 00:45, 2
03, 002 (PersonB), 003 (PersonC), 00:15, 3

DaySheets (DaySheetID, DaySheetDate)
1, 01/07/12
2, 10/07/12
3, 11/07/12

So, ignoring the last 30 days the results should be:
001 (PersonA) 02:15 (2.25 as decimal hours)
002 (PersonB) 01:45 (1.7 as decimal hours)
003 (PersonC) 01:00 (1.0 as decimal hours)

DateQuery

SELECT DaySheets.DaySheetID, DaySheets.DaySheetDate
FROM DaySheets
WHERE (((DaySheets.DaySheetDate)>(Date()-30)));

Then actual query:

SELECT EmployeesShifts.companyID, Sum(EmployeesShifts.ShiftDuration)*24 
AS SumOfShiftDuration
FROM DateQuery
INNER JOIN EmployeesShifts ON DateQuery.DaySheetID = EmployeesShifts.DaySheetID
GROUP BY EmployeesShifts.companyID;