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;
Mileage | PreviousMileage | Diff |
---|---|---|
200 | | |
600 | 200 | 400 |
800 | 600 | 200 |
900 | 800 | 100 |
1100 | 900 | 200 |
This is quite simple with a report because reports are made sequentially. So, if the query gets too slow, you can use this method.I'd like to calculate the mileage for each refill.
Hi MajPtblData tblData
ID Mileage 1 200 2 600 3 800 4 900 5 1100Code: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
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;
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.
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"
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;
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;
Hi there.you create a Query from your FuelConsumption table that will Calculate the Mileage:
Code: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;
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!original request:
current request:
So why have we been wasting our time trying to help the OP calculated the mileage at each refil??????????????
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.We've all been learners at some point right!