query difference between tables

[pl456]

struggling with this, hope someone can help.

I have a table of timesheets that contains records for staff but it only contains weekly record if they have actually created that weeks record.
I also have a table of weeks that contains all of the available weeks that time can be booked against.

How can I query the timesheet table against the weeks table and get a list of staff that have not created a record for that week ?

 

[neileg]

You need one more dataset - a set containing every employee that you want data about.

Then in a query join the weeks table to the week field in the timesheet table and the names table to the name field in the timesheet table. The default join in Access is an inner join and requires data on both sides of the join to get a result. You need to change this for both joins so you get all of the records from weeks and all of the records from names and any records from timesheets that match. This will produce a result for every name for every week. If there is no data from timesheets, then these fields will be null. Use Is Null as a criterion against the timesheet data.

Job done.

 

[pl456]

it says it contains ambigious outer joins. ???

 

[neileg]

Silly me! Yes you need to join weeks and staff first. See the attached.

 

[pl456]

do you have this in 97 format?

 

[neileg]

No, sorry. But here's the sql of the two queries:

qryStaffWeeks
SELECT tblStaff.StaffID, tblStaff.StaffName, tblWeeks.WeekID, tblWeeks.WeekEndDate
FROM tblStaff, tblWeeks;

Note there is no join in this query. That is deliberate so that you get a record for every staff member for every week.

qryMissingData
SELECT qryStaffWeeks.StaffID, qryStaffWeeks.StaffName, qryStaffWeeks.WeekID, qryStaffWeeks.WeekEndDate, tblTsheet.TimesheetID, tblTsheet.StaffID, tblTsheet.WeekID, tblTsheet.TimesheetData
FROM qryStaffWeeks LEFT JOIN tblTsheet ON (qryStaffWeeks.WeekID = tblTsheet.WeekID) AND (qryStaffWeeks.StaffID = tblTsheet.StaffID)
WHERE (((tblTsheet.TimesheetID) Is Null));