Selecting data with criteria from 3 or 4 tables 2

[RaceStats]

I am new to SQL and need help with some complicated transactions. Using an Access DB and writing the code in VB6, I am creating a Racing Stats Program. The current 2 queries that I am trying to figure out gather driver stats for a selected track or track type. The tables involved and contents are:

Results - DriverID, EventID, Points
Drivers - DriverID, DriverFullName
Events - EventID, TrackID
Tracks - TrackID, TrackTypeID
TrackType - TrackTypeID, TrackID

In the first query I want to select all of the results per driver for the selected TrackID. The Results table does not contain the TrackID. It is located in the Events table which is reference by the EventID in the Results. My current code follows (which errors out): FYI - I have working queries that are similar to below but most of the data is contained in the Results table.

strSQL = "SELECT Drivers.DriverFullName, SUM(Results.ResultPoints), "
strSQL = strSQL & "AVG(Results.ResultPoints), AVG(Results.ResultStart), "
strSQL = strSQL & "AVG(Results.ResultFinish), SUM(ResultLapsComp), "
strSQL = strSQL & "SUM(ResultWinnings), SUM(ResultAdjustment) "
strSQL = strSQL & "FROM Results, Drivers "
strSQL = strSQL & "WHERE Results.DriverID = Drivers.DriverID AND "
strSQL = strSQL & "Year(Results.ResultDate) > " & CStr(cmbYear) & " AND "
strSQL = strSQL & "Events.TrackID = " & CStr(iTrackID) & " "
strSQL = strSQL & "GROUP BY Drivers.DriverFullName"

I've tried a couple of different approaches but I always error out. The code above produces "No value given for one or more required arguments". Another error concerned a non reference aggregate (I think).

The second query is similar to the above but it goes another step deeper. This time I want the same information but this is based on track type which adds another table into the equation. The query would be the same but now I need all of the results where all of the Tracks with the same TrackTypeID (which is referenced in the Tracks table) is the same as the TrackID of the selected track.

Whew! I know this is a lot of information and a lot to ask for. I've been working on this database and program off and on for 4 years and I'm getting closer to having something that works. Any help would be greatly appreciated.

LOL!! Once I get past this hurdle it's on to the next one which is calculating averages at intervals (Last 3, 4, 6 8, etc..) and then perfoming calcualtions on those results.

 

[lespaul]

First, you will be able to do much more with SQL if you read the Understanding Joins article below.

Second, do you have any null values in any of the fields you are trying to average?

Have you tried building the query in the design grid, getting the results you want and then switching to the SQL view to get the query you need in your code?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Golom]

Just for starters

strSQL = "SELECT D.DriverFullName, SUM(R.ResultPoints), " & _
         "AVG(R.ResultPoints), AVG(R.ResultStart), " & _
         "AVG(R.ResultFinish), SUM(R.ResultLapsComp), " & _
         "SUM(R.ResultWinnings), SUM(R.ResultAdjustment) " & _
         "FROM (Results R INNER JOIN Drivers D " & _
         "     ON R.DriverID = D.DriverID) " & _
         "     INNER JOIN Events E " & _
         "     ON E.EventID = R.EventID " & _
         "WHERE Year(R.ResultDate) > " & Val(cmbYear) & " AND " & _
         "E.TrackID = '" & CStr(iTrackID) & "' " & _
         "GROUP BY D.DriverFullName"

You were referencing the table "Events" but it didn't appear in the FROM clause.
"Year" returns a number so you want to compare ot to a number ... not a string.
You need single quotes around strings like "Cstr(iTrackID)"
INNER JOINS are usually much faster than filtered cross joins.

 

[RaceStats]

Leslie

Thanks for the help. That worked great. Also thanks for the article. Much of that information I had used when I created my DB, but this gave a little better understanding of some areas I was fuzzy in.

To answer your question, there are no NULL values in the Points field however there are Zero's.

Now my text task is to figure out getting averages at intervals (Last 3, 4, 6, 8, etc..). Any suggestions would be greatly appreciated.

 

[lespaul]

