thanks for the quick reply. after much anguish on my part I got it to
work.
this job would be easy if I could type.
the sample code worked great with 2 exceptions. First the way it
resolves
ties will not work my users. what I get for rank could be 1,2,4,4,5 but
what
I need is 1,2,3,3,5 if third and fourth place are tied. I have tried
reversing and/or removing '<' and '>' but still cant it to resolve ties
the
way I need.
also this projcet is much more complicated than as indicated in my
original
post. I needed to create 3 queries such as you reffered to as qRank. I
call
them MSum,SSum, and TSum. This is a multipart event consisting of ranking
for Modeling, strut, and Twirling. the second set of code resulted in 3
queries named MRank, SRank, and TRank. Each 'Rank' query uses the
associated
Sum query. So fat so good. Each works just great except fo geing dog
slow.
That is probably because what you assumed was a table (ModelingXTab) is
actuall a very complicated XTabQuery.(I got the sample code from a
Newsgroup
Post). this query seems to be running many,many times. May have to make
it
a table to get better performance. with only 32 records in my test bed it
takes 3-5 seconds to redisplay the results.
To complicate matters further these 3 queries (M, S, and TRank) have to be
combined in a select query to feed a report. When I combine any two of
the
'Rank fields' on the final report I get the error: 'Object invalid or no
longer set'. The SQL is:
SELECT MRank.Name, MRank.Level, MRank.[Age Group], MRank.RankM,
TRank.RankT
FROM MRank INNER JOIN TRank ON MRank.Name = TRank.Name;
I can display all the fields from all 3 queries except the 'xRank' field'
Selecting any 2 of them will produce the error. i do not see any
duplicate
named fields and each set works OK. any suggestions??
Here is the SQL for all 6 queries involved.
MSum:
SELECT [Twirlers].[First Name] & " " & [Twirlers].[Last Name] AS Name,
Fees.Level, ModelingXTab.[Age Group], Val(nz([MJ1N],0)) AS MJ1,
Val(nz([MJ1S],0)) AS MS1, Val(nz([MJ1R],0)) AS MR1, Val(nz([MJ2N],0)) AS
MJ2,
Val(nz([MJ2S],0)) AS MS2, Val(nz([MJ2R],0)) AS MR2, Val(nz([MJ3N],0)) AS
MJ3,
Val(nz([MJ3S],0)) AS MS3, Val(nz([MJ3R],0)) AS MR3, [MR1]+[MR2]+[MR3] AS
MPP
FROM (ModelingXTab INNER JOIN Twirlers ON ModelingXTab.[Twirler ID] =
Twirlers.[Twirler ID]) INNER JOIN Fees ON ModelingXTab.[Pagent ID] =
Fees.[Fee ID];
MRank creates RankM
SELECT M.Name, M.Level, M.[Age Group], M.MJ1, M.MS1, M.MR1, M.MJ2, M.MS2,
M.MR2, M.MJ3, M.MS3, M.MR3, M.MPP, (SELECT COUNT(*) FROM MSum As M1
WHERE M
1.[Age group] = M.[Age group]
AND M1.Level = M.Level
AND M1.MPP <= M.MPP) AS RankM
FROM MSum AS M
WHERE ((((SELECT COUNT(*) FROM MSum M1
WHERE M1.[Age group] = M.[Age group]
AND M1.Level = M.Level
AND M1.MPP > M.MPP)+1)<>False))
ORDER BY M.Level, M.[Age Group], M.MPP;
TSum:
SELECT [Twirlers].[First Name] & " " & [Twirlers].[Last Name] AS Name,
Fees.Level, TwirlXTab.[Age Group], Val(nz([TJ1N],0)) AS TJ1,
Val(nz([TJ1S],0)) AS TS1, Val(nz([TJ1R],0)) AS TR1, Val(nz([TJ2N],0)) AS
TJ2,
Val(nz([TJ2S],0)) AS TS2, Val(nz([TJ2R],0)) AS TR2, Val(nz([TJ3N],0)) AS
TJ3,
Val(nz([TJ3S],0)) AS TS3, Val(nz([TJ3R],0)) AS TR3, [TR1]+[TR2]+[TR3] AS
TPP
FROM Fees INNER JOIN (Twirlers INNER JOIN TwirlXTab ON Twirlers.[Twirler
ID]
= TwirlXTab.[Twirler ID]) ON Fees.[Fee ID] = TwirlXTab.[Pagent ID];
TRank creates RankT
SELECT Q.Name, Q.Level, Q.[Age Group], Q.TJ1, Q.TS1, Q.TR1, Q.TJ2, Q.TS2,
Q.TR2, Q.TJ3, Q.TS3, Q.TR3, Q.TPP, (SELECT COUNT(*) FROM TSum As Q1
WHERE Q1.[Age group] = Q.[Age group]
AND Q1.Level = Q.Level
AND Q1.TPP <= Q.TPP) AS RankT
FROM TSum AS Q
WHERE ((((SELECT COUNT(*) FROM TSum Q1
WHERE Q1.[Age group] = Q.[Age group]
AND Q1.Level = Q.Level
AND Q1.TPP > Q.TPP)+1)<>False))
ORDER BY Q.Level, Q.[Age Group], Q.TPP;
_____________________________________________________________
Jeanette Cunningham said:
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 said:
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
