|
|
|
|
Senior Member
|
|
Aug 6, 2012, 09:57 AM
|
|
Question about Query Possibilities?
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?
|
|
|
Computer Expert and Renaissance Man
|
|
Aug 6, 2012, 10:38 AM
|
|
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.
|
|
|
Senior Member
|
|
Aug 14, 2012, 12:25 PM
|
|
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:
- 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?
|
|
|
Computer Expert and Renaissance Man
|
|
Aug 14, 2012, 12:38 PM
|
|
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,
|
|
|
Senior Member
|
|
Aug 15, 2012, 02:32 PM
|
|
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:
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;
|
|
|
Computer Expert and Renaissance Man
|
|
Aug 16, 2012, 04:57 AM
|
|
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.
|
|
|
Senior Member
|
|
Aug 16, 2012, 07:17 AM
|
|
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;
|
|
|
Computer Expert and Renaissance Man
|
|
Aug 16, 2012, 07:32 AM
|
|
Can you attach a file with the tables and sample data?
|
|
|
Senior Member
|
|
Aug 16, 2012, 07:41 AM
|
|
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.
|
|
|
Computer Expert and Renaissance Man
|
|
Aug 16, 2012, 07:58 AM
|
|
Don't you just have employee numbers?
|
|
|
Senior Member
|
|
Aug 16, 2012, 08:08 AM
|
|
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.
|
|
|
Senior Member
|
|
Aug 16, 2012, 08:21 AM
|
|
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.
|
|
Question Tools |
Search this Question |
|
|
Check out some similar questions!
Query Question for Access
[ 3 Answers ]
Hello - I'm trying to create a field in my query that will do the following:
Evaluate whether the Inactive Date field is either blank or has dates between: 7/1/12 and 7/31/12 and return the words "use for calculation"
The Inactive Date field in my table has blanks or sometimes it has dates....
Question about mutual break-up for better future possibilities.
[ 4 Answers ]
Haven't been on this board in a while but figured that I have some new stuff I could use a little perspective on.
So as a few of the main board members may know I had a break-up with my ex about 10 or so months ago give or take. Took a while for me to accept it but I was able to move forward...
What are my possibilities of being pregnant?
[ 4 Answers ]
I just have some questions concerning my possibilities of being pregnant. The first day of my last period (when I began spotting/bleeding) was back on Sept. 20 (I am on birth control, Nortrel). I have never had sexual intercourse, though a few months back my boyfriend and I tried. I experienced a...
How many possibilities
[ 4 Answers ]
So I was bored. And I was wondering...
How many possible combinations could you make of the numbers
1, 2, 3, 4
Like numbers like, 1234, 4321, 3241
I'm curious
DNS Query Query. A Query about DNS Queries...
[ 12 Answers ]
I am a software developer, I don’t know much about networks. I have a working network but with one weird (well to me it’s weird) problem. If you can offer any insight I’ll be very grateful!
<!--- Image Attachment Below (I couldn't find a way to paste it here in the editor :-( ) --->
...
View more questions
Search
|