How to do MAX-MIN on groups of records?

G

Guest

The query below selects all equipment between specified dates. Now I need to
get the usage on each machine (by using MAX-MIN on the Usage field). How do I
do this calculation for each machine? (ie. the selection will have multiple
records (depending on the date range) for multiple machines and I need the
MAX-MIN for each individual machine)

SELECT Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Usage.Date
FROM Machines INNER JOIN [Usage] ON Machines.MachineDesignator =
Usage.MachineDesignator
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Usage.Date
HAVING (((Usage.Date) Between [First date of interest (MM/DD/YY)] And [Last
date of interest (MM/DD/YY)]))
ORDER BY Machines.MachineDesignator, Usage.Date;
 
J

John Spencer

Perhaps what you want is


SELECT Machines.MachineDesignator
, Machines.MachineName,
, Machines.MachineType
, Usage.MachineUsage
, Max(MachineUsage) as Biggest
, Min(MachineUsage) as Smallest
, Max(MachineUsage) - Min(MachineUsage) as Used
FROM Machines INNER JOIN [Usage]
ON Machines.MachineDesignator = Usage.MachineDesignator
WHERE (((Usage.Date) Between [First date of interest (MM/DD/YY)] And
[Last date of interest (MM/DD/YY)]))
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage
ORDER BY Machines.MachineDesignator

Note that you cannot include Usage.Date in the Select or Group by clause
or you will end up with one record per machine per day. You could get
the range of dates for each machine to show by using Max(USage.Date) and
Min(Usage.Date) in the Select clause.
 
G

Guest

Thanks John. Your code gives me basically the same results I have, a line per
machine per day with the added colums Biggest and Smallest (they are the same
so when subtracted the Usage is zero). I need some sort of code that compares
MachineDesignator and if it's the same stores the Min and Max and if it's
different stores a diferent Min Max.

Phil

John Spencer said:
Perhaps what you want is


SELECT Machines.MachineDesignator
, Machines.MachineName,
, Machines.MachineType
, Usage.MachineUsage
, Max(MachineUsage) as Biggest
, Min(MachineUsage) as Smallest
, Max(MachineUsage) - Min(MachineUsage) as Used
FROM Machines INNER JOIN [Usage]
ON Machines.MachineDesignator = Usage.MachineDesignator
WHERE (((Usage.Date) Between [First date of interest (MM/DD/YY)] And
[Last date of interest (MM/DD/YY)]))
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage
ORDER BY Machines.MachineDesignator

Note that you cannot include Usage.Date in the Select or Group by clause
or you will end up with one record per machine per day. You could get
the range of dates for each machine to show by using Max(USage.Date) and
Min(Usage.Date) in the Select clause.

---
John Spencer
Access MVP 2001-2005, 2007

The query below selects all equipment between specified dates. Now I need to
get the usage on each machine (by using MAX-MIN on the Usage field). How do I
do this calculation for each machine? (ie. the selection will have multiple
records (depending on the date range) for multiple machines and I need the
MAX-MIN for each individual machine)

SELECT Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Usage.Date
FROM Machines INNER JOIN [Usage] ON Machines.MachineDesignator =
Usage.MachineDesignator
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Usage.Date
HAVING (((Usage.Date) Between [First date of interest (MM/DD/YY)] And [Last
date of interest (MM/DD/YY)]))
ORDER BY Machines.MachineDesignator, Usage.Date;
Click to expand...
Click to expand...
 
J

John Spencer

The code I posted should give you one line per machine for the designated
time period. Did you notice that usage.Date was dropped from the select and
group clauses and that the criteria for usage.date is not in a HAVING
clause, but is in a WHERE clause.

In the query grid, change GROUP BY to WHERE under the field Usage.Date.

If you did do the above, then I obviously don't understand your table
structure or what you are trying to accomplish.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Phil said:
Thanks John. Your code gives me basically the same results I have, a line
per
machine per day with the added colums Biggest and Smallest (they are the
same
so when subtracted the Usage is zero). I need some sort of code that
compares
MachineDesignator and if it's the same stores the Min and Max and if it's
different stores a diferent Min Max.

Phil

John Spencer said:
Perhaps what you want is


SELECT Machines.MachineDesignator
, Machines.MachineName,
, Machines.MachineType
, Usage.MachineUsage
, Max(MachineUsage) as Biggest
, Min(MachineUsage) as Smallest
, Max(MachineUsage) - Min(MachineUsage) as Used
FROM Machines INNER JOIN [Usage]
ON Machines.MachineDesignator = Usage.MachineDesignator
WHERE (((Usage.Date) Between [First date of interest (MM/DD/YY)] And
[Last date of interest (MM/DD/YY)]))
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage
ORDER BY Machines.MachineDesignator

