Summing records from current to last non-null value (1 Viewer)

DWayne

Registered User.
Local time
Today, 17:08
Joined
Feb 12, 2009
Messages
14
I have a section of a database that keeps track of gas purchases (see attached photos), called tblGasoline.

I have constructed a subquery that will calculate the "previous" ODO read so I can tally the total miles driven since the last fill up.

I am looking to create an additional field that calculates the gallons from tblGasoline, but the biggest issue is when I connect that with my subquery with the goal of finding the miles per gallon is that a null value in the ODO record will throw off the gallons used.

You will notice in the picture (qryMileageAndMPG.jpg) that the PrevODO field is the same for both dates (TrxDate = 10/15 and 10/23). I tried to generate a JOINED query that summed up the gallons purchased for whenever the PrevODO was the same (qrySumOfGallons.jpg). This actually crashes Access, and I think that's because I'm looking to create a join on a field (PrevODO) that is actually generated within a subquery.

I would like to find some form of query that will total up gallons purchased from the last time that an Odometer Reading (MileageLog) is entered. What I'm looking to do is eventually use this information to calculate miles per gallon on each purchase with all the necessary fields entered (i.e., 10/15 would not have this information but 10/23 would), and not require that an odometer reading necessarily exist for each record. We all make mistakes and forget to enter the value sometimes, so the purpose of this last step is to keep from getting invalid data or errors. In essence, it is basically assuming that the gallons used since the last valid Odometer reading has been steady.

I am comfortable throwing a subquery in here, even a sub-subquery if it needs to be done.

Any help, as always, is greatly appreciated.
 

Attachments

  • tblGasoline.JPG
    tblGasoline.JPG
    55.3 KB · Views: 137
  • qryMileageAndMPG.JPG
    qryMileageAndMPG.JPG
    69.2 KB · Views: 137
  • qrySumOfGallons.JPG
    qrySumOfGallons.JPG
    27 KB · Views: 166

John Big Booty

AWF VIP
Local time
Tomorrow, 08:08
Joined
Aug 29, 2005
Messages
8,263
Use Is Not Null in your criteria to filter out Null values. Alternately create an expression in you query that uses the NZ() function to convert Null values to zero.

Also have a look at a Totals Query.
 

Users who are viewing this thread

Top Bottom