1. Registered User
Join Date
Jul 2004
Posts
214

All,
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.
Thanks

2. Registered User
Join Date
Aug 2002
Posts
33
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. Registered User
Join Date
Jul 2004
Posts
214
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. Registered User
Join Date
Sep 2006
Posts
265
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. Registered User
Join Date
Jul 2004
Posts
214
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.
Thanks

6. Registered User
Join Date
Feb 2004
Location
Chicago, IL
Posts
1,312
Another approach that I frequently take is to create a function that determines a range, like this...

Code:
```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:

RangeID-----RangeText
1-----------Before 8:00AM
2-----------8:00AM to 8:15AM
etc.

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
•