Note that you cannot include Usage.Date in the Select or Group by clause
or you will end up with one record per machine per day. You could get
the range of dates for each machine to show by using Max(USage.Date) and
Min(Usage.Date) in the Select clause.

---
John Spencer
Access MVP 2001-2005, 2007

The query below selects all equipment between specified dates. Now I
need to
get the usage on each machine (by using MAX-MIN on the Usage field).
How do I
do this calculation for each machine? (ie. the selection will have
multiple
records (depending on the date range) for multiple machines and I need
the
MAX-MIN for each individual machine)

SELECT Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Usage.Date
FROM Machines INNER JOIN [Usage] ON Machines.MachineDesignator =
Usage.MachineDesignator
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Usage.Date
HAVING (((Usage.Date) Between [First date of interest (MM/DD/YY)] And
[Last
date of interest (MM/DD/YY)]))
ORDER BY Machines.MachineDesignator, Usage.Date;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

This is what I did:

SELECT Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Max(MachineUsage) as Biggest,
Min(MachineUsage) as Smallest, Max(MachineUsage) - Min(MachineUsage) as Used
FROM Machines INNER JOIN [Usage] ON Machines.MachineDesignator =
Usage.MachineDesignator
WHERE (((Usage.Date) Between [First date of interest (MM/DD/YY)] And [Last
date of interest (MM/DD/YY)]))
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage
ORDER BY Machines.MachineDesignator;

and this is what I got:

CL03 EPL 710 Lathe 400 400 400 0
CL03 EPL 710 Lathe 456 456 456 0
CL03 EPL 710 Lathe 459 459 459 0
CL03 EPL 710 Lathe 473 473 473 0
CL03 EPL 710 Lathe 496 496 496 0
CL18 SL2 CL 18 Lathe 12 12 12 0
CL30 WIELER50-CL30 Lathe 345 345 345 0
CLM1 NT4250 Mill Turn 20 20 20 0
CM03 OKUMA CM3 Mill 100 100 100 0
CM03 OKUMA CM3 Mill 115 115 115 0
CM03 OKUMA CM3 Mill 127 127 127 0
CM03 OKUMA CM3 Mill 130 130 130 0
CM03 OKUMA CM3 Mill 137 137 137 0

Thanks.

Phil

John Spencer said:
The code I posted should give you one line per machine for the designated
time period. Did you notice that usage.Date was dropped from the select and
group clauses and that the criteria for usage.date is not in a HAVING
clause, but is in a WHERE clause.

In the query grid, change GROUP BY to WHERE under the field Usage.Date.

If you did do the above, then I obviously don't understand your table
structure or what you are trying to accomplish.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Phil said:
Thanks John. Your code gives me basically the same results I have, a line
per
machine per day with the added colums Biggest and Smallest (they are the
same
so when subtracted the Usage is zero). I need some sort of code that
compares
MachineDesignator and if it's the same stores the Min and Max and if it's
different stores a diferent Min Max.

Phil

John Spencer said:
Perhaps what you want is


SELECT Machines.MachineDesignator
, Machines.MachineName,
, Machines.MachineType
, Usage.MachineUsage
, Max(MachineUsage) as Biggest
, Min(MachineUsage) as Smallest
, Max(MachineUsage) - Min(MachineUsage) as Used
FROM Machines INNER JOIN [Usage]
ON Machines.MachineDesignator = Usage.MachineDesignator
WHERE (((Usage.Date) Between [First date of interest (MM/DD/YY)] And
[Last date of interest (MM/DD/YY)]))
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage
ORDER BY Machines.MachineDesignator

Note that you cannot include Usage.Date in the Select or Group by clause
or you will end up with one record per machine per day. You could get
the range of dates for each machine to show by using Max(USage.Date) and
Min(Usage.Date) in the Select clause.

---
John Spencer
Access MVP 2001-2005, 2007


Phil wrote:
The query below selects all equipment between specified dates. Now I
need to
get the usage on each machine (by using MAX-MIN on the Usage field).
How do I
do this calculation for each machine? (ie. the selection will have
multiple
records (depending on the date range) for multiple machines and I need
the
MAX-MIN for each individual machine)

SELECT Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Usage.Date
FROM Machines INNER JOIN [Usage] ON Machines.MachineDesignator =
Usage.MachineDesignator
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Usage.Date
HAVING (((Usage.Date) Between [First date of interest (MM/DD/YY)] And
[Last
date of interest (MM/DD/YY)]))
ORDER BY Machines.MachineDesignator, Usage.Date;
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

AHHHH!!! Sound of my head thumping against the wall. Ouch. Stop. That
feels better.

You also need to drop MachineUsage. That is different every day, so you are
going to get one record returned for each time the value is unique and since
it changes each day for each machine that is in use...

So, now I think what you want is

SELECT Machines.MachineDesignator
, Machines.MachineName
, Machines.MachineType
, Max(MachineUsage) as Biggest
, Min(MachineUsage) as Smallest
, Max(MachineUsage) - Min(MachineUsage) as Used
FROM Machines INNER JOIN [Usage] ON
Machines.MachineDesignator = Usage.MachineDesignator
WHERE (((Usage.Date) Between [First date of interest (MM/DD/YY)]
And [Last date of interest (MM/DD/YY)]))
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType
ORDER BY Machines.MachineDesignator;

As I observed before, if you want the actual first and last day of usage
during the period, you could add that in by using Max(Usage.Date) and
Min(Usage.Date).
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Phil said:
This is what I did:

SELECT Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Max(MachineUsage) as Biggest,
Min(MachineUsage) as Smallest, Max(MachineUsage) - Min(MachineUsage) as
Used
FROM Machines INNER JOIN [Usage] ON Machines.MachineDesignator =
Usage.MachineDesignator
WHERE (((Usage.Date) Between [First date of interest (MM/DD/YY)] And [Last
date of interest (MM/DD/YY)]))
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage
ORDER BY Machines.MachineDesignator;

and this is what I got:

CL03 EPL 710 Lathe 400 400 400 0
CL03 EPL 710 Lathe 456 456 456 0
CL03 EPL 710 Lathe 459 459 459 0
CL03 EPL 710 Lathe 473 473 473 0
CL03 EPL 710 Lathe 496 496 496 0
CL18 SL2 CL 18 Lathe 12 12 12 0
CL30 WIELER50-CL30 Lathe 345 345 345 0
CLM1 NT4250 Mill Turn 20 20 20 0
CM03 OKUMA CM3 Mill 100 100 100 0
CM03 OKUMA CM3 Mill 115 115 115 0
CM03 OKUMA CM3 Mill 127 127 127 0
CM03 OKUMA CM3 Mill 130 130 130 0
CM03 OKUMA CM3 Mill 137 137 137 0

Thanks.

Phil

John Spencer said:
The code I posted should give you one line per machine for the designated
time period. Did you notice that usage.Date was dropped from the select
and
group clauses and that the criteria for usage.date is not in a HAVING
clause, but is in a WHERE clause.

In the query grid, change GROUP BY to WHERE under the field Usage.Date.

If you did do the above, then I obviously don't understand your table
structure or what you are trying to accomplish.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Phil said:
Thanks John. Your code gives me basically the same results I have, a
line
per
machine per day with the added colums Biggest and Smallest (they are
the
same
so when subtracted the Usage is zero). I need some sort of code that
compares
MachineDesignator and if it's the same stores the Min and Max and if
it's
different stores a diferent Min Max.

Phil

:

Perhaps what you want is


SELECT Machines.MachineDesignator
, Machines.MachineName,
, Machines.MachineType
, Usage.MachineUsage
, Max(MachineUsage) as Biggest
, Min(MachineUsage) as Smallest
, Max(MachineUsage) - Min(MachineUsage) as Used
FROM Machines INNER JOIN [Usage]
ON Machines.MachineDesignator = Usage.MachineDesignator
WHERE (((Usage.Date) Between [First date of interest (MM/DD/YY)] And
[Last date of interest (MM/DD/YY)]))
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage
ORDER BY Machines.MachineDesignator

Note that you cannot include Usage.Date in the Select or Group by
clause
or you will end up with one record per machine per day. You could get
the range of dates for each machine to show by using Max(USage.Date)
and
Min(Usage.Date) in the Select clause.

---
John Spencer
Access MVP 2001-2005, 2007


Phil wrote:
The query below selects all equipment between specified dates. Now
I
need to
get the usage on each machine (by using MAX-MIN on the Usage field).
How do I
do this calculation for each machine? (ie. the selection will have
multiple
records (depending on the date range) for multiple machines and I
need
the
MAX-MIN for each individual machine)

SELECT Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Usage.Date
FROM Machines INNER JOIN [Usage] ON Machines.MachineDesignator =
Usage.MachineDesignator
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Usage.Date
HAVING (((Usage.Date) Between [First date of interest (MM/DD/YY)]
And
[Last
date of interest (MM/DD/YY)]))
ORDER BY Machines.MachineDesignator, Usage.Date;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Bingo! You got it. Now I'll study the code and figure out why (I'm a newbie).
Your comment seems to indicate that all fields need to be common for them to
be grouped (or something like that).

Appreciate it John.

Thanks.

John Spencer said:
AHHHH!!! Sound of my head thumping against the wall. Ouch. Stop. That
feels better.

