Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004

    Unanswered: calculation nightmare

    I have created a table from a text file. I need to create some queries to perform calculations and I am not sure how to go about it. In the table is the name of the employee, date and the transactions they completed. I brought over the transactions field as text but it represents the transactions performed as time. For example; the first transaction for that day was 8:45am, followed by 8:47am and another at 8:59am. I need to first find out how many hours the employee worked and how many transactions per 1/4 hour the employee performed from this field. In this example, it was three transactions between 8:45am and 9:00am (three per quarter hour) If this holds and the employee works 8 hours minus a lunch, it would be about 84 transactions for that day. I don't work with calculations a lot and I need a way to do this. I hope I provided enough information to grasp the concept I am trying to do.

  2. #2
    Join Date
    Aug 2002
    You are not providing enough information on how you want the final result displayed. Do you want an average transaction rate per quarter hour? Or do you want a listing of each quarter hour and how many transactions occured in that quarter hour.

  3. #3
    Join Date
    Jul 2004
    Ultimately, I want an average transaction rate per quarter hour but I also want a listing of each quarter hour and how many transactions occured in that quarter hour. Is this possible?

    Thank you

  4. #4
    Join Date
    Sep 2006
    Forgive me for sounding a little longwinded. You could use a crosstab query but I stopped using these as I found them inefficient. Without going into averaging at this stage

    I would create a query with Totals, Group on Employee and Date. Ttoal Transactions. Then for each Qtr hour I would define:

    HourQtr1: if([Time] Between 8:46 and 9:00,Count([Transactions],0)
    HourQtr2: iif([Time] between 9:01 and 9:15,Count([Transactions],0) etc..

    Create your report with all the HourQtr2 and you can graphically represent and compare how many transactions each employee has acheived.

    The Average is Total Transactions divided by HourQtrs

  5. #5
    Join Date
    Jul 2004
    thank you for you response. I will try this and see if it works like I need it to. If not, you have given me a good start.

  6. #6
    Join Date
    Feb 2004
    Chicago, IL
    Another approach that I frequently take is to create a function that determines a range, like this...

    Public Function QuarterHour(datTime As Date) As Long
        Select Case datTime
            Case Is < #8:00:00 AM#
                QuarterHour = 1
            Case Is < #8:15:00 AM#
                QuarterHour = 2
        End Select
    End Function
    NOTE: You will want to add more quarter hours, but this gives a start.

    Then you can create a table with all of your ranges:

    1-----------Before 8:00AM
    2-----------8:00AM to 8:15AM

    You can then call the function in a query RangeID:QuarterHour([Time]) and then use Count and Group By to come up with all of the counts by employee etc. that you want.

    When you go to print the info on a report then you can use that table to display the Range Text.

Posting Permissions

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