Join with a Having clause -- having problems too

Could someone please help me with a query that I am trying to create or suggest a better way?

What I am trying to do is is sum the production information (tbl_ProductionInfo) that is greater than the last date a particular task was done (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) and the production has met the quanity ran (Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)).

When I put this critera (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) in the Where clause I get an error "An aggregate may not appear in a Where unless it is in a subquery contained in a Having...etc."

SELECT DISTINCT tbl_ProductionInfo.LineNum, tbl_ProductionInfo.Dept, tbl_ProductionInfo.EquipType, Sum(tbl_ProductionInfo.Production) AS SumOfProduction, tbl_Mertering.PMType

FROM tbl_ProductionInfo LEFT JOIN tbl_Mertering ON tbl_ProductionInfo.EquipType = tbl_Mertering.EquipType

WHERE tbl_Mertering.DateOfChange>=tbl_ProductionInfo.EntryDate AND tbl_Mertering.UD2=0 AND tbl_ProductionInfo.LineNum= tbl_Mertering.LineNum AND tbl_ProductionInfo.EquipType= tbl_Mertering.EquipType

GROUP BY tbl_ProductionInfo.LineNum, tbl_ProductionInfo.Dept, tbl_ProductionInfo.EquipType, tbl_Mertering.PMType

HAVING Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)Can you post the DDL for the tables?|||Sorry for the late reply as I have been in meetings all afternoon and please excuse my ingorance, but I am not sure what the DDL is.|||DDL: Data Definition Language.

The SQL Statements that can be used to create the tables and objects involved in your problem, or at least the relevant parts.

Brett is asking for more information on your table design.

blindman|||Here is a little more history.
The production table already existed. I am attempting to create a "meterting" scheduler for our home grown CMMS. We want to create workorders (table that already exists) based on the amount of production that has occurred. I created the metering table to holds the date that work is done. I want to keep each record for historical data.

The flow of the program is
User completes the existing work order; this action creates a record in the metering table; DateOfChange is populated UD2 defaults to 0.

Each day the job runs that looks for Metering records with UD2 = 0 and sums the production for that line, equiptype, dept, ItemDesc. If the sum is greater >= LifeCycle then I write a new work order and change the UD2 =1.

I run this job as an active X (because I am weak in SQL). The writing of the work order and the changing of UD2 works fine. I just can't get the sum right because it is not picking up the >= Max(DateOfChange).

Here is the table structure.

tbl_ProductionInfo Columns
Name Type Size
ProdID int (autonumber) 4
EntryDate Date/Time 8
LineNum Text 10
Shift Text 10
SubEmp Text 35
Dept Text 10
EquipType Text 35
ProductType Text 10
ContNum int 4
Production int 4
ScheduledTime int 4
OnHold int 4
Speed int 4
Potential int 4
PM int 35
EditedBy Text 35
DateCode Text 15
Employee Text 35
UtilTime int 4
Util float 8

tbl_metering Name Type Size
Id int (autonumber) 4
Dept Text 35
EquipType Text 35
LineNum Text 2
Station Text 35
ItemDesc Text 35
LifeCycle int 4
DateOfChange smalldate 8
Comments Text 250
PMType Text 40
UD2 int 4
CreateWo int 4|||To start with, rewrite your query like this:

SELECT tbl_ProductionInfo.LineNum,
tbl_ProductionInfo.Dept,
tbl_ProductionInfo.EquipType,
Sum(tbl_ProductionInfo.Production) AS SumOfProduction,
tbl_Mertering.PMType
FROM tbl_ProductionInfo
inner join tbl_Mertering
ON tbl_ProductionInfo.EquipType = tbl_Mertering.EquipType
and tbl_ProductionInfo.EntryDate <= tbl_Mertering.DateOfChange
AND tbl_ProductionInfo.LineNum = tbl_Mertering.LineNum
AND tbl_ProductionInfo.EquipType= tbl_Mertering.EquipType
WHERE tbl_Mertering.UD2=0
GROUP BY tbl_ProductionInfo.LineNum,
tbl_ProductionInfo.Dept,
tbl_ProductionInfo.EquipType,
tbl_Mertering.PMType
HAVING Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)

DISTINCT is not need in GROUP BY queries, and your LEFT JOIN is superfluous when you are matching records in the WHERE clause.

Now to your problem...
Does tbl_Mertering hold a history of values, differentiated by DateOfChange, or is DateOfChange just updated every time a record is modified?

blindman|||A new record is written and DateOfChange is added for each record so that I maintain a record of the date the PM was done. The reason for this is that there may be delay in when work is actually done. With the historic data, we can tell what the average production and/or actual production between PM's is.|||What I am trying to do is is sum the production information (tbl_ProductionInfo) that is greater than the last date a particular task was done (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) and the production has met the quanity ran (Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)).

[I can't speak to whether the above Max() code, etc, is appropriate or correct. I simply include it as part of a quote. In fact I don't think it is...]

I suggest that you first construct a query which returns "the production information that is greater than the last date..." This query will return all rows.

Then build a second query which is based on the first (i.e. it takes input from the first), and does the sum.

That combination is clear, easy to understand, and also easy to prove/audit by desk checking. Furthermore, when you go to run the combined query, the DBMS will automatically consider both queries in combination when building the overall execution plan.|||Yes! I do need to approach it differently as I continue to get an incorrect sum of production.

I had thought that I could return a recordset with all the records in the metering table with UD2 = 0 and then with active x loop through the recordset with a second query that would sum the production based on the critera (line, date, equiptype, etc) and sum >= LifeCycle.

I had just hoped that I could learn a cleaner way.
Thanks,
Lee|||My BAD!

I missed the signing
tbl_ProductionInfo.EntryDate <= tbl_Mertering.DateOfChange to
tbl_ProductionInfo.EntryDate >= tbl_Mertering.DateOfChange
In the INNER JOIN critera.

Also I found that that collects the production data was not putting the right equipment type in the column. I corrected that as well and now IT SEEMS to be working fine.

Thanks to all for your help!
You are the best!