query to update data

[cliff]

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

 

[John Spencer]

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]

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