Averaging a Calculated field containing a Division calculation

[BenDatabase]

Hi - I have an Access database that I created to track fuel mileage for my Automobiles several months ago. Originally the database worked without issue. However a few weeks ago the charts I had on my main Form and my Mileage trend report quit working. I've been able to correct the chart issue but the Mileage trend report still has an issue. Originally the report would display the mileage each auto got for each tank of gas generated from a query. The report would then average those mileage numbers for each auto so that I would have an overall average mileage for each auto using the totaling on the report. That totaling has quit working. I get an error message shown in Figure #1 when trying to run the report.

Figure #1


I've been able to trace the issue all the way back to the table containing the data by creating a field (not sure why I didn't do this originally) to calculate the MPG for each entry in the table itself as shown in Figure #2 attached.



Figure #2


I've discovered that when I add the totaling row to the table in datasheet view, this MPG field the functions SUM, Average, Standard Deviation, or Variance will not work in the totaling row for that column. I have another calculated field in the same table which will do those things without issue. The only difference between the two calculated fields is that the MPG field is a division calculation and the other is a Multiplication calculation. I'm sure this is something simple but so far have been unable to figure it out. Anyone have any ideas or suggestions I could try to resolve the issue?

Thanks

[kd2017]

Calculated Fields

How do you get Access to store the result of a calculation?
For example, if you have fields named Quantity and UnitPrice, how do you get Access to write Quantity * UnitPrice to another field called Amount?
The best answer is, "Don't!"
Calculated fields belong in queries, not tables.

Calculated columns are part of life on a spreadsheet, but do not belong in a database table. Never store a value that is dependent on other fields: it's a basic rule of normalization. Break the rule, and you have to worry about how to maintain the calculation correctly in every possible circumstance.
Ask yourself, "Would it be wrong if my field contained a value different from the calculation?" If you answer Yes, you must not store the field. If the difference would be meaningful, you do need the field.
So, how do you get the calculated field if you do not store it in a table? Use a query:

1. Create a query based on your table.
2. Type your expression into the Field row of the query design grid:
   Amount: [Quantity] * [UnitPrice]

This creates a field named Amount. Any form or report based on this query treats the calculated field like any other, so you can easily sum the results. It is simple, efficient, and foolproof.

http://allenbrowne.com/casu-14.html

[BenDatabase]

kd2017 thanks for your reply. I agree with you and originally calculated the MPG using a query for use on the report. The reason I backed up to the table was to troubleshoot the issue described above of Access not averaging the MPG column now and giving me the error shown and described in the OP. Once I figure out what the issue is I'll be going back to calculating the MPG using the Query for the report to display.

Thanks,
Ben

[June7]

Is there division by zero? Although I would expect a different error message.

I have never used Decimal and Currency number types - only Double, Single, and Long Integer.

A Calculated type field in table is not same as code saving a calculated result. Only real issue I see with table Calculated type fields is they complicate migrating to another db platform such as SQLServer. Same goes for multi-value fields.

[BenDatabase]

Still playing around with trying to figure out the issue. The image below may help in seeing what is going on. This is from my query that calculates the MPG. When I add the totals row to the query and try to use the average function I get the error shown below which is the same error the report is giving me when trying to average the MPG on the report. The query runs fine if I don't try to average the column. All the data used is contained in the Access Database and has not been imported from another source but was entered directly into the Access database. It's like the Average function is trying to return a value that doesn't fit in the datatype of the column. However in messing with the datatype for the column nothing changes in relation to the error.

[June7]

Think we need data to analyse. If you want provide db, follow instructions at bottom of my post.

[BenDatabase]

Ok here is my database. I've left the MPG field in the Milleage table as well as in the completeMPGCalculation query. Interestingly enough I've found that it works if the datatype is currency. Of course that isn't the right datatype for the data haha but it is a note of interest.

Thanks,
Ben

[BenDatabase]

Ok well I solved the issue though not sure why it broke. Turns out it was indeed a datatype issue. My MPG calculation was using the MilesDriven field divided by the Gallons field in the Mileage table. Both of which were decimal (6,3,3) datatype. I switched these two fields to Double datatype and the average function in the Total row started working. I set everything in the Queries and Report back as I had them originally leaving the two fields in the Mileage table datatypes as doubles and the Report calculated the overall Avg MPG for each auto as it should. As I'm pretty sure I didn't change the datatype of these two fields from when I built the database originally several months ago I'm not sure why it worked to start with and then "broke". Apparently Access is not able to calculate an Avg on a Decimal Datatype using the Avg Function in the Total Row.

Thanks June7 it was your original comment about not having used the Decimal datatype but instead the Double datatype that I tried the Double and solved the issue.