Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Location
    Midland, Michigan, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query calculation (2000/XP)

    Hello,
    I am doing a database for a small gas station. They take pump readings (North and South - 2 pumps), add the two numbers together to get the 'current' reading.
    I need to be able to go back one record recalculate this number (sum), then subtract this sum from the current record's sum. This gives the total gallons sold for the day.
    How do I 'go back' and get this calculated sum. I am currently working in a query, to generate a report using these numbers.
    I have attached a copy of the database (Winzip compressed). The Daily BVO Report is what I'm referring to. The 'Less Previous Reading' line is what I need.
    Also, any hints/pointers as to optimizing the database - tables, etc. is greatly appreciated.
    Please help,
    Bart
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query calculation (2000/XP)

    Welcome to Woody's Lounge!

    You don't have an AutoNumber field or something like that; that would make it easier to determine which is the previous record. You could create a new query, say BVO_Plus, based on the existing BVO query, that calculates the next highest TotalPrem value:

    SELECT [BVO Query].*, DMax("TotalPrem","BVO Query","TotalPrem < " & [TotalPrem]) AS PrevPrem
    FROM [BVO Query];

    You can change the record source of the report from BVO Query to BVO_Plus, and the control source of the PrevPrem text box to PrevPrem.

    However, this will not necessarily return the value of TotalPrem in the previous record. Mor specifically, it won't if no Premium has been sold in between - it will go back until it finds a lower value for TotalPrem and return that.

    <img src=/w3timages/blueline.gif width=33% height=2>

    General tips:
    <UL><LI>Turn off the Track Name AutoCorrect options in the General tab of Tools | Options... This holds for all your databases; this option has a negative impact on performance and will almost certainly cause you grief.
    <LI>Set the Subdatasheet Name property of all tables to [None]. This holds for all databases too; subdatasheets have a negative impact on performance.[/list]<img src=/w3timages/blueline.gif width=33% height=2>

  3. #3
    New Lounger
    Join Date
    Apr 2004
    Location
    Midland, Michigan, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query calculation (2000/XP)

    Thanks Hans,

    If I add the AutoNumber field, how would I then pull the previous record?
    I really appreciate your time!
    Thanks again,
    Bart

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query calculation (2000/XP)

    If you add an AutoNumber field afterwards, you cannot be sure that it puts the records in the right sort order. but if that works out OK, you could use this expression for PrevPrem:

    DMax("TotalPrem","BVO Query","ID < " & [ID])

    where ID is the name of the AutoNumber field. This would work even if the value of TotalPrem in the previous record is equal to the current value, i.e. if no Premium has been sold since the previous reading.

  5. #5
    New Lounger
    Join Date
    Apr 2004
    Location
    Midland, Michigan, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query calculation (2000/XP)

    Hans,
    I've put DMax in to the report and I am getting an error. I did add the AutoNumber field to the Gas table, and added it to the BVO Query.
    Could you perhaps put into the database and send it to me?
    putnam@toast.net
    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query calculation (2000/XP)

    I have attached the modified database (zipped.)
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    Apr 2004
    Location
    Midland, Michigan, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query calculation (2000/XP)

    I REALLY appreciate your time Hans - many thanks.
    I did play around with your DMAX and I think I got it to work, but I'll look at the database you attached and verify it.
    Again thanks
    Bart

Posting Permissions

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