Challenging Query/Report - Reposted

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Year Score Nam
2004 10 Joh
2003 10 Joh
2001 9 Joh
2000 10 Joh

I need to create report that shows

Name Count of Consecutive Superior
John

[Count of Consecutive Superiors] counts only the consecutive 10s starting from 2004.
So if John had 9 for 2003 - his [Count of Consecutive Superiors] would be 1 - (only for 2004) - and if he had 9 for 2004 then the count would automatically be 0 since the count starts from 2004

Any suggestions on how I can accomplish this? I realize that I probably should write a loop

Thank you very much for your help
Sophi

Note that not all names have data for all years....Counting Scores other than 10 doesn't work because there could be gaps of years between the non superior score and the superior score
 
Sophia said:
Nope, I didn' figure it out...this query is the craziest thing I have ever seen....
Click to expand...

Hi Sophia,

I think you just have to "premassage" your
data to show all years and then use Michel's query.

tblSophia
AYear Score AName
2004 10 John
2003 10 John
2001 9 John
2000 10 John
2004 10 Homer
2002 10 Homer
2003 10 Marge
2002 10 Marge
2000 10 Marge
2004 9 Bart
2003 10 Bart

from above, you would want

John 2
Homer 1
Marge 0
Bart 0

Start with Iotas table with one field Iota
going from 0 to {some large number}
- I will use table going from 0 to 10,000
then limit to 0-10 in example, but your
Iota table *could* just go from 0 to 10.

tblIota
Iotas
0
1
2
3
4
5
6
7
8
9
10

1) the first query gives every year for every name

qrySophiaYearName:

SELECT
DistinctNames.AName,
EnumYears.AYear
FROM
[SELECT Year(Date()) - Iota AS AYear
FROM Iotas WHERE Iota<10]. AS EnumYears,
[SELECT DISTINCT AName FROM tblSophia]. AS DistinctNames
ORDER BY
DistinctNames.AName,
EnumYears.AYear DESC;

producing

AName AYear
Bart 2004
Bart 2003
Bart 2002
Bart 2001
Bart 2000
Bart 1999
Bart 1998
Bart 1997
Bart 1996
Bart 1995
Homer 2004
Homer 2003
Homer 2002
Homer 2001
Homer 2000
Homer 1999
Homer 1998
Homer 1997
Homer 1996
Homer 1995
John 2004
John 2003
John 2002
John 2001
John 2000
John 1999
John 1998
John 1997
John 1996
John 1995
Marge 2004
Marge 2003
Marge 2002
Marge 2001
Marge 2000
Marge 1999
Marge 1998
Marge 1997
Marge 1996
Marge 1995

2) the next query gets any scores

qrySophiaNameYearScore:

SELECT
qrySophiaYearName.AName,
qrySophiaYearName.AYear,
tblSophia.Score
FROM qrySophiaYearName
LEFT JOIN tblSophia
ON (qrySophiaYearName.AYear = tblSophia.AYear)
AND (qrySophiaYearName.AName = tblSophia.AName)
ORDER BY qrySophiaYearName.AName, qrySophiaYearName.AYear DESC;

producing:

AName AYear Score
Bart 2004 9
Bart 2003 10
Bart 2002
Bart 2001
Bart 2000
Bart 1999
Bart 1998
Bart 1997
Bart 1996
Bart 1995
Homer 2004 10
Homer 2003
Homer 2002 10
Homer 2001
Homer 2000
Homer 1999
Homer 1998
Homer 1997
Homer 1996
Homer 1995
John 2004 10
John 2003 10
John 2002
John 2001 9
John 2000 10
John 1999
John 1998
John 1997
John 1996
John 1995
Marge 2004
Marge 2003 10
Marge 2002 10
Marge 2001
Marge 2000 10
Marge 1999
Marge 1998
Marge 1997
Marge 1996
Marge 1995

3) Now you can use Michel's query

SELECT
a.AName,
MAX(a.AYear)- NZ(MAX( b.AYear),MIN(a.AYear)-1) AS ConsecutiveCount
FROM qrySophiaNameYearScore AS a
LEFT JOIN
[SELECT * FROM qrySophiaNameYearScore
WHERE score <> 10 OR score IS NULL]. AS b
ON (a.AYear >= b.AYear) AND (a.AName = b.AName)
GROUP BY a.AName;

producing

AName ConsecutiveCount
Bart 0
Homer 1
John 2
Marge 0
 
Back
Top