Solved Get previous value of record where... (1 Viewer)

foshizzle

Registered User.
Local time
Today, 08:02
Joined
Nov 27, 2013
Messages
277
As shown in the attached photos, I have a table where a user enters meter readings for each fuel truck after each day. Each truck has two meters.
The user wants to see the difference between the previous day and the current day's readings for each truck.

How would I go about making this happen?
 

Attachments

  • table.jpg
    table.jpg
    30.5 KB · Views: 87
  • reportd.jpg
    reportd.jpg
    98.5 KB · Views: 92
  • reportv.jpg
    reportv.jpg
    39 KB · Views: 85

foshizzle

Registered User.
Local time
Today, 08:02
Joined
Nov 27, 2013
Messages
277
Thanks for your reply. It sort of makes sense. To make it a bit more confusion, I have an inner join which references another table. This is my code so far. It gives me a 'Type mismatch in expression' error. Can you help?

SQL:
SELECT tblMeterReadings.MeterID,
tblMeterReadings.MeterDate,
tblMeterReadings.TruckNumberLookup,
tblMeterReadings.Meter1Reading,
    (SELECT TOP 1 Dupe.Meter1Reading
        FROM tblMeterReadings AS Dupe
        WHERE Dupe.TruckNumberLookup = Meter1Reading.TruckNumberLookup
        AND Dupe.MeterDate < Meter1Reading.MeterDate
        ORDER BY Dupe.ReadDate DESC,Dupe.ID) AS Meter1PriorValue,
tblMeterReadings.Meter1Adjust,
tblMeterReadings.Meter2Reading,
    (SELECT TOP 1 Dupe.Meter2Reading
        FROM tblMeterReadings AS Dupe
        WHERE Dupe.TruckNumberLookup = Meter2Reading.TruckNumberLookup
        AND Dupe.MeterDate < Meter2Reading.MeterDate
        ORDER BY Dupe.ReadDate DESC,Dupe.ID) AS Meter2PriorValue,
tblMeterReadings.Meter2Adjust
FROM tblTrucks INNER JOIN tblMeterReadings ON tblTrucks.TruckNumber = tblMeterReadings.TruckNumberLookup;
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:02
Joined
Aug 30, 2003
Messages
36,118
That error would imply that the data types of fields in the join aren't compatible; what are they? Or can you attach the db here to play with?
 

foshizzle

Registered User.
Local time
Today, 08:02
Joined
Nov 27, 2013
Messages
277
Attached. Thank you.
 

Attachments

  • Database111.accdb
    432 KB · Views: 102

foshizzle

Registered User.
Local time
Today, 08:02
Joined
Nov 27, 2013
Messages
277
I'm aware of that now but I've already got the entire DB built around the way its setup now. I changed the one table so they both read as SHORT TEXT data type. Not sure if that resolves that problem.

I think I'm getting closer. Here is the updated code, but it just shows blank now instead of errors
SQL:
SELECT tblMeterReadings.MeterID,
tblMeterReadings.MeterDate,
tblMeterReadings.TruckNumberLookup,
tblMeterReadings.Meter1Reading,
    (SELECT TOP 1 Dupe.Meter1Reading
        FROM tblMeterReadings AS Dupe
        WHERE Dupe.TruckNumberLookup = tblMeterReadings.TruckNumberLookup
        AND Dupe.MeterDate < tblMeterReadings.MeterDate) AS Meter1PriorValue,
tblMeterReadings.Meter1Adjust,
tblMeterReadings.Meter2Reading,
    (SELECT TOP 1 Dupe.Meter2Reading
        FROM tblMeterReadings AS Dupe
        WHERE Dupe.TruckNumberLookup = tblMeterReadings.TruckNumberLookup
        AND Dupe.MeterDate < tblMeterReadings.MeterDate) AS Meter2PriorValue,
tblMeterReadings.Meter2Adjust
FROM tblTrucks INNER JOIN tblMeterReadings ON tblTrucks.TruckNumber = tblMeterReadings.TruckNumberLookup;
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:02
Joined
Aug 30, 2003
Messages
36,118
See if this works, with your original data types:

SELECT tblMeterReadings.MeterID, tblMeterReadings.TruckNumberLookup, tblMeterReadings.MeterDate, tblMeterReadings.Meter1Reading, (SELECT TOP 1 Dupe.Meter1Reading
FROM tblMeterReadings AS Dupe
WHERE Dupe.TruckNumberLookup = tblMeterReadings.TruckNumberLookup
AND Dupe.MeterDate < tblMeterReadings.MeterDate
ORDER BY Dupe.MeterDate DESC,Dupe.MeterID) AS Meter1PriorValue, tblMeterReadings.Meter1Adjust, tblMeterReadings.Meter2Reading, tblMeterReadings.Meter2Adjust
FROM tblTrucks INNER JOIN tblMeterReadings ON tblTrucks.TruckID = tblMeterReadings.TruckNumberLookup
ORDER BY tblMeterReadings.TruckNumberLookup, tblMeterReadings.MeterDate;
 

foshizzle

Registered User.
Local time
Today, 08:02
Joined
Nov 27, 2013
Messages
277
Thats it! I had to make an adjustment to get the TruckNumber to show up properly but this is the final code with both Meters for each truck Thanks so much for your help!

SQL:
SELECT tblMeterReadings.MeterID, tblMeterReadings.TruckNumberLookup, tblMeterReadings.MeterDate, tblMeterReadings.Meter1Reading, (SELECT TOP 1 Dupe.Meter1Reading
    FROM tblMeterReadings AS Dupe
    WHERE Dupe.TruckNumberLookup = tblMeterReadings.TruckNumberLookup
    AND Dupe.MeterDate < tblMeterReadings.MeterDate
    ORDER BY Dupe.MeterDate DESC,Dupe.MeterID) AS Meter1PriorValue, tblMeterReadings.Meter1Adjust, tblMeterReadings.Meter2Reading, (SELECT TOP 1 Dupe.Meter2Reading
    FROM tblMeterReadings AS Dupe
    WHERE Dupe.TruckNumberLookup = tblMeterReadings.TruckNumberLookup
    AND Dupe.MeterDate < tblMeterReadings.MeterDate
    ORDER BY Dupe.MeterDate DESC,Dupe.MeterID) AS Meter2PriorValue, tblMeterReadings.Meter2Adjust, tblTrucks.TruckNumber
FROM tblTrucks INNER JOIN tblMeterReadings ON tblTrucks.TruckID = tblMeterReadings.TruckNumberLookup
ORDER BY tblMeterReadings.TruckNumberLookup, tblMeterReadings.MeterDate;
 

Users who are viewing this thread

Top Bottom