Marking Car Show Class winners

[Mich2too]

Am working on a database to total judge's scores for each car participating in a car show. (The database also does other things, but my only problem right now is this one.) From a query (or queries), we would like to create custom award certificates for first, second, and third place winners from each class right there on the spot. I'm just learnign the SQL parts of Access and this is what I have so far:

SELECT Cars.Class, [Score Cards].[Car#], Sum([Score Cards].Total) AS [Total Points], Cars.Class, Cars.Year, Cars.[Car Manufacturers], Cars.Model, Cars.Style, [Mailing List].[First Name #1], [Mailing List].[Last Name #1], [Mailing List].[First Name #2], [Mailing List].[Last Name #2]
FROM ([Mailing List] INNER JOIN Cars ON [Mailing List].[ID#] = Cars.[Owner ID#]) INNER JOIN [Score Cards] ON Cars.[Car#] = [Score Cards].[Car#]
GROUP BY Cars.Class, [Score Cards].[Car#], Cars.Class, Cars.Year, Cars.[Car Manufacturers], Cars.Model, Cars.Style, [Mailing List].[First Name #1], [Mailing List].[Last Name #1], [Mailing List].[First Name #2], [Mailing List].[Last Name #2]
ORDER BY Cars.Class, Sum([Score Cards].Total) DESC;

Any help would be GREATLY appreciated. THANKS!!

 

[JoeMiller]

And the question is?? Joe Miller
joe.miller@flotech.net

 

[Mich2too]

"From a query (or queries), we would like to create custom award certificates for first, second, and third place winners from each class right there on the spot."

Have the query set up to total the scores given to each car by each judge and lists them by class then by high score. Don't know how to mark them in a field as First place, second place, third place.

 

[JoeMiller]

How does the scoring work? What are the values you'll be looking it, 1 to 100, 1 to 10, A to F? It sounds like you could use a crosstab query to accomplish what you want, but depending on the scoring system it may be less than optimal.

Why do you have to do it from a query? Why can't you make a report that is grouped by Class and then list the cars in descending order? Joe Miller
joe.miller@flotech.net

 

[Mich2too]

Am using a scoring table with query to total scores given by each judge in a number of different categories for each car and total those scores. Used a seperate query to create the grand total for each car. Reason I was using queries instead of reports is that I would not need to print ALL the cars entered...only the top three of each class. Have also had more luck printing queries (when needed) on the least amount of paper. Even when fussing with a report layout to get the maximum use of paper, queries still take up less paper to print when needed.

Unfortunately, I have a year to refine the entire process as the car show has been cancelled due to lack of manpower and too many other conflicting auto events in the area that same weekend. But am still interesting in learning how to do this.