#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Location
    Canada
    Posts
    5
    Rep Power
    0

    Stuck: Comparing values from the same field at Different dates


    Hi, I've looked around on google and this forum for an answer to this but I'm not sure I've been looking for the right things.

    I'm in the process of converting a lot of our excel sheet "databases" into Access DBs. This perticular one is related to our volume of fuel in inventory.

    In order to calculate consumption, I want to do the Volume at Day 2, minus the Volume at Day 1.

    I've tried a few things but I'm falling short on the syntax I should be using. Also tried VBA but I'm short on creativity today it seems.


    Graphically, this is the result i'd want under the calculated "consumption" field
    Date Volume Consumption
    1 100
    2 80 20
    3 30 50


    Thank You in Advance!

    Max
  2. #2
  3. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    Use subquery.

    Review Microsoft Access tips: Subquery basics
    Specifically the example for 'Get the value in another record'
    I like to know if my suggestion helped you. One way to let me know is by clicking star at bottom left corner of post.
    Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm
  4. #3
  5. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    Let's assume:
    1. Your table is normalized.
    2. For the sake of this example we'll call it Fuel. We'll also assume the only two fields we care about are a data field [FuelDate] and the amount of fuel [FuelVolume]
    3. The only days that we currently care about are today and yesterday.
    4. The only thing we're looking for is the difference between the fuel volume today and the fuel volume yesterday.

    This can be done in one query. You could also modify it to look at days other than today, or even longer time spans by using references to textboxes
    in your forms rather than the simple date() function.

    Code:
    SELECT Sum(IIf([FuelDate]=Date(),[FuelVolume]*-1,IIf([FuelDate]=(Date()-1),[FuelVolume],"0"))) AS FuelUsage
    FROM fuel;
  6. #4
  7. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Location
    Canada
    Posts
    5
    Rep Power
    0
    Thank you very much June7 and Meratigoerr.

    Subqueries solves a lot of issues all across the board, and simplifies a lot of the cascading queries I had going. Definitly worth becoming a master at it.

    As for the FuelUsage formula, works wonders. Very logical. I'll program the likes of it as a function.

    The project I signed up for is a bit beyond my beginer-intermediate skill level, but I'm learning!

Similar Threads

  1. Comparing dates
    By ceema in forum .NET Development
    Replies: 2
    Last Post: March 4th, 2006, 04:37 AM
  2. Comparing Array Values to Recordset Values
    By sugarNspice in forum ASP Development
    Replies: 6
    Last Post: October 13th, 2004, 05:45 PM
  3. Comparing two dates
    By juanahusted in forum Microsoft Access Help
    Replies: 0
    Last Post: July 3rd, 2004, 12:01 PM
  4. Comparing Dates in SQL
    By davidagnew in forum SQL Development
    Replies: 3
    Last Post: December 26th, 2003, 08:02 AM
  5. Comparing Dates in ASP
    By Steve Schofield in forum SQL Development
    Replies: 1
    Last Post: May 30th, 2002, 05:22 AM

IMN logo majestic logo threadwatch logo seochat tools logo