Incorrect result for an average

[DJBummy]

Have searched but could not find my solution. I have a bowling league database and I am doing averages based on games bowled. On certain averages the results are incorrect. Such as

Tot pins = 1169 divided by
tot games = 6

the result should be 194.83

but the result in my query is 196

have tried the Round function, Abs function and cLng function to no avail.

Thank you

 

[FoFa]

Ae they only averages with a fractional value affected?
Any comon "thing" you can see by them?
Just first guess would be I think total pins is most likely a interger value so it isn't tracking decimal places properly.

 

[DJBummy]

Yes some of the averages are correct and some are not.

The only common thing I can see is that the [Series] and [GameSum] fields get there numbers from another query and they are grouped by sum in the query i am having trouble with.

Field: Series
Table: qryWeeklyBlank1
Total: Sum

Field: GameSum
Table: qryWeeklyBlank1
Total: Sum

This is the expression i am using to do the avg.

TotAvg: Avg(IIf(tblSchedules.WeekNo<4,[StartAvg],IIf([GameSum]=0,[StartAvg],[Series]/[GameSum])))

 

[FoFa]

Just going to jump out on a limb here, but I would try to CDBL([SERIES]) and CDBL([GAMESUM]) to see if that makes any difference.
I would try in your select query first, if that doesn't work try to do it in the other query that groups them. Division results (and someone maybe able to explain it better than I) determines it's working results by the decimal places (or datatype) of the input variables. I have found this sometimes causes issues like this. I have actually create a table, with the proper datatypes, and put the info. in there, then turn around and read it with the second query to get the proper results in the past. But I do think it could be worked with some of the newer functions like CDBL as an example (back then I only had VAL)

 

[DJBummy]

Thank you for your reply

Tried the CDbl in both queries. Didn't work in select query. Got the SubQueries cannot be used in expression error in the sum query.

Actually I am using the results from the query in a spreadsheet. I can make the fix there by adding another column. But I wanted to try and do it in the query first before doing that. In other words eliminating a step.

 

[FoFa]

One other Thought that has worked for me in the past. Change the datatype to single or double for the base numbers. You most likely have one or more them as integers (maybe LONG). Only takes a sec. to do that.

 

[DJBummy]

nope that didn't work either. Well I think Im going to do some more research into this some other time. For now I will use excel to make it right. Don't like to do that but you gotta do what you gotta do. I thank you for your time. I will repost if I find the solution. Thanks again

 

[gemma-the-husky]

Have any of the figures making up the total been entered as real numbers eg 200.8, but displayed as integers.

Alternatively, change the result type to a double type. An average of exactly 196 seems strange, and this may include some rounding error.

Try taking a line out and seeing if it averages 5 scores correctly.

Is the 196 a calculated value on a form, or in a query. Might make a difference

 

[DJBummy]

the scores have been entered as whole numbers ae: 196, 200, 234 etc.

have already tried changing to double but no help.

the totals are done in a query.

i agree i think it is a rounding problem. I have tried the round() function but results were still the same. it was at this point my frustration began.

The only thing i can figure could be the cause, is i do a calculation to determine the number of games bowled (anywhere from 0 to 3, but most of the time 3). There are a few reasons there can be less than 3 games bowled, bowler was absent for all three games, missed first game but bowled last two, etc. So with each passing week the scores and number of games are progressively totaled throughout the year.

=iif([Absent1]=true,0,iif([AbsentAll]=true,0,1]))

So i am assuming the total of the games could be 6.000015 or 5.99998 because of the way access handles decimals.

As i mentioned before. I have a semi work around for this. I am doing a transferspreadsheet of several queries into a excel file (Normally i would use reports but the old dudes in the league like to see the data a certain way, hence the spreadsheet). I can fix the problem in the spreadsheet by adding a column that does the correct calculation and the results are what is expected.

Where i am at now is that there has to be a way to fix this in access and it bothers me that i can't figure it out. Just the way I am! Normally i will avoid posting a problem and do a search, but this one had me frustrated.

Thank you for your time.

By the way i have not given up yet....

 

[WayneRyan]

DJ,

I haven't read this thread in great detail, but when calculating averages
any NULL values will be ignored. So that could be the cause of your
discrepancy.

As an experiment, change any NULLs to 0 and your calculated average
should drop a bit.

hth,
Wayne

 

[DJBummy]

Not sure if this is the correct thing to do or not but it works none the less.

Originally i had two queries
1st to collect the data
2nd to sum the data

To correct my problem i simply added a 3rd query using the the 2nd as my source. I then used the following formula to do my averages:

TotAvg: IIf([SumOfSeries]=0,[StartAvg],Format([SumOfSeries]/[SumOfGameSum],"#.##"))

This has corrected my problem......

thanks to all of you for your help.