Fuel Efficiency

[316854]

Access newbie. Access 2013.

Please see attached database.



How do I add a calculated field for miles per gallons?

[June7]

Your Miles data is cumulative which means you need the difference between sequential records. Not easy, need subquery. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

Should not use spaces, special characters/punctuation (underscore is exception) in names, nor reserved words as names. The $ and Date as field names is a bad idea.

[316854]

The more I pull my hair out trying to use Access for calculations the more it makes me think that I should be using Excel for this kind of stuff. It seems like, regarding calculations, what would take me 30 seconds to set up in excel takes 5 minutes in Access, possibly even needing VBA.

Many times I wonder why I'm trying to leave Excel for Access.

I need encouragement.

[June7]

Excel is a better tool for mathematical analysis but very poor database manager. Sometimes like using a hammer to do a screwdriver task. You might get it to work but could be frustrating experience. Access can be made to do the calcs but can be harder than Excel. The data must be properly defined.

I took another look at your data. The subquery won't work because there are no identifiers like the MeterID and AddressID in Allen Browne's example.

An alternate approach requires a sequential ID without gaps. Your autonumber is not without gaps. If the dates did not have gaps, that field could serve as the sequential ID.

Here is one that doesn't require any unique ID:
SELECT MPG.*, ([Miles]-DMax("Miles","MPG","[Date]<#" & [Date] & "#"))/[Gal] AS M_P_G FROM MPG;

However dates cannot be duplicated unless there is other criteria to filter with, such as a vehicleID.