Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    May 2013
    Posts
    40

    Unanswered: Count Unique Days and Display Total in Report

    I received a request to have total number of unique days counted so that leadership will know how many days within a date range the employee worked. The issue is that an operator can have multiple activities documented under their ID in a single day.

    Example:
    ID Date Job
    userJR 4/1/13 123xyz
    userJR 4/1/13 123xyz
    userJR 4/1/13 123xyz
    userJR 4/2/13 123xyz
    userJR 4/8/13 123xyz
    userJR 4/8/13 123xyz
    userJR 4/16/13 123xyz
    userJR 4/21/13 123xyz

    What management needs to know is how many days did this person work? Not how many documented activated did they have. So the answer to the above would be < 5> the above user has FIVE unique work days. The 1st, 2nd, 8th, 16th and the 21st a total of 5 unique days.

    Iím using QUERY <G_OpPROD_DQ_ALL>
    When I added the field [LogDate] and changed it from Group By to COUNT all I get is a 1 next to each day but I havenít figured out how to get it group like days as 1 like in my example above.

    In REPORTS <G_OpPROD_DR_ALL> using a date range of [StartDate 4/1/13] and [EndDate 4/30/13] I have what I get in green txtÖ. it shows the Days Worked as 209 because thatís the total number of individual activities. It should show 22 because thatís the actual number of unique days.

    You can use FORM <G_OpPROD_DF_ALL> to enter DateRange [Start Date] [End Date]

    You may have to Hold SHIFT when you double click to open database.

    I hope this makes sense and as always I really appreciate your help.
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    I don't have Access, but the following query works on the small dataset that you provided.

    Code:
    Select cte.ID, COUNT(*) 
      from (Select distinct ID, DateID 
              from #temp
           ) cte
      where  cte.DateID >= "2013-04-01" and cte.DateID < DATEADD("d", 1, "2013-04-30")
     group by cte.ID

  3. #3
    Join Date
    May 2013
    Posts
    40
    Thanks for the quick reply "LinksUP". please forgive my lack of knowlege but where do I play the code you provided? I went into the SQL Design view in my current querry and have the following:
    ----------------------------------
    SELECT JobInfo.StartTime, PPOR2_OperatorInformation.AreaLeader, PPOR2_OperatorInformation.Op_FullName, JobInfo.TotalFlatExits, JobInfo.TotalFoldedExits, JobInfo.TotalSheetsFed, JobInfo.TotalActiveTime, JobInfo.TotalIdleTime, JobInfo.MacName, ([TotalActiveTime]+[TotalIdleTime]) AS TotalTime, IIf([TotalFlatExits]=0,0,[TotalSheetsFed]/[TotalFlatExits]) AS AvgPage_FLT, IIf([TotalFoldedExits]=0,0,[TotalSheetsFed]/[TotalFoldedExits]) AS AvgPage_FLD, PPOR2_OperatorInformation.Shift, PPOR2_OperatorInformation.Op_ID, JobInfo.JobName, JobInfo.ActiveTimePercentage, (([TotalActiveTime])/3600/24) AS TotalACT, (([TotalIdleTime])/3600/24) AS TotalIDL, (([TotalActiveTime]+[TotalIdleTime]/3600/24)) AS TotalTIM, Count(JobInfo.LogDate) AS CountOfLogDate
    FROM JobInfo INNER JOIN PPOR2_OperatorInformation ON JobInfo.Operator = PPOR2_OperatorInformation.Op_ID
    GROUP BY JobInfo.StartTime, PPOR2_OperatorInformation.AreaLeader, PPOR2_OperatorInformation.Op_FullName, JobInfo.TotalFlatExits, JobInfo.TotalFoldedExits, JobInfo.TotalSheetsFed, JobInfo.TotalActiveTime, JobInfo.TotalIdleTime, JobInfo.MacName, ([TotalActiveTime]+[TotalIdleTime]), IIf([TotalFlatExits]=0,0,[TotalSheetsFed]/[TotalFlatExits]), IIf([TotalFoldedExits]=0,0,[TotalSheetsFed]/[TotalFoldedExits]), PPOR2_OperatorInformation.Shift, PPOR2_OperatorInformation.Op_ID, JobInfo.JobName, JobInfo.ActiveTimePercentage, (([TotalActiveTime])/3600/24), (([TotalIdleTime])/3600/24), (([TotalActiveTime]+[TotalIdleTime]/3600/24)), Year([StartTime])
    HAVING (((JobInfo.StartTime) Between [Forms]![PPOR2_FQ_OpPROD_GunDETAILED_ALL]![StartDate] And [Forms]![PPOR2_FQ_OpPROD_GunDETAILED_ALL]![EndDate]) AND ((Year([StartTime]))=Year(Now())));----------------------------

    Thanks,

  4. #4
    Join Date
    Jan 2005
    Posts
    146
    Hi tryn2learn. Your problem is your attempting to select just unique (distinct) LogDates but also trying to return numerous other columns. A query will only return distinct records, not distinct columns. You are wanting it to ignore the data in the subsequent columns but it can't. If the subsequent columns are not required in your determining what a unique record is, you have to leave them out. So, to return the operator and just unique LogDates, you have to leave all the other columns out unless each record would contain the same data, for example, Operator Name or Operator ID.
    Code:
    SELECT DISTINCT JobInfo.Operator, JobInfo.LogDate
    FROM JobInfo
    GROUP BY JobInfo.Operator, JobInfo.LogDate;

  5. #5
    Join Date
    May 2013
    Posts
    40
    Thanks “bilmeye”
    Is there any then to have two separate queries, one for all the employee stats and the other for the unique day count… and then have a report that will pull from both queries so that I can still have a single report with the employee stats “plus” the unique day count?

    I really appreciate you trying to help me out with this.
    Last edited by tryn2learn; 07-22-13 at 15:38.

  6. #6
    Join Date
    Jan 2005
    Posts
    146
    You can take advantage of the report Grouping and Sorting and Group by LogDate and Operator to see all the details for each day and then just use Dcount("*","qryUniqueDays","[Operator]='" & [Operator] & "'") to count how many unique days.

  7. #7
    Join Date
    May 2013
    Posts
    40
    So I need to create that second query using the code you provided then go to my existing report and do the Group and Sorting? I'll give it a shot... thanks.

  8. #8
    Join Date
    Jan 2005
    Posts
    146
    If you group by Operator, you can nicely place the count of dates in the Operator header.

  9. #9
    Join Date
    May 2013
    Posts
    40
    Thanks, I really appreciate your help especially because this is way beyond my current skillset/understanding.

  10. #10
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    You may also want to check out your parens on your time calculation. It looks like you may have a problem with one of them.

    original code:
    Code:
              , (( [totalactivetime] + [totalidletime] / 3600 / 24 ))
    It probably should be:
    Code:
              , (( [totalactivetime] + [totalidletime]) / 3600 / 24 )

  11. #11
    Join Date
    May 2013
    Posts
    40
    Thank you "LinksUp" I'll get that fixed. Sadly I still haven't been successful with the Sorting and Grouping... I konw it's not the logic, I'm sure it's how I'm doing. I'll keep at it. thanks again "LinksUp & billmeye"

  12. #12
    Join Date
    Jan 2005
    Posts
    146
    Post what you have, let me know which report and I'll take a look.

  13. #13
    Join Date
    May 2013
    Posts
    40
    Thanks, sorry to be such a bother with this...
    Attached Files Attached Files

  14. #14
    Join Date
    Jan 2005
    Posts
    146
    Which report are we working with?

  15. #15
    Join Date
    May 2013
    Posts
    40
    I was working with the report [G_OpPROD_SR_ALL_COUNT] and the query [G_OpPROD_SR_ALL_COUNT & G_OpPROD_SR_ALL]. trying to get the iformation from both queries into the one report.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •