Results 1 to 11 of 11
  1. #1
    Join Date
    May 2007
    Posts
    13

    Unanswered: Return value to form that meets multiple criteria

    Hello All,

    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.


    Thank you so much for your help!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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
    FROM FuelPurchases;

    You'd have to modify as it sounds like you have multiple lift stations stored in that table, but that's a simple tweak.
    Paul

  3. #3
    Join Date
    May 2007
    Posts
    13
    Paul,

    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.

    (ie. 6/1/2007 PBLiftStation 1211
    6/2/2007 GGLiftStation 1001
    6/3/2007 PBLiftStation 1218
    5/1/2006 GGLiftStation 840 )

    A user enters a new record: 5/4/2006 GGLiftStation 900

    The query would match the LiftStation Names and return the value closest to the entry date (840)


    Thank you for your help,

    Daniel

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  5. #5
    Join Date
    May 2007
    Posts
    13
    Paul,

    Thank you again for the quick reply. I can get the greatest value for the Lift Station, but I can't get the value of the closest dated record.

    The formula that I am using in a Query is:

    Previous1Counter: (Select Max (Pump1Reading) From UserInput where [UserInput]![Lift Station] = [LiftStationPumpRates]![LiftName])

    What do I need to add, or maybe I am going about it wrong? I couldn't follow the logic of the example you provided.

    Also, what is your suggested method to prevent roll over (In our case a new pump may replace an existing one, resulting in a PumpReading resetting to 0)?

    Thanks for the help,

    Daniel

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can you post a sample db? It would be easier.
    Paul

  7. #7
    Join Date
    May 2007
    Posts
    13
    Here is the monstrosity: LiftStationReport.zip


    Thanks,

    Daniel

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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
    FROM UserInput;

    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).
    Paul

  9. #9
    Join Date
    May 2007
    Posts
    13
    Paul,

    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.

    Have a great day,

    Daniel

  10. #10
    Join Date
    May 2007
    Posts
    13
    Paul,

    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]))

    Thank you again for your help,

    Daniel

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Wrap the whole thing in an nz() function.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •