Selecting top 3 of each class

[Mich2too]

Am working on a database for local car shows. Have a query set up to do all the needed calcs (numerous judges, numerous cars, numerous categories judged for each car, totals, etc.). How do I mark the 1st, 2nd & 3rd place in each class?? Just learning the SQL part of Access. Know that curing this problem will help make it easier to create the needed reports to print the actual certificates. Thanks in advance.

 

[bcoats]

if you are just wanting to mark the winners and if the data in the catagory is a score or something like that you could querry the database using a recordset (dynaset) in your code and programatically set the top 3. Something like this.

SQLString = "select Catagory1, Catagory1Place from TableNeame order by Catagory 1 desc" (this should get the biggest number on top)
set db = Currentdb()
set Rec = db.OpenRedordset (SQLString, dbOpenDynaset)
(The dynaset allows the changes to be stored)
for X = 1 to 3 do
Rec!Catagory1Place = X
Rec.MoveNext
next

You could also add checks for ties if you wish to. Hope this helps.

Brian Coats

 

[Mich2too]

Oh THANK YOU for answering so quickly. Accidently duplicated this first post and have had responses to it...but you've answered me the most directly so far.

Here's the SQL for the query that contains all the information (other than the actual placing in each class) that would be needed to print the award certificates accordingly.

SELECT Cars.Class, Classes.[Class Description], [Score Cards].[Car#], Sum([Score Cards].Total) AS [Total Points], 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 Classes INNER JOIN (([Mailing List] INNER JOIN Cars ON [Mailing List].[ID#] = Cars.[Owner ID#]) INNER JOIN [Score Cards] ON Cars.[Car#] = [Score Cards].[Car#]) ON Classes.[Class ID] = [Score Cards].Class
GROUP BY Cars.Class, Classes.[Class Description], [Score Cards].[Car#], 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;

Would like to have 1st Place, 2nd Place, etc. automatically print with each record as the users of this database will have little to no computer experience and want to make this as simple as possible with as little user input at this point as possible.

I would be deeply endebted and ever thankful if you could help me with the actual SQL coding into what you have suggested. (((((((((((((((THANKS))))))))))))))))

 

[bcoats]

First of all, I think you should include in the select part the car.place... You can't update it if you don't have it.

Second, I am thinking about using this querry in a method (button click etc.) Whatever method you choose you could do something like this.

dim SQLString as string
dim db as database
dim Rec as recordset
dim CarClass as string
dim Place as integer

SQLString = <Query from above with the proper syntax and variables>
set db = currentdb()
set Rec = db.OpenRecordset(SQLString, dbDynaset)
' This stores the result in the querry and is updateable
Place = 1
CarClass = Rec!class
while not Rec.EOF
If Rec!class <> CarClass then
Carclass = Rec!class
Place = 1
end if
if place < 3 then
Rec!Place = Place
Place = Place + 1
end if
Rec.MoveNext
wend

' still in the method, to do the printing then...
' I think you could do another SQL querry that has an
' additional clause in th where like car.place <> &quot;&quot;
' then print them out, or use a form with that querry
' and automatically print it out. If you need help automatically printing another form from this one, let me know

This should take care of all the cases such as only two cars in the class etc. I hope this helps get you on your way.

 

[Mich2too]

THANK YOU SO MUCH!!! You have me well on my way now. Am printing out the last two posts and will be sitting down to digest it into what I need tomorrow.

As I am just learning the SQL part of Access (more out of shear survival than anything else)what good reference books would you suggest? Have SQL for Mere Mortals in hand and about 2 chapters into it. Have a surgery coming up in the next few weeks and will be off work for a number of weeks. Will have LOTS of time to read, learn, digest and attempt to conquer.

 

[bcoats]

I don't know of any books on it, I just learned it in class one time in college and have gone to SQL tutorial sites online to learn SQL. Not much to it. Glad I could help

Brian

 

[Mich2too]

Okay...I'm lost. Tried to set up a query using in SQL view using the above suggestion. Even cut & paste in case of typing errors and editted the existing query info as needed.

dim SQLString as string
dim db as database
dim Rec as recordset
dim CarClass as string
dim Place as integer

SQLString = <SELECT Cars.Class, Classes.[Class Description], [Score Cards].[Car#], Cars.Placing, Sum([Score Cards].Total) AS [Total Points], 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 Classes INNER JOIN (([Mailing List] INNER JOIN Cars ON [Mailing List].[ID#] = Cars.[Owner ID#]) INNER JOIN [Score Cards] ON Cars.[Car#] = [Score Cards].[Car#]) ON Classes.[Class ID] = [Score Cards].Class
GROUP BY Cars.Class, Classes.[Class Description], [Score Cards].[Car#], Cars.Placing, 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;>
set db = currentdb()
set Rec = db.OpenRecordset(SQLString, dbDynaset)
' This stores the result in the querry and is updateable
Placing = 1
CarClass = Rec!class
while not Rec.EOF
If Rec!class <> CarClass then
Carclass = Rec!class
Placing = 1
end if
if placing < 3 then
Rec!Place = Place
Placing = Placing + 1
end if
Rec.MoveNext
wend


When I tried to view this query...got an error message saying that it is an invalid SQL statement. I toyed with different versions and got no where.

You had suggested using this as a button, but really have no place to put the button yet. Have not set up the switchboard until I know exactly what to name everything. Have learned from past experiences to not create the switchboard(s) until the last step as continually changing names can cause problems if I forget to update the switchboard.

 

[bcoats]

try building a querry in the access querry tab from the database window. I sometimes put my querries in there and run them to get them working right and check them, then I set them = to SQLString in my code so that I know they will work and I use a form of character stuffing basically to place a (&quot where ever I need one, for instance if I have the string,

select * from table where idnum = &quot;10&quot;; it becomes
sqlstring = &quot;select * from table where idnum = &quot;&quot;10&quot;&quot;;&quot;

you have to have the double quotes there to get a single in the sql string. You used a <STRING> format that might keep you from having to do this though, I have never seen this anywhere and don't know about it.

But I would say to build the sql statement in the querry area under the SQL design and it will tell you where you have problems with the sql statement. Let me know If I can help anymore.

Brian