How do I get 4 highest fields

G

Guest

I have setup my access database like an excel sheet. I realize now Access
likes everything in 1 column rather than in rows. I can't rewrite the whole
program so I need help finding the 4 highest values.

Dance Students are judged in 5 disciplines. we give a 4 class high score
total award and a 5 class high score award award.

My data is set up like

# Name BalletScore TapScore JazzScore AcroScore HipHop Score

I need the next column to give me a total of the 4 highest scores.
 
J

John Spencer

You could use a normalizing UNION query to temporarily "fix" the data

SELECT [#], [Name], BalletScore as Score, "Ballet" as ScoreType
FROM YourTable
UNION ALL
SELECT [#], [Name], TapScore as Score, "Tap" as ScoreType
FROM YourTable
UNION ALL
SELECT [#], [Name], JazzScore as Score, "Jazz" as ScoreType
FROM YourTable
UNION ALL
SELECT [#], [Name], AcroScore as Score, "Acrobatic" as ScoreType
FROM YourTable
UNION ALL
SELECT [#], [Name], HipHop as Score, "Hip Hop" as ScoreType
FROM YourTable


Now using that saved query as the source

To get the top 5

SELECT [#], [Name], Sum(Score) as TotalScore
FROM SavedUnionQuery
GROUP BY [#], [Name]

To get the sum of the top 4 just get the total and subtract the minimum
score.

SELECT [#]
, [Name]
, Sum(Score) - Min(Score) as Top4Score
FROM SavedUnionQuery
GROUP BY [#], [Name]

Getting the top 3 would require a slightly different query, but it still
would use the union query as a source.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
G

Guest

The Scores are quries. Each Subject has 3 scores.

For example:
# Name Ballet 1 Ballet 2 Ballet 3 Tap 1 Tap 2
Tap 3 etc etc
1 John Doe 90 94 91 99 96
98
2 Jane Doe 92 98 98 89 90
87
etc etc


Query
Total Ballet:[Ballet 1]+[Ballet 2]+[Ballet 3]

And I have done the query for the other 4 subjects


so now I am looking for a query that would look something like


Highest Class:MAX[Total Ballet][Total Tap][Total Jazz][Total Acro][Total Hip
Hop],1

then

2nd Highest Class:MAX[Total Ballet][Total Tap][Total Jazz][Total Acro][Total
Hip Hop],2

etc etc

then I could add the top 4 scores by doing

Total 4 Highest Class Score:[Highest]+[2nd Highest]+[3rd Highest]+[4th
Highest]

Any help qould be appreciated


John Spencer said:
You could use a normalizing UNION query to temporarily "fix" the data

SELECT [#], [Name], BalletScore as Score, "Ballet" as ScoreType
FROM YourTable
UNION ALL
SELECT [#], [Name], TapScore as Score, "Tap" as ScoreType
FROM YourTable
UNION ALL
SELECT [#], [Name], JazzScore as Score, "Jazz" as ScoreType
FROM YourTable
UNION ALL
SELECT [#], [Name], AcroScore as Score, "Acrobatic" as ScoreType
FROM YourTable
UNION ALL
SELECT [#], [Name], HipHop as Score, "Hip Hop" as ScoreType
FROM YourTable


Now using that saved query as the source

To get the top 5

SELECT [#], [Name], Sum(Score) as TotalScore
FROM SavedUnionQuery
GROUP BY [#], [Name]

To get the sum of the top 4 just get the total and subtract the minimum
score.

SELECT [#]
, [Name]
, Sum(Score) - Min(Score) as Top4Score
FROM SavedUnionQuery
GROUP BY [#], [Name]

Getting the top 3 would require a slightly different query, but it still
would use the union query as a source.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have setup my access database like an excel sheet. I realize now Access
likes everything in 1 column rather than in rows. I can't rewrite the whole
program so I need help finding the 4 highest values.

Dance Students are judged in 5 disciplines. we give a 4 class high score
total award and a 5 class high score award award.

My data is set up like

# Name BalletScore TapScore JazzScore AcroScore HipHop Score

I need the next column to give me a total of the 4 highest scores.
Click to expand...
Click to expand...
 
J

John Spencer

At this point you have totally lost me. I don't understand what you are
trying to do and it is possible I don't understand your structure.

My example would return the sum of scores for each individual (either
all their scores or the 4 highest of there scores).

If what you want is then to get the highest score(s), you should be able
to use the query I posted (or a modification of it to get the top scores)


Two highest scores overall:

SELECT TOP 2 [#], [Name], Sum(Score) as TotalScore
FROM SavedUnionQuery
GROUP BY [#], [Name]
ORDER BY Sum(Score) DESC


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

The Scores are quries. Each Subject has 3 scores.

For example:
# Name Ballet 1 Ballet 2 Ballet 3 Tap 1 Tap 2
Tap 3 etc etc
1 John Doe 90 94 91 99 96
98
2 Jane Doe 92 98 98 89 90
87
etc etc


Query
Total Ballet:[Ballet 1]+[Ballet 2]+[Ballet 3]

And I have done the query for the other 4 subjects


so now I am looking for a query that would look something like


Highest Class:MAX[Total Ballet][Total Tap][Total Jazz][Total Acro][Total Hip
Hop],1

then

2nd Highest Class:MAX[Total Ballet][Total Tap][Total Jazz][Total Acro][Total
Hip Hop],2

etc etc

then I could add the top 4 scores by doing

Total 4 Highest Class Score:[Highest]+[2nd Highest]+[3rd Highest]+[4th
Highest]

Any help qould be appreciated


John Spencer said:
You could use a normalizing UNION query to temporarily "fix" the data

SELECT [#], [Name], BalletScore as Score, "Ballet" as ScoreType
FROM YourTable
UNION ALL
SELECT [#], [Name], TapScore as Score, "Tap" as ScoreType
FROM YourTable
UNION ALL
SELECT [#], [Name], JazzScore as Score, "Jazz" as ScoreType
FROM YourTable
UNION ALL
SELECT [#], [Name], AcroScore as Score, "Acrobatic" as ScoreType
FROM YourTable
UNION ALL
SELECT [#], [Name], HipHop as Score, "Hip Hop" as ScoreType
FROM YourTable


Now using that saved query as the source

To get the top 5

SELECT [#], [Name], Sum(Score) as TotalScore
FROM SavedUnionQuery
GROUP BY [#], [Name]

To get the sum of the top 4 just get the total and subtract the minimum
score.

SELECT [#]
, [Name]
, Sum(Score) - Min(Score) as Top4Score
FROM SavedUnionQuery
GROUP BY [#], [Name]

Getting the top 3 would require a slightly different query, but it still
would use the union query as a source.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have setup my access database like an excel sheet. I realize now Access
likes everything in 1 column rather than in rows. I can't rewrite the whole
program so I need help finding the 4 highest values.

Dance Students are judged in 5 disciplines. we give a 4 class high score
total award and a 5 class high score award award.

My data is set up like

# Name BalletScore TapScore JazzScore AcroScore HipHop Score

I need the next column to give me a total of the 4 highest scores.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Interfacing two fields 8
How do I select the maximum of a subset of records? 3
Conditionally summing cells based on conditions in other rows 3
Trying to update multiple fields in the same record (same table) 2
Easy question: Sort ascending in a row? 3
how do I use array references in VLOOKUP()? 2
Rank and multiple Tie-breaks problem 1
VBA code for Rank function - urgent - by class 3

Top