Expression or Query?

[dkuntz]

I have a Query that totals all of the Orders taken per Customer Service Rep in a given day.

However, what I really need is the total # of Orders taken per CSR by the Hour (we're open 24 hours). We have a [TransTime] field that has the exact time the order was taken.

Should this be done in an Expression or perhaps a Query based on another Query? I'm also thoughtless at the moment how the formula should be written.

As always any help is appreciated!

Derek

 

[CJBIRKIN]

Hello

If you know how to do a group by query (sounds like you do) then all you need to do is select the id of the CSR and a datefield related to the time field and the time. You group on the ID and the date and count on the time. This will give you the number of entries per CSR on any given date. you could swop the date and CSR fields around, that would give you the number of entries for each CSR per date. If you don't have a date then it will count all the entries regardless of the day i.e they may have been entered over 8 24hr periods.

method 1

CSRID.............DATE.........TIME(COUNT)
1...............27/07/2002............2
1...............28/07/2002............3
2...............27/07/2002............5
method 2

DATE..................CSRID..............TIME(COUNT)
27/07/2002............1......................2
27/07/2002............2......................2
28/07/2002............1......................5

Chris

 

[dkuntz]

Thanks for replying!

As it is now I pretty much have it set up as you illustrated. But how do I break down the number of orders taken per CSR per hour?

i.e

8/27/02 (The date is grouped)

TIME(Count) TIME(Count) TIME(Count)
CSR _ID....12am-1am....1am-2am....2am-3am....3am-4am etc......

ID500 ...............3.................5.................6................4...............
ID600................2.................1.................8................5...............

Does this make sense?

Thanks -

Derek

 

[dkuntz]

Using the Report Wizard I was able to Group everything how it needed to be.

Grouped [Date] by day
Grouped [Time] by hour
Grouped [CSR]

Thanks again for your help!


Derek