glad it helped....as far as your next task, do you mean last 3 events? years?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[RaceStats]

Lelsie/Golom

Golom's code worked great and your advice has helped me to better understand what's going on. I'm Technical. I'll keep your environment running, build, load, & maintain your servers, etc. but programming has always been difficult for me to pick up so please bear with me.

I'm working my way up the ladder so to speak. I now understand the code and what's going on but I just can't seem to wrap my head around the next step(s).

First expand on the current query but I need to go another table deeper. This time I need to select all of the tracks that have the same TrackTypeID as the selected track. See my table description in the first post.

Next is I need averages taken at intervals (last 3 events, last 4, 6, 8, etc.). I not only need to display those averages, but I need to do calculations off of them. First steps first. Help getting getting those averages would be greatly appreciated.

This has been very cool and I truly appreciate the help. This has been a labor of love and hate for the past few years and the progress that has been made is great to see.

Thanks for all the help.

 

[lespaul]

First expand on the current query but I need to go another table deeper. This time I need to select all of the tracks that have the same TrackTypeID as the selected track

In order to get the TrackTypeID, you need to join into that table (have you read the link in my signature regarding Joins? that will help you tremendously).....

strSQL = "SELECT D.DriverFullName, SUM(R.ResultPoints)," & _
         "AVG(R.ResultPoints), AVG(R.ResultStart), " & _
         "AVG(R.ResultFinish), SUM(R.ResultLapsComp), " & _
         "SUM(R.ResultWinnings), SUM(R.ResultAdjustment) "&_
         "FROM (Results R INNER JOIN Drivers D " & _
         "     ON R.DriverID = D.DriverID) " & _
         "     INNER JOIN Events E " & _
         "     ON E.EventID = R.EventID " & _
         [b]"     INNER JOIN Track T ON E.TrackID = T.TrackID " & _
         "     INNER JOIN TrackType T2 on T.TrackTypeID = T2.TrackTypeID " [/b]& _
         "WHERE Year(R.ResultDate) > " & Val(cmbYear) & " AND " & _
         "E.TrackID = '" & CStr(iTrackID) & "' " & _
         "GROUP BY D.DriverFullName"

now that you have joined into those two tables, you can get any field from them.

In order to get the intervals, look into the TOP predicate.
[tt]
SELECT TOP 10 DriverFullName FROM Drivers ORDER BY DriverID
[/tt]
will return the first 10 records when sorted by ID.




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[RaceStats]

Leslie

Thanks for the SQL article I totally missed that thinking the 2 links were the same. I'll check that out. Thanks for the help. I'll be giving this a try a little late. I can't you enough for the help.

 

[RaceStats]

Leslie

I tried your suggested code and received the following error: "Missing operator in expression" and it displays the following code in the pop-up:

Events E ON E.EventID = R.EventID " & _
" INNER JOIN Track T ON E.TrackID = T.TrackID " & _
" INNER JOIN TrackType T2 ON T.TrackTypeID = T2.TrackTypeID " & _

I fooled with it all weekend and did a bunch of research but I can't figure out the problem.

Thanks a ton for all the help!!

 

[PHV]

strSQL = "SELECT D.DriverFullName, SUM(R.ResultPoints)," & _
"AVG(R.ResultPoints), AVG(R.ResultStart), " & _
"AVG(R.ResultFinish), SUM(R.ResultLapsComp), " & _
"SUM(R.ResultWinnings), SUM(R.ResultAdjustment) "&_
"FROM [!](([/!](Results R INNER JOIN Drivers D " & _
" ON R.DriverID = D.DriverID) " & _
" INNER JOIN Events E " & _
" ON E.EventID = R.EventID[!])[/!] " & _
" INNER JOIN Track T ON E.TrackID = T.TrackID[!])[/!] " & _
" INNER JOIN TrackType T2 on T.TrackTypeID = T2.TrackTypeID " & _
"WHERE Year(R.ResultDate) > " & Val(cmbYear) & " AND " & _
"E.TrackID = '" & CStr(iTrackID) & "' " & _
"GROUP BY D.DriverFullName"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

