Prev: Chart based on data in the report
Next: Grouping two fields together ?
From: cliff on 2 Jul 2008 10:26
I have the following data

sr no1. no2. no.3 no.4 no.5 no.6
1 1 8 11 25 30 32
2 4 8 19 28 30 45
3 7 14 20 31 35 39
4 4 12 15 20 43 48
5 9 16 21 22 26 36
6 5 17 28 29 31 47
7 20 13 15 30 33 32
8 11 15 16 25 26 49
9 25 32 36 39 40 44
10 5 6 17 20 27 43
11 35 37 38 40 45 47
12 25 27 28 38 39 45
13 8 11 15 32 46 18

I need to calculate top 10 numbers drawn in last 10 records from 11th
sr.no. onwards and how many of such nos are equal to 11th sr.no. onwards

for eg upto 10 sr.no./record 20 appears 4 times, 25,15,30 3 times and
4,5,8,11,16,17,26,28,31,32,36,39,43 twice.

so 10 top nos will be 20,25,15,30,3,4,5,8,11,16. however, none of them
appears in 11th record , which will be value of field called hot10 on 11 th
record . I would like to write query to calculate the above result for all
records (based on 10 previous records) and insert value into field called
hot 10 i.e,

please help me out to achieve the ojective


thanks


From: John Spencer on 2 Jul 2008 12:22
First step would be to structure your data correctly.
TableOfNumbers
SR - Your Current value for sr
NumValue - values you are storing in No1 to No6
Position - not needed unless you need to know the position of the NumValue.

I'm not sure this entire process will even work. But I would break it down as
follows.

You can try a UNION query to normalize the data if you can't restructure the
data in the first place.

SELECT SR, [No. 1] as NumValue
FROM [YourTable]
UNION ALL
SELECT SR, [No. 2] as NumValue
FROM [YourTable]
UNION ALL
SELECT SR, [No. 3] as NumValue
FROM [YourTable]
UNION ALL
SELECT SR, [No. 4] as NumValue
FROM [YourTable]
UNION ALL
SELECT SR, [No. 5] as NumValue
FROM [YourTable]
UNION ALL
SELECT SR, [No. 6] as NumValue
FROM [YourTable]

Now get the Count of the numbers for prior 10 records
SELECT A.SR, B.NumValue, Count(B.NumValue) as NumCount
FROM qUnion as A INNER JOIN qUnion As B
ON B.SR <= A.SR-10 and B.SR < A.SR
GROUP BY A.SR, B.NumValue

Next get the top 10 numbers for each set of prior records.
SELECT A.SR, A.NumValue, NumCount
FROM qNumCount as A
WHERE NumValue in
(SELECT TOP 10 NumValue
FROM qNumCount as B
WHERE B.SR = A.SR
ORDER BY NumCount DESC)

Next step: get the matches (if any)
SELECT A.SR. A.NumValue, NumCount
FROM qUnion as A INNER JOIN qTop10EachSR As B
on A.SR = B.SR AND A.NumValue = B.NumValue

Next Step get the one record for each sr with the highest count
SELECT X.SR, X.NumValue
FROM qAbove as X
WHERE NumCount in
(SELECT Max(NumCount)
FROM qAbove as Y
WHERE Y.SR = X.SR)



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

cliff wrote:
> I have the following data
>
> sr no1. no2. no.3 no.4 no.5 no.6
> 1 1 8 11 25 30 32
> 2 4 8 19 28 30 45
> 3 7 14 20 31 35 39
> 4 4 12 15 20 43 48
> 5 9 16 21 22 26 36
> 6 5 17 28 29 31 47
> 7 20 13 15 30 33 32
> 8 11 15 16 25 26 49
> 9 25 32 36 39 40 44
> 10 5 6 17 20 27 43
> 11 35 37 38 40 45 47
> 12 25 27 28 38 39 45
> 13 8 11 15 32 46 18
>
> I need to calculate top 10 numbers drawn in last 10 records from 11th
> sr.no. onwards and how many of such nos are equal to 11th sr.no. onwards
>
> for eg upto 10 sr.no./record 20 appears 4 times, 25,15,30 3 times and
> 4,5,8,11,16,17,26,28,31,32,36,39,43 twice.
>
> so 10 top nos will be 20,25,15,30,3,4,5,8,11,16. however, none of them
> appears in 11th record , which will be value of field called hot10 on 11 th
> record . I would like to write query to calculate the above result for all
> records (based on 10 previous records) and insert value into field called
> hot 10 i.e,
>
> please help me out to achieve the ojective
>
>
> thanks
>
>
From: cliff on 4 Jul 2008 13:41


