Re: ranking calculated fields in a query
- From: "Jeanette Cunningham" <nnn@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 6 Jan 2008 19:06:14 +1100
Steve,
I have simplified the number of fields so you can see how this will work,
then you can add in the extra fields.
I created 3 tables : tblTwirlers, tblFees, tblModellingXTab.
Make the following query and save it as qRank
SELECT tblTwirlers.FirstName, tblFees.Level, tblModellingXTab.R3,
tblModellingXTab.AgeGroup
FROM tblTwirlers INNER JOIN (tblFees INNER JOIN tblModellingXTab ON
tblFees.FeesID = tblModellingXTab.PageantID) ON tblTwirlers.TwirlerID =
tblModellingXTab.TwirlerID
ORDER BY tblFees.Level, tblModellingXTab.R3;
Then create the following query:
SELECT Q.FirstName, Q.AgeGroup, Q.R3, (SELECT COUNT(*) FROM qRank As Q1
WHERE Q1.[Agegroup] = Q.[Agegroup]
AND Q1.R3 <= Q.R3) AS Rank
FROM qRank AS Q
WHERE ((((SELECT COUNT(*) FROM qRank Q1
WHERE Q1.[Agegroup] = Q.[Agegroup]
AND Q1.R3 > Q.R3)+1)<>False))
ORDER BY Q.AgeGroup, Q.R3 DESC;
The result is:
FirstName AgeGroup R3 Rank
Jane 10 - 12 7 3
Sally 10 - 12 6 2
Mary 10 - 12 5 1
Sam 9 - 10 11 5
Pat 9 - 10 10 4
Sue 9 - 10 10 4
Jo 9 - 10 8 2
Beth 9 - 10 6 1
Jeanette Cunningham
"Steve S" <SteveS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E5AABF02-EA02-4416-AFA5-D9271A1FC11A@xxxxxxxxxxxxxxxx
the following select query adds 3 fields (R1 R2,R3) to create MPP. What I
need to do is add the code to Rank MPP within Level and Age Group. I have
tried several variations of code offered by others in responce to
questions
in this news group but evidently I lack the skill level to insert samples
into my existing code.
SELECT [Twirlers].[First Name] & " " & [Twirlers].[Last Name] AS Name,
Fees.Level, ModelingXTab.[Age Group], Val(nz([MJ1R],0)) AS R1,
Val(nz([MJ2R],0)) AS R2, Val(nz([MJ3R],0)) AS R3, [R1]+[R2]+[R3] AS MPP,
"Some SQL" AS Rank
FROM (ModelingXTab INNER JOIN Twirlers ON ModelingXTab.[Twirler ID] =
Twirlers.[Twirler ID]) INNER JOIN Fees ON ModelingXTab.[Pagent ID] =
Fees.[Fee ID];
the results of this query should look like:
Mary Advanced 10-12 2 2 1 5 1
Jane Advanced 10-12 3 1 3 7 3
Sally Advanced 10-12 1 3 2 6 2
Beth Advanced 9-10 1 3 2 6 1
Jo Advanced 9-10 2 1 5 8 2
sue Advanced 9-10 4 2 4 10 3
Pat Advanced 9-10 5 4 1 10 3
Sam Advanced 9-10 3 5 3 11 5
Please note how ties are ranked.
Is it possible to rank the MPP field since it is a calculated field? In
some of the code I tried I got a 'circular reference' error on the table
name. One alternative I have considered is to change this select query to
an
update query and have a seperate query to update the rank field but would
prefer to get the job done in one pass.
any help is appreciated
.
- Follow-Ups:
- Re: ranking calculated fields in a query
- From: Steve S
- Re: ranking calculated fields in a query
- References:
- ranking calculated fields in a query
- From: Steve S
- ranking calculated fields in a query
- Prev by Date: Re: removing extra pages from query printouts?
- Next by Date: Re: Problems with an SQL
- Previous by thread: ranking calculated fields in a query
- Next by thread: Re: ranking calculated fields in a query
- Index(es):
Relevant Pages
|
