ranking calculated fields in a query
- From: Steve S <SteveS@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 5 Jan 2008 15:04:02 -0800
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: Jeanette Cunningham
- Re: ranking calculated fields in a query
- Prev by Date: Re: Make table query
- Next by Date: Re: Populate a field by query
- Previous by thread: RE: Is it possible to write subqueries in a query?
- Next by thread: Re: ranking calculated fields in a query
- Index(es):
Relevant Pages
|
Loading
