Finding Averages, Sums in a Query 1

[firebirdta84]

I have a table with 5 columns, UserID, UnitID, DateRequested, DateFulfilled and NumberofUnits. Here's a sample:

http://i131.photobucket.com/albums/p314/firebirdta84/1-2.jpg

I need to create a report on this information that gives a "summary" for each UserID - showing the average days it takes them from the DateRequested until the DateFulfilled the total number of units and the total number of records they have in the table. So, based on the table above, this is what I would need displayed in the report:

http://i131.photobucket.com/albums/p314/firebirdta84/2-2.jpg

The report needs to include one entry for each user id, ordered by who had the least number of average days between DateRequested and DateFulfilled.

Help with writing the query would be appreciated!

Thanks,
Joe

 

[lameid]

Apparently my web filter blocks your sample data but I think the below is what you want...

Select UserID, AVG(DateDiff("d", [DateRequested],[DateFulfilled])) As AverageCylceTime
From [i]Table[/i]
Group By UserID
Order By AVG(DateDiff("d", [DateRequested],[DateFulfilled]))

 

[firebirdta84]

This worked perfectly! Thank you. Now my question is this...I want to add a column to this query that "ranks" each UserID. The ranking would be based off of the average cycle time between Date Requested and Date Fulfilled. Whoever had the shortest cycle time gets assigned a "1" in that ranking column, the next shortest gets a "2" and so on...ideas?

Thanks!
Joe

 

[lameid]

The easiest way to do this is to add a literal 1 to a query.

Then make a report based on the query and set the new literal 1 field to do a running sum. The report will start with 1 and effectively count up (add 1 each time).

Otherwise I think you might find a solution by searching this forum.

 

[firebirdta84]

The problem is that the report will not order them by who had the smallest cycle time, but rather alphabetically...so I'm not sure that would work.

 

[lameid]

Set the sorting and grouping in the report to sort by the average cycle time.

 

[firebirdta84]

One of the requirements of the project is that the report be sorted alphabetically, and that the ranking be reported as a number....otherwise that would work fine.

Joe

 

[lameid]

Query1

Select UserID, AVG(DateDiff("d", [DateRequested],[DateFulfilled])) As AverageCylceTime, 1 As Counter
From Table
Group By UserID
Order By AVG(DateDiff("d", [DateRequested],[DateFulfilled]))

query2

Select UserID, AverageCycleTime, (Select Sum(Counter) From Query1 Where query1.AverageCycleTime < q.AverageCycleTime) as Rank
From Query1 As Q

Hopefully Jet will like that subquery in query2.

 

[firebirdta84]

Well, that works great except for one small thing...the counter is off by 1....example: the person who should be ranked #1 is not ranked at all; the person who should be ranked #2 is ranked #1 and so on....you're very close though!

Thanks,
Joe

 

[firebirdta84]

Nevermind! I figured it out...just needed to change the < to <=. Thanks for all your help!

Joe

 

[lameid]

I would stick with < and add 1...

Select UserID, AverageCycleTime, (Select Sum(Counter) From Query1 Where query1.AverageCycleTime < q.AverageCycleTime) + 1 as Rank
From Query1 As Q

I say that because if two people have the same cycle time they will have the same rank... say 2 and 2 and then the next will have 4. Using <= they would be rank 3 and 3. That may be desireable.