Here's the SQL:
SELECT
SessionResults.Driver,
COUNT(IIF(SessionResults.Place=1, 1, NULL)) AS Wins,
ROUND(AVG([SessionResults].[Place]), 2) AS [Avg Race Placing],
ROUND(AVG([SegmentData].[SegPlace]),2) AS [Avg Heat Placing],
COUNT(SessionResults.TotalLaps) AS [Total Laps],
EventHistory.TrackID AS [Track/Layout],
MAX(EventHistory.Date) AS [Last Race]
FROM
SessionResults, SegmentData, EventHistory
WHERE
(((SessionResults.EventID) = [EventHistory].[EventID])
AND ((SegmentData.EventID) = [SessionResults].[EventID]))
GROUP BY
SessionResults.Driver, EventHistory.TrackID;
I've tried all kinds of different approaches but the second expression in the select never produces the right results. Does anybody know how one can count the SessionResults.Place columns that have a value of 1?
I've tried all kinds of methods. I've been able to count in that position other column values accidently, I've counted negative numbers, and completely inaccurate numbers. I can do a specific select and get the right data, just not in a complex Select statement like above.