adding the "HAVING" eliminates the "GROUP BY"

[Dsperry101 via AccessMonster.com]

Hi,
I have a work order database and I'm trying to do a report on the down
time of machines by the amount of time to complete a work order. The
following SQL gets the down time from the whole database:
SELECT tblNewMwo.Equipment, Count(tblNewMwo.Equipment) AS ECount, Sum
(tblNewMwo.flddownmin) AS SumOfflddownmin
FROM tblNewMwo
WHERE (((tblNewMwo.Priority)="Production Stoppage"))
GROUP BY tblNewMwo.Equipment
ORDER BY Count(tblNewMwo.Equipment) DESC;
The primary index is the Work_Order_Number which is a string composed of
the date and time the work order was generated "YYMMDDHHMM"
I want to select a period of time for the report by selecting records by
the Work_Order_Number but when I add that to the SQL it no longer groups the
field . IE ECount shows the number of work orders for each piece of equipment
but when I add the selection by Work_Order_Number ECount is 1 for eack entry.

SELECT tblNewMwo.Equipment, Count(tblNewMwo.Equipment) AS ECount, Sum
(tblNewMwo.flddownmin) AS SumOfflddownmin, tblNewMwo.Work_Order_Number
FROM tblNewMwo
WHERE (((tblNewMwo.Priority)="Production Stoppage"))
GROUP BY tblNewMwo.Equipment, tblNewMwo.Work_Order_Number
HAVING (((tblNewMwo.Work_Order_Number) Like "0802*"))
ORDER BY Count(tblNewMwo.Equipment) DESC;
adding the "HAVING" eliminates the "GROUP BY"

How can I get around this ?

 

[Douglas J. Steele]

Why are you putting that as a HAVING clause? It looks as though it should be
part of the WHERE clause to me.

The HAVING clause should only be used when you're trying to limit the
outcome based on the grouping. For instance, if you only wanted rows where
the count was greater than a certain number.

 

[Dsperry101 via AccessMonster.com]

Doug,
You were right , the HAVING was screwing me up. Thanks!!

Why are you putting that as a HAVING clause? It looks as though it should be
part of the WHERE clause to me.

The HAVING clause should only be used when you're trying to limit the
outcome based on the grouping. For instance, if you only wanted rows where
the count was greater than a certain number.

Hi,
I have a work order database and I'm trying to do a report on the down

[quoted text clipped - 26 lines]

How can I get around this ?