Calculate the difference between two figures

[Pdqkwaker]

Hi there.

I have the milage of my car accumulating in one column each time I fill with fuel in a table. I'd like to calculate the mileage for each refill.

Thanks in advance

 

[oleronesoftwares]

Create a field for begin mileage( a number field)
create a field for fuel bought/refill(this can be a number field)

thats all.

So each time there is a refill
you will enter the milleage as at the time of refill(i.e before refill)
you will enter the fuel refilled in litres

You can then use this fields to run reports

 

[pbaldy]

This help?

Microsoft Access tips: Subquery basics

Using subqueries in Microsoft Access. What subqueries are, and examples of using subqueries.

allenbrowne.com

 

[CJ_London]

or you can use the dmax function

SELECT *, mileage-dmax("mileage","myTable","Mileage<" & mileage) AS mileageSinceLastRefill
FROM myTable

 

[MajP]

tblData tblData

ID	Mileage
1​	200​
2​	600​
3​	800​
4​	900​
5​	1100​

SELECT tblData.Mileage,

  (SELECT top 1 Mileage
   FROM tblData AS A
   WHERE A.mileage < tblData.mileage
   ORDER BY Mileage DESC) AS PreviousMileage,
       [mileage]-[previousMileage] AS Diff
FROM tblData;

Query1 Query1

Mileage	PreviousMileage	Diff
200​	​	​
600​	200​	400​
800​	600​	200​
900​	800​	100​
1100​	900​	200​

 

[Pdqkwaker]

tblData tblData

ID	Mileage
1​	200​
2​	600​
3​	800​
4​	900​
5​	1100​

SELECT tblData.Mileage,

  (SELECT top 1 Mileage
   FROM tblData AS A
   WHERE A.mileage < tblData.mileage
   ORDER BY Mileage DESC) AS PreviousMileage,
       [mileage]-[previousMileage] AS Diff
FROM tblData;

Query1 Query1

Mileage	PreviousMileage	Diff
200​	​	​
600​	200​	400​
800​	600​	200​
900​	800​	100​
1100​	900​	200​

Hi MajP

I'm trying not to have a "start" mileage and a "finish" mileage. I'd like to just have the one column. Here's the report I've started and would like a column with "mileage"

 

[arnelgp]

you create a Query from your FuelConsumption table that will Calculate the Mileage:

Select CarID, Car.CarName, ID, [Date], [Odometer],
[ODometer] - Nz(DMax("Odometer", "FuelConsumptionTable", "CarID = " & [CarID] &
" And [ID] < " & [ID]), [ODometer]) As Mileage,
Litres, [Cost Per Ltr], Litres*[Cost Per Ltr] As TotalCost
From FuelConsumptionTable Inner Join Car On FuelConsumption.CarID = Car.CarID;

 

[MajP]

If you do not want the previous mileage shown then you can take it out and do it all in one big step.

SELECT tblData.Mileage,

(SELECT top 1 Mileage
FROM tblData AS A
WHERE A.mileage < tblData.mileage
ORDER BY Mileage DESC) AS PreviousMileage,
[mileage]-[previousMileage] AS Diff
FROM tblData;

change to something like

SELECT 
  tblData.Mileage,
  tbldata.mileage -
     (SELECT top 1 Mileage
       FROM tblData AS A
       WHERE A.mileage < tblData.mileage
        ORDER BY Mileage DESC) AS MilesDrive,
FROM tblData
ORDER BY Mileage;

However, if this was going to be the query for the report it would not matter. Just have a control for the difference and not one for the previous mileage.

 

[Pdqkwaker]

you create a Query from your FuelConsumption table that will Calculate the Mileage:

Select CarID, Car.CarName, ID, [Date], [Odometer],
[ODometer] - Nz(DMax("Odometer", "FuelConsumptionTable", "CarID = " & [CarID] &
" And [ID] < " & [ID]), [ODometer]) As Mileage,
Litres, [Cost Per Ltr], Litres*[Cost Per Ltr] As TotalCost
From FuelConsumptionTable Inner Join Car On FuelConsumption.CarID = Car.CarID;

Hi there.

I've tried using the code but get this error. What might I be doing wrong?

 

[Pdqkwaker]

original request:




current request:


So why have we been wasting our time trying to help the OP calculated the mileage at each refil??????????????

Sorry for any confusion but like I said I'm very much the novice and trying to fumble my way through. We've all been learners at some point right!

 

[pbaldy]

Note different table names

From FuelConsumptionTable Inner Join Car On FuelConsumption.CarID = Car.CarID

Make both match your actual table name.

 

[CJ_London]

other common reserved words are desc, description and time. Another naming blunder is starting table and field names with a number

 

[MajP]

Avoid using reserved words and symbols in Access - Microsoft 365 Apps

Lists the reserved words that you shouldn't use in field, object, and variable names.

docs.microsoft.com