Ask Experts Questions for FREE Help !
Ask
    jakester's Avatar
    jakester Posts: 582, Reputation: 165
    Senior Member
     
    #1

    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?
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #2

    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.
    jakester's Avatar
    jakester Posts: 582, Reputation: 165
    Senior Member
     
    #3

    Aug 14, 2012, 12:25 PM
    Quote Originally Posted by ScottGem View Post
    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?
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #4

    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,
    jakester's Avatar
    jakester Posts: 582, Reputation: 165
    Senior Member
     
    #5

    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;
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #6

    Aug 16, 2012, 04:57 AM
    Quote Originally Posted by jakester View Post
    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.
    jakester's Avatar
    jakester Posts: 582, Reputation: 165
    Senior Member
     
    #7

    Aug 16, 2012, 07:17 AM
    Quote Originally Posted by ScottGem View Post
    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;
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #8

    Aug 16, 2012, 07:32 AM
    Can you attach a file with the tables and sample data?
    jakester's Avatar
    jakester Posts: 582, Reputation: 165
    Senior Member
     
    #9

    Aug 16, 2012, 07:41 AM
    Quote Originally Posted by ScottGem View Post
    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.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #10

    Aug 16, 2012, 07:58 AM
    Don't you just have employee numbers?
    jakester's Avatar
    jakester Posts: 582, Reputation: 165
    Senior Member
     
    #11

    Aug 16, 2012, 08:08 AM
    Quote Originally Posted by ScottGem View Post
    Don't you just have employee numbers?
    Maybe I'll just create some dummy data and attach that. Let me try that.
    jakester's Avatar
    jakester Posts: 582, Reputation: 165
    Senior Member
     
    #12

    Aug 16, 2012, 08:21 AM
    Quote Originally Posted by ScottGem View Post
    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.
    Attached Files
  1. File Type: zip db1.zip (20.5 KB, 28 views)

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search


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