Hi Petteri,
I might assume you have a lot of data and your table has a pk like "ID" in tblResponse below.
tblResponse ID RespDate RespTime
1 5/1/2005 3
2 5/1/2005 6
3 5/2/2005 4
4 5/3/2005 2
5 5/4/2005 4
6 5/6/2005 3
7 5/7/2005 8
8 5/8/2005 5
9 5/9/2005 5
10 5/10/2005 3
11 5/11/2005 2
12 5/12/2005 4
13 5/13/2005 1
14 5/14/2005 8
15 5/15/2005 4
16 5/16/2005 3
17 5/17/2005 6
18 5/18/2005 7
19 5/19/2005 7
20 5/20/2005 4
21 6/1/2005 3
22 6/1/2005 5
23 6/2/2005 3
24 6/3/2005 6
25 6/4/2005 9
26 6/6/2005 2
27 6/7/2005 4
28 6/8/2005 5
29 6/9/2005 7
30 6/10/2005 3
31 6/11/2005 1
32 6/12/2005 4
33 6/13/2005 6
34 6/14/2005 3
35 6/15/2005 7
36 6/16/2005 4
37 6/17/2005 4
38 6/18/2005 2
39 6/19/2005 7
40 6/20/2005 5
41 6/21/2005 5
42 6/22/2005 3
43 6/23/2005 9
44 6/24/2005 4
45 6/25/2005 6
I also might assume that you want
to show your results in a report.
If the above is true, then while the
following probably can be achieved in a
Steve-Dassin-like crosstab, it might
help to break the process down into
query steps.
The gist of this method is to rank
the times within a year/month group.
Then you can use this rank to give each
time a "rank %", i.e., Rank/Count of group.
You can then group on (RankPercent<=.9)
to get your 2 groups which you can apply
aggregates to.
I might suggest creating a scratch table
(say "tblScratch") with following fields:
RespYear Long
RespMonth Long
RespTime Long
ID Long
Rank Long
RankPerCent Single
When you start the process, clear tblScratch
of all records. Then run the following append qry:
INSERT INTO tblScratch ( RespYear, RespMonth, RespTime, ID, Rank )
SELECT
Year([RespDate]) AS RespYear,
Month([RespDate]) AS RespMonth,
t1.RespTime,
t1.ID,
(SELECT COUNT(*) FROM tblResponse t2
WHERE
Year(t2.RespDate)=Year(t1.RespDate)
AND
Month(t2.RespDate)=Month(t1.RespDate)
AND
t2.RespTime*1000+t2.ID*.001 < t1.RespTime*1000+t1.ID*.001)+1 AS Rank
FROM tblResponse AS t1
ORDER BY
Year([RespDate]),
Month([RespDate]),
t1.RespTime,
t1.ID;
then run the following update qry:
UPDATE tblScratch AS S
SET S.RankPerCent =
.[Rank]/DCount("*","tblScratch","[RespYear]=" & .[RespYear] & " AND [RespMonth]=" & .[RespMonth]);
with above data, your tblScratch would now look like:
tblScratch RespYear RespMonth RespTime ID Rank RankPerCent
2005 5 1 13 1 0.05
2005 5 2 4 2 0.1
2005 5 2 11 3 0.15
2005 5 3 1 4 0.2
2005 5 3 6 5 0.25
2005 5 3 10 6 0.3
2005 5 3 16 7 0.35
2005 5 4 3 8 0.4
2005 5 4 5 9 0.45
2005 5 4 12 10 0.5
2005 5 4 15 11 0.55
2005 5 4 20 12 0.6
2005 5 5 8 13 0.65
2005 5 5 9 14 0.7
2005 5 6 2 15 0.75
2005 5 6 17 16 0.8
2005 5 7 18 17 0.85
2005 5 7 19 18 0.9
2005 5 8 7 19 0.95
2005 5 8 14 20 1
2005 6 1 31 1 0.04
2005 6 2 26 2 0.08
2005 6 2 38 3 0.12
2005 6 3 21 4 0.16
2005 6 3 23 5 0.2
2005 6 3 30 6 0.24
2005 6 3 34 7 0.28
2005 6 3 42 8 0.32
2005 6 4 27 9 0.36
2005 6 4 32 10 0.4
2005 6 4 36 11 0.44
2005 6 4 37 12 0.48
2005 6 4 44 13 0.52
2005 6 5 22 14 0.56
2005 6 5 28 15 0.6
2005 6 5 40 16 0.64
2005 6 5 41 17 0.68
2005 6 6 24 18 0.72
2005 6 6 33 19 0.76
2005 6 6 45 20 0.8
2005 6 7 29 21 0.84
2005 6 7 35 22 0.88
2005 6 7 39 23 0.92
2005 6 9 25 24 0.96
2005 6 9 43 25 1
From here, you could go several ways.
One way would be to base your report on the
following query:
SELECT
S.RespYear,
S.RespMonth,
Count(S.RespTime) AS Cnt,
Min(S.RespTime) AS MinRespTime,
Max(S.RespTime) AS MaxRespTime,
Avg(S.RespTime) AS AvgRespTime,
[RankPerCent]<=0.9 AS InTop90
FROM tblScratch AS S
GROUP BY
S.RespYear,
S.RespMonth,
[RankPerCent]<=0.9;
qryrptResponseTime RespYear RespMonth Cnt MinRespTime MaxRespTime AvgRespTime InTop90
2005 5 18 1 7 4.05555555555556 -1
2005 5 2 8 8 8 0
2005 6 22 1 7 4.18181818181818 -1
2005 6 3 7 9 8.33333333333333 0
Petteri Toukoniitty said:
Hi Steve,
and thank you for your answer!
I'm still having some problems with the query. I know how to use the average
function, but the problem is that it can't be
used directly in this case as it would calculate average amount for each
month.
What I need is that the query should
1) Take 90% of the fastest response times per month (e.g. sort the response
times for each month, then take top-90% of these per month)
2) Calculate average for these top 90% response times per month
3) Calculate average for the remaining 10%
So this way each month would have two averages, one for the faster reponse
times and one for the slower ones.
So my guess is that I should somehow combine the average-function and the
"SELECT TOP 90 PERCENT" attribute in access/SQL,
but I don't know how... Does anyone have clue how to do this?
Cheers,
Petteri
[MVP] S.Clark said:
I'll admit that I don't totally understand, but you can use the Average
operator in a query.
SELECT [ID], Average([Field]) FROM [Tablename] GROUP BY [ID]
You can also make a crosstab query to transpose data to be a column.
Using the two together, probably in two or more queries, you can get to
where you want to go.
--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting
Petteri Toukoniitty said:
Hi,
I have a database containing info about response times to user
requests. In addition to the response time, each record contains date
when the request was made. Now I need to count two averages for each
month: average for top 90% of the response times (ordered by response
time to get the top 90%) and other average for the remaining 10%.
Example table:
Date(month) Response time (minutes)
January 1
January 2
January 5
January 4
February 8
February 2
The query should then return something like this:
Date(month) Avg top 90% Avg remaining
January X Y
February X2 Y2
Where X would be the average response time for the fastest 90% of the
response times, and Y would be the average for the remaining requests
that have the longest response times.
How could I do this with Access?
Thanks in advance,
Petteri
