Question about Query Possibilities?

Function NetWorkDays(STARTDATE As Date, ENDDATE As Date) As Integer

Dim TESTDATE As Date

NetWorkDays = 0

TESTDATE = STARTDATE

While TESTDATE <= ENDDATE

    If Not ((Weekday(TESTDATE) = 7) Or (Weekday(TESTDATE) = 1)) Then
        NetWorkDays = NetWorkDays + 1
    End If 

TESTDATE = TESTDATE + 1
    
Wend

End Function 

SELECT * FROM table
WHERE Date BETWEEN startdate and Enddate AND Hours =0;

That will give you a list of people with no reported hours. However it gets more complicated if they might not have a record for the day. In that case you need a table of working days and you need to do an Unmatched query between you time log and the # of working days for the month.

Hi Scott - I created a table for 2012 that lists all 365 days and tells me whether it is a weekday or not (Weekday Indicator = Y or N).

I also have my source data (or time log as you called it). That source data has a date field in it, too.

I'm having a problem getting the results I want in my query. SourceData_tbl has 3 fields that I am interested in:

1. Date Logged
   EmployeeID
   Hours

CalendarTbl has the Datefield.

The problem with SourceData_tbl is that if someone did not report his time, no record shows up.

So, the only way that I figure I can identify whether someone reported time or not is to bring all of the data into a pivot table and count all of the blanks there, with the dates as column headers and Users in the rows with the hours in the Data area. Whenever I'd tried to do a query to get some results, I get an ambiguous outer join error.

The only other scenario I can think of is a crosstab query that puts dates in the column headers and users in the rows, which saves me a step in having to create a pivot table but I still have to count the blanks in another step.

You mentioned the unmatched query but I'm not certain how I could make this work. I do have another table which is the Employee_tbl which has all active employees who are supposed to report time. Could I use that in conjunction with the Calendar_tbl and the SourceData_tbl so find out which employees didn't report time?

The unmatched just needs the 2 tables. You would have to this by employee, but you are looking for dates in the calendar table where there is no matching record in SourceData,.

Scott - I put together a crosstab query instead. I have two things that I would like to try to do in the query.

1. Count the zeros
2. Count the times less than 8 hours was reported

Is that possible?

Here is the SQL code for the query I have. I did try to use

Count([Hours]=0) but the result doesn't make any sense. It's calculating something but I'm not sure how it is behaving:

I Think you will need to first create a query that counts the zeros and less than 8s. This should be easy. Then use that query as the source of your crosstab or have multiple crosstabs.

Ok, Scott...I've tried to solve for this query but I'm not certain this works. You're right, because a person doesn't have a record for a day he didn't report time, it is complicated.

I have two tables: table of working days and my time log table. How would I set this query up?

If I join on the date fields which are found in both tables and say "Include ALL Records from 'Calendar Table" and only those records from 'TimeLog' where joined fields are equal" and bring the Date field from the Calendar Table, UserName from the TimeLog table and the Hours from the TimeLog table, I don't see any missing data; nor can I immediately tell who failed to report time. So am I not setting up the query correctly?

In this example, I'm only interested in July data. Here is my code:

Can you attach a file with the tables and sample data?

I wish I could but there is sensitive employee information in them. I appreciate your help, though.

Don't you just have employee numbers?