Using: MS Access 2010; VB6 with MSHFlexGrid
I have an SQL query in VB6 that calls data from an MS Access table called 'Batting'. It's baseball data that tracks singles, doubles, triples, homers, walks, strikeouts, at-bats etc. All I want to do is calculate 'batting average' by aggregating all the different types of hits, and dividing them by at-bats. My SQL statement will aggregate the hits, but it's wrong in calculating the batting average. My guess is that it has to do with numerical values being treated as strings. I have no hair left to pull out---so I decided to post here. I have Total At-Bats [TAB], singles [SI], doubles [DB], triples [TR], and home runs [HRun] that I'm aggregating.
Here's the statement:
SqlString = "Select Batter, Team, SUM([AB]) As TAB, SUM([1B]) As SI, SUM([2B]) As DB, SUM([3B]) As TR, SUM(
) As HRun, (IIf(IsNull([SI]), 0, 1) + IIf(IsNull([DB]), 0, 1) + IIf(IsNull([TR]), 0, 1) + IIf(IsNull([HRun]), 0, 1)/TAB) AS BAvg From Batting GROUP BY Team, Batter"
I have a picture of the tabled data, the table design view, and the flexgrid results in VB6 that the query creates. Notice how every batting average result is 3.25 ..... this is despite the fact I have some code that rounds the values of the column off to .XXX numerical digit format. This result is really strange; not sure where I'm going wrong.
Any help is really appreciated.