Selecting data with criteria from 3 or 4 tables

[RaceStats]

I am new to SQL and need help with some complicated transactions. 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 - 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.

 

[philhege]

Hi RaceStats,

You need to do two things here.

One is to create the correct join statement to retrieve the data.

The other is to put this code in a stored procedure and call the SP from your code.

As a simple stab at the SELECT statement:
--
SELECT Drivers.DriverFullName, SUM(Results.ResultPoints),
AVG(Results.ResultPoints), AVG(Results.ResultStart),
AVG(Results.ResultFinish), SUM(ResultLapsComp),
SUM(ResultWinnings), SUM(ResultAdjustment)

FROM Results
JOIN Drivers on Drivers.DriverID = Results.DriverID

WHERE Year(Results.ResultDate) > @p_dYear AND
Events.TrackID = @p_iTrackID

GROUP BY Drivers.DriverFullName
--
Pass @p_dYear and @p_iTrackID in your SP call.

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[SQLSister]

I too prefer Phil's approach. It is especially critial to learn the correct join syntax as the older style joins are going away and even in SQl 2000 will not work properly if you need a right or left join so you should stay away from using them as they create bad habits.

When you are trying to debug a statment like this, print out the SQL rather than executing it and you will probably immediately see the problem. I would guess that one of your parameters is null.

Questions about posting. See faq183-874

 

[RaceStats]

Thanks for the help, I truly appreciate it. However, I am currently utilizing an Access database. I could create the query in Access, but how would I call it from there?
Thanks again for the help.

 

[philhege]

The join syntax is different for Access SQL statements (and, IMHO, pretty ugly).

You should post this question to the Access forum.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.