Calculate the difference between two figures (1 Viewer)

[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​

 

[Pat Hartman]

I'd like to calculate the mileage for each refill.

This is quite simple with a report because reports are made sequentially. So, if the query gets too slow, you can use this method.

Create a public variable in the report's header to save prevMileage. And a second one for prevprevMileage.
In the Format Event of the detail section, subtract prevMileage from Me.CurrentMileage and place the result in a control in the detail section.
Then store the prevMileage in prevprevMileage and store the current record's mileage in prevMileage.
Now we come to why "prevprev". The detail section runs BEFORE Access knows if it has room to print it on the current page. If it doesn't fit, Access has to run its code to print page footers and page headers for the next page. Then it reruns the format event. The problem is that the prevMileage has the value for the current record rather than the previous record. SO, in the Retreat event, move the prevprevMileage to prevMileage so that the Format event will be working with the correct values.

There are other methods to handle this. If you don't like this one, I'll suggest another.

Do NOT store the calculated Mileage value. That violates normal forms and imposes a specific sequence on table rows which might not exist.

 

[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;

 

[Pat Hartman]

original request:

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.

current request:

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"

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

 

[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.

 

[Pat Hartman]

We've all been learners at some point right!

Yes we have. However, it is really important that you present what you are asking for correctly. You responded to my annoyance but you didn't clarify so we still have no idea what your request actually is. Your original post was quite specific and identified a business problem you were trying to solve. So, that is what everyone was working on. We've all solved that particular problem and it has several solutions and the most efficient one is to use a report. Of course other solutions were suggested to use a query. That's fine but slower. I'm not sure if you ever tested them. Then you changed the requirement entirely. The answer should now have become, just use a simple query or report. No special technique is necessary. That led us to the syntax error. If you build your query with the QBE, you are much less likely to create syntax errors. You can later copy and paste the SQL to embed it in your VBA. Why? who knows, but, if you like messing with code when you don't need to, OK. At least you never asked to store the calculated mileage because we would have had to talk you down off that ledge.

It is hard for novices because they frequently come to us with "How can I make this work" but they've only explained the technical problem they're having and not the business problem. A large part of the time, they, in their inexperience chose a "solution" that wasn't actually ever going to solve the problem and so, many will spin their wheels trying to solve a meaningless technical problem and then 30 posts in, the business problem materializes and we divert to resolving that with a totally different "solution" that the novice never had enough experience to even envision. So, you started with a good question. Unfortunately you didn't know the business requirements. It happens.

And finally - Date is a reserved word. It is the name of a function and will cause confusion in VBA and SQL unless you are extremely careful. You might be getting Date() when you want FillDate or vice versa. When you created the table, Access gave you a warning message to NOT use that word as a column name. You blew by the warning. MS wasn't kidding and you may pay for ignoring the advice. While we are on the subject of naming, do not use embedded spaces or special characters and if Access warns you to not use a specific word. Do NOT ignore the warning. "Name" is especially deadly.

 

[CJ_London]

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

 

[Pat Hartman]

There are hundreds of reserved words once you consider Access, VBA, and SQL. The key to avoiding them is that they are all "simple". So to avoid them, you should lean toward compound words such as SaleDate, FirstName, TaskDesc, BillingMonth, etc.

 

[MajP]

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

docs.microsoft.com