did you make sure that the joins were referring to the correct fields in each table? So Track contains TrackID and Event contains TrackID and they are the same datatype? What about Track and TrackType?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[RaceStats]

LOL!! I got a copy of Beginning SQL by Paul Wilton & John Colby. They have the best explaination of JOINS that I've come across so far. I put the extra "()" and got rid of the syntax error and at least received a result set. However all that is being returned are the results fro the selected track not all of the tracks taht have the same TrackType. I'll fool with it a little and see what I can come up with but more than anything I wanted to take this opportunity to thank all of you that have helped me. I'm learning and I'm really excited.

Below is a copy of my current code:

strSQL = "SELECT D.DriverFullName, SUM(R.ResultPoints), " & _
"Count(R.ResultPoints), MIN(R.ResultStart), " & _
"AVG(R.ResultPoints), AVG(R.ResultStart), " & _
"AVG(R.ResultFinish), SUM(R.ResultLapsComp), " & _
"SUM(R.ResultWinnings), SUM(R.ResultAdjustment) " & _
"FROM (((Results R INNER JOIN Drivers D ON R.DriverID = D.DriverID) " & _
" INNER JOIN Events E ON R.EventID = E.EventID) " & _
" INNER JOIN Tracks T ON E.TrackID = T.TrackID) " & _
" INNER JOIN TrackType T2 ON T.TrackTypeID = T2.TrackTypeID " & _
"WHERE Year(R.ResultDate) > " & CStr(iYear) & " AND " & _
"E.TrackID = " & CStr(iTrackID) & " " & _
"GROUP BY D.DriverFullName

 

[PHV]

not all of the tracks taht have the same TrackType
Replace this:
"E.TrackID = '" & CStr(iTrackID) & "' " & _
with this:
"T2.TrackID = '" & CStr(iTrackID) & "' " & _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RaceStats]

OUCH!!! At first I thought that would work. I made the change and I got the following error: "No value given for one or more required parameters". After looking at the code it seems to me that this would limit the result set to only those records with same TrackID as that of the selected track.

We're close to the solution on this though. I'm thinking that it's an ordering problem. But the thing is it looks to me like we are ordering things correctly. We have matched all of the Primary and Foriegn Keys, so that looks good.

The rules for the result set should be (I think):

ALL of the Results from table Results whose EventID in
table Events has the same TrackID in
table Tracks that have the same TrackTypeID in
table TrackType whose TrackID =
the selected track (combobox)

So where we are getting stuck is pulling all of the TrackID's and passing them back to Events. Does this make sense?

 

[PHV]

Seems the schema posted in your first post is wrong (at least for the TrackType table) ...
Another guess
strSQL = "SELECT D.DriverFullName, SUM(R.ResultPoints), " & _
"Count(R.ResultPoints), MIN(R.ResultStart), " & _
"AVG(R.ResultPoints), AVG(R.ResultStart), " & _
"AVG(R.ResultFinish), SUM(R.ResultLapsComp), " & _
"SUM(R.ResultWinnings), SUM(R.ResultAdjustment) " & _
"FROM ((Results R INNER JOIN Drivers D ON R.DriverID = D.DriverID) " & _
"INNER JOIN Events E ON R.EventID = E.EventID) " & _
"INNER JOIN Tracks T ON E.TrackID = T.TrackID " & _
"WHERE Year(R.ResultDate) > " & CStr(iYear) & " AND " & _
"T.TrackTypeID = (SELECT TrackTypeID FROM Tracks WHERE TrackID=" & CStr(iTrackID) & ") " & _
"GROUP BY D.DriverFullName"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RaceStats]

Awesome!! That did it. I now have the result set that I'm looking for. One more hurdle has been jumped and it's on to the next one.

I want to thank everyone for their help. This was fun and I have learned a lot from it. For quite some time I've been focusing on creating the database which now contains over 40,000 records and it will increase by about 1,600 a year. It's really cool to begin to see some results from that. Y'all have been great and I really can't express how much I appreciate this.

Next step - Performing calculations on/with the recordset but that's for another thread.

THANKS!!!!!!!!!!!!!!!!!