Hey Access gurus:

I manage a time tracking tool for my organization. I'm trying to create a query that will tell me the number of times in a month that someone failed to report their time. Now, I have a function that will tell me the number of net working days in a month (which reduces 31 days for July down to 22 days) and then I use the function in my query to tell me the capacity hours for a month. I'm thinking that maybe that function would be useful in conjunction with counting the days that someone didn't report their time but I'm not sure how I could make that work... just a thought so far.

Here are the fields that I would use in my query. UserID, Date, Hours. If no time was reported on a certain day, the field would be blank.

The function that I use to count net working days is:

HTML Code:

---

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

---

I've played around with creating a crosstab query where User is the Row Heading, Date is the Column Heading, and Hours is the Value. It's nice to visually see the dates across the time and the hours reported or not reported but I'd like to be able to count the times hours weren't reported. I'm totally lost on how I can make this work... can I use the count function in the crosstab query?

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.

Quote:

---

Originally Posted by ScottGem

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:

Quote:

---

TRANSFORM IIf(IsNull(Sum([07-2012 Journyx Data dump].Hours)),0,Sum([07-2012 Journyx Data dump].Hours)) AS SumOfHours
SELECT [07-2012 Journyx Data dump].User, [07-2012 Journyx Data dump].[Team Lead], [BSS Employee Table].[Contr Flag]
FROM [BSS Employee Table] RIGHT JOIN ([07-2012 Journyx Data dump] INNER JOIN [Calendar Table_2012] ON [07-2012 Journyx Data dump].[Date Mod] = [Calendar Table_2012].Date) ON [BSS Employee Table].EmpID = [07-2012 Journyx Data dump].[Employee ID]
WHERE ((([Calendar Table_2012].WeekDay)="Y") AND (([07-2012 Journyx Data dump].[Jx Required])="Y"))
GROUP BY [07-2012 Journyx Data dump].User, [07-2012 Journyx Data dump].[Team Lead], [BSS Employee Table].[Contr Flag], [Calendar Table_2012].WeekDay, [07-2012 Journyx Data dump].[Jx Required]
PIVOT [Calendar Table_2012].Date;

---

Quote:

---

Originally Posted by jakester

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.

Quote:

---

Originally Posted by ScottGem

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.

---

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:

Code:

---

SELECT [Calendar Table_2012].Date, [07-2012 Journyx Data dump].User, Sum([07-2012 Journyx Data dump].Hours) AS SumOfHours, [Calendar Table_2012].WeekDay
FROM [Calendar Table_2012] RIGHT JOIN [07-2012 Journyx Data dump] ON [Calendar Table_2012].Date = [07-2012 Journyx Data dump].[Date Mod]
GROUP BY [Calendar Table_2012].Date, [07-2012 Journyx Data dump].User, [Calendar Table_2012].WeekDay
HAVING ((([Calendar Table_2012].Date) Between #7/1/2012# And #7/31/2012#) AND (([Calendar Table_2012].WeekDay)="Y"))
ORDER BY [Calendar Table_2012].Date, [07-2012 Journyx Data dump].User;

---

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

Quote:

---

Originally Posted by ScottGem

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?

Quote:

---

Originally Posted by ScottGem

Don't you just have employee numbers?

---

Maybe I'll just create some dummy data and attach that. Let me try that.

Quote:

---

Originally Posted by ScottGem

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

---

Here's a sample of the two tables. I had to zip it because .mdb isn't an allowed file type.