Unanswered: Return value to form that meets multiple criteria
I have a form that needs to return a value from the previous record that meets certain criteria. The field on the form would display the last record's PumpCounter value for the Lift Station that was selected. (A user enters the Date of the record occurance then selects the Lift Station that it applies to. The PreviousPumpCounter box needs to populate with the value of that Lift Station's last record where the date is the most recent). The user would then enter the current PumpCounter value (The CurrentPumpCounter - previousPumpCounter = TimeofPumpOperation). Of course the CurrentPumpCounter then becomes the previousPumpCounter for the next record for that Lift Station.
I'm lost and don't know how to set up a mulit-criteria query.
You're not storing the previous reading again are you? Generally, you shouldn't. If you went back and edited any reading, you don't want to worry about finding where it may be stored as a previous reading as well. I'd just enter the date and reading; the previous and difference can be calculated easily from that. Here's a little gas mileage query that may get you going in the right direction:
SELECT FuelPurchases.Mileage, FuelPurchases.gallons, FuelPurchases.FillDate, (SELECT Max(Mileage) FROM FuelPurchases AS Alias WHERE Alias.Mileage < FuelPurchases.Mileage) AS PrevMileage, [Mileage]-[PrevMileage] AS Miles, [Miles]/[gallons] AS GasMileage
You'd have to modify as it sounds like you have multiple lift stations stored in that table, but that's a simple tweak.
Thanks for the reply. This may be a simple tweak to most, but I'm not sure what goes where and how.
A user may be entering data that is months or years old, and I can't just return the largest value for that Lift Station that the query finds. I need the query to return a value that is associated with the selected Lift Station that is closest to the Date entered.
Well, if you look at the criteria within the subquery, it's basically saying "give me the highest mileage where the date is less than the date of the record I'm working on". The tweak would be to add to that: "and the lift station is the same as the one I'm working on". Do you see how to do that?
By the way, I'd recommend a little different method if the value "rolls over". IOW if at 9999 your value rolls back around to 0001, this method would not return the correct value after the rollover.
I can't really test because you left out the table of lift stations, so they don't populate this table, but try this clunker:
SELECT UserInput.Date, UserInput.[Lift Station], UserInput.Pump1Reading, UserInput.Pump2Reading, (SELECT Pump1Reading FROM UserInput AS Alias WHERE Alias.[Lift Station] = UserInput.[Lift Station] AND Alias.[Date] = (SELECT Max([Date]) FROM UserInput AS Alias2 WHERE Alias2.[Lift Station] = UserInput.[Lift Station] AND Alias2.[Date] < UserInput.[Date] )) AS PrevPump1, (SELECT Pump2Reading FROM UserInput AS Alias WHERE Alias.[Lift Station] = UserInput.[Lift Station] AND Alias.[Date] = (SELECT Max([Date]) FROM UserInput AS Alias2 WHERE Alias2.[Lift Station] = UserInput.[Lift Station] AND Alias2.[Date] < UserInput.[Date] )) AS PrevPump2
BTW, the spaces in your names will cause you grief, and Date should not be used as a field name, as it's a reserved word (because there is a Date function).
I want to thank you very much for your help and the time you put into answering my question. The query you provided worked great (after realizing that one could change the query from the grid view to the SQL view, it made a lot more sense. The things you run across when you start hitting buttons...amazing!)
It's a nice thing that people who know what they are doing are willing to share their expertise.
Sorry to bother you again. If the query below returns a value of Null, how do I make that Null value = 0 ?
PrevPump1: (Select Pump1Reading FROM InputRecordsTable AS Alias WHERE Alias.[LiftStationName] = InputRecordsTable.[LiftStationName] AND Alias.[InputDate]=(Select MAX([InputDate]) FROM InputRecordsTable AS Alias2 WHERE Alias2.[LiftStationName] = InputRecordsTable.[LiftStationName] AND Alias2.[InputDate] < InputRecordsTable.[InputDate]))