hi john spencer,
thanks for help

I followed step 1 and step 11 it is sucessful with slight modification

step 1 union query - worked as it is

> SELECT SR, [No. 1] as NumValue
> FROM [YourTable]
> UNION ALL
> SELECT SR, [No. 2] as NumValue
> FROM [YourTable]
> UNION ALL
> SELECT SR, [No. 3] as NumValue
> FROM [YourTable]
> UNION ALL
> SELECT SR, [No. 4] as NumValue
> FROM [YourTable]
> UNION ALL
> SELECT SR, [No. 5] as NumValue
> FROM [YourTable]
> UNION ALL
> SELECT SR, [No. 6] as NumValue
> FROM [YourTable]
>
step 2

> Now get the Count of the numbers for prior 10 records
> SELECT A.SR, B.NumValue, Count(B.NumValue) as NumCount
> FROM qUnion as A INNER JOIN qUnion As B
> ON B.SR <= A.SR-10 and B.SR < A.SR
> GROUP BY A.SR, B.NumValue
>
here I chaged slighted and worked well giving correct result

SELECT a.sr, B.NumValue, count(a.numvalue)/6 AS numcount
FROM [tloto Union QRY] AS a INNER JOIN [tloto union QRY] AS B ON (b.sr<a.sr)
AND (b.sr-a.sr<=10) AND (B.SR+10>=A.SR)
GROUP BY a.sr, B.NumValue;



setp 3

> Next get the top 10 numbers for each set of prior records.
> SELECT A.SR, A.NumValue, NumCount
> FROM qNumCount as A
> WHERE NumValue in
> (SELECT TOP 10 NumValue
> FROM qNumCount as B
> WHERE B.SR = A.SR
> ORDER BY NumCount DESC)

it is giving wrong output . Here I want to display top 10 counts for each
sr .
But above query,
1. it some sr's it is listing 5 in some case , 6 in other and maximum
listing 9 records instead 10.
2. it is not listing on descending order of counts for each sr


step 4 should work , if step work correctly

>
> Next step: get the matches (if any)
> SELECT A.SR. A.NumValue, NumCount
> FROM qUnion as A INNER JOIN qTop10EachSR As B
> on A.SR = B.SR AND A.NumValue = B.NumValue
>

step 5 should work properly

> Next Step get the one record for each sr with the highest count
> SELECT X.SR, X.NumValue
> FROM qAbove as X
> WHERE NumCount in
> (SELECT Max(NumCount)
> FROM qAbove as Y
> WHERE Y.SR = X.SR)
>
> I want to add one more to count for no.of records step 4 for each sr

can you help to complete my mission

thanks in advance

cliff

>
> John Spencer
> Access MVP 2002-2005, 2007-2008
> The Hilltop Institute
> University of Maryland Baltimore County
>
> cliff wrote:
> > I have the following data
> >
> > sr no1. no2. no.3 no.4 no.5 no.6
> > 1 1 8 11 25 30 32
> > 2 4 8 19 28 30 45
> > 3 7 14 20 31 35 39
> > 4 4 12 15 20 43 48
> > 5 9 16 21 22 26 36
> > 6 5 17 28 29 31 47
> > 7 20 13 15 30 33 32
> > 8 11 15 16 25 26 49
> > 9 25 32 36 39 40 44
> > 10 5 6 17 20 27 43
> > 11 35 37 38 40 45 47
> > 12 25 27 28 38 39 45
> > 13 8 11 15 32 46 18
> >
> > I need to calculate top 10 numbers drawn in last 10 records from 11th
> > sr.no. onwards and how many of such nos are equal to 11th sr.no. onwards
> >
> > for eg upto 10 sr.no./record 20 appears 4 times, 25,15,30 3 times and
> > 4,5,8,11,16,17,26,28,31,32,36,39,43 twice.
> >
> > so 10 top nos will be 20,25,15,30,3,4,5,8,11,16. however, none of them
> > appears in 11th record , which will be value of field called hot10 on 11 th
> > record . I would like to write query to calculate the above result for all
> > records (based on 10 previous records) and insert value into field called
> > hot 10 i.e,
> >
> > please help me out to achieve the ojective
> >
> >
> > thanks
> >
> >
>
 | 
Pages: 1
Prev: Chart based on data in the report
Next: Grouping two fields together ?