Re: How to do MAX-MIN on groups of records?
- From: "John Spencer" <spencer@xxxxxxxxx>
- Date: Fri, 2 Feb 2007 11:53:56 -0500
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" <Phil@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3921833A-7CBD-4997-BCB1-C603A67F2836@xxxxxxxxxxxxxxxx
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" wrote:
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;
.
- Follow-Ups:
- Re: How to do MAX-MIN on groups of records?
- From: Phil
- Re: How to do MAX-MIN on groups of records?
- References:
- Re: How to do MAX-MIN on groups of records?
- From: John Spencer
- Re: How to do MAX-MIN on groups of records?
- From: Phil
- Re: How to do MAX-MIN on groups of records?
- Prev by Date: Re: Criteria IIf
- Next by Date: Re: Make Multiple Tables via Query
- Previous by thread: Re: How to do MAX-MIN on groups of records?
- Next by thread: Re: How to do MAX-MIN on groups of records?
- Index(es):
Relevant Pages
|
