Results 1 to 7 of 7

Thread: Query

  1. #1
    Join Date
    Mar 2004
    Posts
    128

    Unanswered: Query

    Hi all,
    if I have the following table and data:

    Begin_Date End_Date S1 S2 SUM
    03/12/2004 6:00:10 03/12/2004 10:00:10 0 500 500 03/12/2004 10:00:10 03/12/2004 12:00:10 500 1000 500
    03/12/2004 6:00:10 03/12/2004 12:00:10 0 1000 1000

    How can I tell Access to take the SUM of the minimum S1 and the Maximum S2,In my example the Third line.

    Best regards...

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by wasimf
    Hi all,
    if I have the following table and data:

    Begin_Date End_Date S1 S2 SUM
    03/12/2004 6:00:10 03/12/2004 10:00:10 0 500 500 03/12/2004 10:00:10 03/12/2004 12:00:10 500 1000 500
    03/12/2004 6:00:10 03/12/2004 12:00:10 0 1000 1000

    How can I tell Access to take the SUM of the minimum S1 and the Maximum S2,In my example the Third line.

    Best regards...
    For starters, put your tables in [ CODE]stuff[/ CODE], makes it far easier to read:
    Code:
    Begin_Date         End_Date                     S1        S2    SUM
    03/12/2004 6:00:10     03/12/2004 10:00:10      0        500     500
    03/12/2004 10:00:10    03/12/2004 12:00:10      500      1000    500        
    03/12/2004 6:00:10     03/12/2004 12:00:10      0        1000    1000
    Where is this information going exactly? Your best bet may be to use domain functions to pull your min/max values.
    Last edited by Teddy; 12-07-04 at 13:14.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2004
    Posts
    128

    Query

    This data is for machines . the S1 and S2 are two counters of the MAchine.
    I have also amchine number . I have more than one line of data about S1 and S2. My target to calculate the maximum difference between S1 and S2.

    So, as you look I have more than one line of the difference, I want to take the maximum difference.

    Please help me to find the proper statment.
    Thanks...

  4. #4
    Join Date
    Mar 2004
    Posts
    128

    Query

    What do you mean by Domain functions to get the (max,min) values from a coloumn in Access.
    Thanks...

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ooops - duplicate cos the site is sooooooo sloooooooooooooowwwwwwwwww
    Last edited by izyrider; 12-07-04 at 13:52.
    currently using SS 2008R2

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    check out Dmax() Dmin() in help.

    it goes something like this:
    myVariable = Dmax("fieldName", "tableName", "WHEREclauseWithoutTheWordWHERE")

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Give this a shot:

    SELECT MachineActivity.MachineID, Min(MachineActivity.S1) AS S1Min, Max(MachineActivity.S2) AS S2Max, Max([s2])-Min([s1]) AS Change
    FROM MachineActivity
    GROUP BY MachineActivity.MachineID;


    This is based upon your table being named 'MachineActivity' and a field existing to identify the machine that the counter is for, which in this case I have called 'MachineID'.

    If you also wanted to show the related date for the counter readings, you could use this:

    SELECT MachineActivity.MachineID, Min(MachineActivity.Begin_Date) AS MinOfBegin_Date, Max(MachineActivity.End_Date) AS MaxOfEnd_Date, Min(MachineActivity.S1) AS S1Min, Max(MachineActivity.S2) AS S2Max, Max([s2])-Min([s1]) AS Change
    FROM MachineActivity
    GROUP BY MachineActivity.MachineID;


    You could even add parameters to select a range of Beginning and Ending Dates.

    TD

Posting Permissions

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