1. Registered User
Join Date
Mar 2004
Posts
128

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. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
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.

3. Registered User
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.

Thanks...

4. Registered User
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. Cavalier King Charles
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.

6. Cavalier King Charles
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

7. Registered User
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
•