You also need to drop MachineUsage. That is different every day, so you are
going to get one record returned for each time the value is unique and since
it changes each day for each machine that is in use...

So, now I think what you want is

SELECT Machines.MachineDesignator
, Machines.MachineName
, Machines.MachineType
, Max(MachineUsage) as Biggest
, Min(MachineUsage) as Smallest
, Max(MachineUsage) - Min(MachineUsage) as Used
FROM Machines INNER JOIN [Usage] ON
Machines.MachineDesignator = Usage.MachineDesignator
WHERE (((Usage.Date) Between [First date of interest (MM/DD/YY)]
And [Last date of interest (MM/DD/YY)]))
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType
ORDER BY Machines.MachineDesignator;

As I observed before, if you want the actual first and last day of usage
during the period, you could add that in by using Max(Usage.Date) and
Min(Usage.Date).
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Phil said:
This is what I did:

SELECT Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Max(MachineUsage) as Biggest,
Min(MachineUsage) as Smallest, Max(MachineUsage) - Min(MachineUsage) as
Used
FROM Machines INNER JOIN [Usage] ON Machines.MachineDesignator =
Usage.MachineDesignator
WHERE (((Usage.Date) Between [First date of interest (MM/DD/YY)] And [Last
date of interest (MM/DD/YY)]))
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage
ORDER BY Machines.MachineDesignator;

and this is what I got:

CL03 EPL 710 Lathe 400 400 400 0
CL03 EPL 710 Lathe 456 456 456 0
CL03 EPL 710 Lathe 459 459 459 0
CL03 EPL 710 Lathe 473 473 473 0
CL03 EPL 710 Lathe 496 496 496 0
CL18 SL2 CL 18 Lathe 12 12 12 0
CL30 WIELER50-CL30 Lathe 345 345 345 0
CLM1 NT4250 Mill Turn 20 20 20 0
CM03 OKUMA CM3 Mill 100 100 100 0
CM03 OKUMA CM3 Mill 115 115 115 0
CM03 OKUMA CM3 Mill 127 127 127 0
CM03 OKUMA CM3 Mill 130 130 130 0
CM03 OKUMA CM3 Mill 137 137 137 0

Thanks.

Phil

John Spencer said:
The code I posted should give you one line per machine for the designated
time period. Did you notice that usage.Date was dropped from the select
and
group clauses and that the criteria for usage.date is not in a HAVING
clause, but is in a WHERE clause.

In the query grid, change GROUP BY to WHERE under the field Usage.Date.

If you did do the above, then I obviously don't understand your table
structure or what you are trying to accomplish.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks John. Your code gives me basically the same results I have, a
line
per
machine per day with the added colums Biggest and Smallest (they are
the
same
so when subtracted the Usage is zero). I need some sort of code that
compares
MachineDesignator and if it's the same stores the Min and Max and if
it's
different stores a diferent Min Max.

Phil

:

Perhaps what you want is


SELECT Machines.MachineDesignator
, Machines.MachineName,
, Machines.MachineType
, Usage.MachineUsage
, Max(MachineUsage) as Biggest
, Min(MachineUsage) as Smallest
, Max(MachineUsage) - Min(MachineUsage) as Used
FROM Machines INNER JOIN [Usage]
ON Machines.MachineDesignator = Usage.MachineDesignator
WHERE (((Usage.Date) Between [First date of interest (MM/DD/YY)] And
[Last date of interest (MM/DD/YY)]))
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage
ORDER BY Machines.MachineDesignator

Note that you cannot include Usage.Date in the Select or Group by
clause
or you will end up with one record per machine per day. You could get
the range of dates for each machine to show by using Max(USage.Date)
and
Min(Usage.Date) in the Select clause.

---
John Spencer
Access MVP 2001-2005, 2007


Phil wrote:
The query below selects all equipment between specified dates. Now
I
need to
get the usage on each machine (by using MAX-MIN on the Usage field).
How do I
do this calculation for each machine? (ie. the selection will have
multiple
records (depending on the date range) for multiple machines and I
need
the
MAX-MIN for each individual machine)

SELECT Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Usage.Date
FROM Machines INNER JOIN [Usage] ON Machines.MachineDesignator =
Usage.MachineDesignator
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Usage.Date
HAVING (((Usage.Date) Between [First date of interest (MM/DD/YY)]
And
[Last
date of interest (MM/DD/YY)]))
ORDER BY Machines.MachineDesignator, Usage.Date;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Missing Expression 2
Displaying timestamps corresponding to min / max values 1
Display Counts of records per day 3
Query to select individual records 4
multiple records from one 1
Odd result with Min of Date Range 3
Sums Way to big 7
Max of Records Date in Text Format Problem 4

Top