Missing Timesheets

[mreference]

I have created a form that displays timesheets input by officers.

Officer Name | Week No | Month | Year | Hours Worked

It would be easier to know who hasn't completed a timesheet for a particular week so we can chase them up with a reminder.

The query underpining the form is made of a query and a table.

qryTimesheet for [finweek_nbr], [finmonthtext], [finyear_nbr], [sumOfhoursworked], [officerid]

tblOfficer for [OfficerName]

regards

 

[nathansav]

You need to use a subquey to be your criteria for the selection from the timesheets table

SELECT [_tbl_Staff].id, [_tbl_Staff].E_Name
FROM _tbl_Staff
WHERE [_tbl_Staff].id Not In (SELECT [_tbl_Timesheets].Staff_ID FROM _tbl_Timesheets WHERE[_tbl_Timesheets].Timesheet_Date=#01/08/2010#);


The subquery looks for all staff_ids with a time sheet on that date, and then we use this as a criteria in the "main" query to show which ones are not there.

Hope this helps

Can be tricky to get your head round subqueries, but they become invaluable

 

[nathansav]

or

SELECT [_tbl_Staff].*
FROM _tbl_Staff
WHERE (((Exists (Select [Staff_ID] from[_tbl_timesheets] Where [_tbl_Staff].[ID]=[_tbl_Timesheets].[Staff_id] AND [_tbl_Timesheets].[Timesheet_Date]=#01/08/2010#))=False));

 

[mreference]

Thanks for this, before trying it I have changed your code to this (swapped field names)

SELECT [tblOfficer].*
FROM tblOfficer
WHERE (((Exists (Select [officerid] from[qryTimesheetCheckerTotalsLINKER] Where [tblOfficer].[officerID]=[ qryTimesheetCheckerTotalsLINKER].[officerid] AND [qryTimesheetCheckerTotalsLINKER].[Timesheet_Date]=#01/08/2010#))=False));

The field in red is not included in my query anywhere.

Also, I'm trying to understand what is going on in the query are you able to explain the line WHERE....

 

[nathansav]

Timesheet date will be the field name for the date that the time sheet was entered in your table that holds details of time sheets entered.

The subquery (after the WHERE) gets the details from the timesheets table when there is an officer, but there is no corresponding time sheet record AND the timesheet enetered date is 8th Jan. Without the equals false bit you would show all who have entered a time sheet on that date.

 

[mreference]

I'm having some trouble getting this to work, I created a new query based on the tblTimesheetDetails and added two fields, officerid and timesheetDate.

I added this code to the criteria of the timesheetDate...

SELECT [tblOfficer].*
FROM tblOfficer
WHERE (((Exists (Select [officerid] from[tblTimesheetsDetails] Where [tblOfficer].[officerID]=[tblTimesheetsDetails].[officerid] AND [tblTimesheetsDetails].[timesheetDate]=#01/08/2010#))=False));

...but got an error message

The syntax of the subquery in this expreesion is incorrect
Check the subquery's syntax and enclose the subquery in parentheses

Looking at the subquery, will this only do it a day that is specified, or will it do all dates missing?

 

[nathansav]

you need a space after the FROM in the sub query

 

[nathansav]

Also, my dates were in american format, check that also on yours, so the date in the sub query is actually 8th Jan, i havent reveresed them to UK.

 

[mreference]

you need a space after the FROM in the sub query

I have a space after FROM, same error applies

 

[nathansav]

try

WHERE (Exists (Select [officerid] from[tblTimesheetsDetails] Where [tblOfficer].[officerID]=[tblTimesheetsDetails].[officerid] AND [tblTimesheetsDetails].[timesheetDate]=#01/08/2010#)=False);