• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 711
  • Last Modified:

SQL query of MS Access table in VB6 not calculating results properly.... Is it a 'data type' issue?

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.
mdb-tabled-data.jpg
mdb-design-view.jpg
flexgrid-results.jpg
0
jazjef
Asked:
jazjef
  • 3
  • 3
2 Solutions
 
FlysterCommented:
Just a guess here. Try adding an extra set of brackets in your formula. One after the comma after HRun and the other before /TAB:

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"

Flyster
0
 
jazjefAuthor Commented:
Well, that changed it alright Flyster----- now every BAvg is either 1.000, or .800  ..... but you're on to something here. Any other ideas?
0
 
FlysterCommented:
What about this:

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, [SI]) + IIf(IsNull([DB]), 0, [DB]) + IIf(IsNull([TR]), 0, [TR]) + IIf(IsNull([HRun]), 0, [HRun]))/TAB) AS BAvg From Batting GROUP BY Team, Batter"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ThomasMcA2Commented:
Your averages are wrong because every iif...IsNull is returning either 0 or 1.

Here is the syntax for iif:
iif ( condition, value_if_true, value_if_false )

Open in new window


Change them like this:
IIf(IsNull([SI]), 0, [SI]) 

Open in new window

0
 
FlysterCommented:
Looking at your IIf statements, If the field value was null, the aggregated value is 0. If not, then it's 1. It should be the field value. The extra brackets directs the order of calculation. Your original code was looking at it as [SI] + [DB] + [TR] + ([HRun]/TAB). You want: ( [SI] + [DB] + [TR] + [HRun] ) / TAB
0
 
jazjefAuthor Commented:
ha ha..... that was the other piece to the puzzle; nice work ThomasMcA2. Combined with Flyster's added parentheses it works great. I'll split the points since the statement won't run correctly without both solutions in place. Many thanks guys.
0
 
jazjefAuthor Commented:
Superb. Quickly solved with very clear explanation. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now