Jack -

You can add another field that shows the count of Release Types that are 1
with this trick. Add an Iif Statement that checks to see if the
fldReleaseType is a 1 or not. If it is a 1, then set the value to 1,
otherwise set the value to zero. Sum these in your query for a count of how
many of the recrds have a Release Type of 1. I added the code in here, but
it is untested:

SELECT tblOrderDetails.fldPart,
tblOrderDetails.fldRev,
tblOrderDetails.fldDescription,
Min(tblOrderReleases.fldDueDate) AS cfldFirstDue,
Sum([tblOrderReleases].[fldQty])-Sum([tblOrderReleases].[fldQtyToBE]) AS
cfldQtyToProcess, Sum(Iif([tblOrderDetails].[fldReleaseType] = 1,1,0)) AS
CountOfReleaseType_1
FROM tblOrders
LEFT JOIN (tblOrderDetails
LEFT JOIN tblOrderReleases
ON tblOrderDetails.fldID = tblOrderReleases.fldDetail)
ON tblOrders.fldOrder = tblOrderDetails.fldOrder
WHERE (((tblOrders.fldStatus)=0)
AND ((tblOrderReleases.fldBEdComplete)=0))
GROUP BY tblOrderDetails.fldPart,
tblOrderDetails.fldRev,
tblOrderDetails.fldDescription;

--
Daryl S


"Jack Leach" wrote:

Hi all, tia for any insight.

I have the following query that gives some totals for qty's in a releases
table, pulling and grouping information (part number, rev and desc) from a
details table. Table heirarchy is (one to manys) tblOrders -
tblOrderDetails - tblOrderReleases



SELECT tblOrderDetails.fldPart,
tblOrderDetails.fldRev,
tblOrderDetails.fldDescription,
Min(tblOrderReleases.fldDueDate) AS cfldFirstDue,
Sum([tblOrderReleases].[fldQty])-Sum([tblOrderReleases].[fldQtyToBE]) AS
cfldQtyToProcess
FROM tblOrders
LEFT JOIN (tblOrderDetails
LEFT JOIN tblOrderReleases
ON tblOrderDetails.fldID = tblOrderReleases.fldDetail)
ON tblOrders.fldOrder = tblOrderDetails.fldOrder
WHERE (((tblOrders.fldStatus)=0)
AND ((tblOrderReleases.fldBEdComplete)=0))
GROUP BY tblOrderDetails.fldPart,
tblOrderDetails.fldRev,
tblOrderDetails.fldDescription;


There is one more piece of information I am trying to discern from this
query, but am not sure how, or if, it is done. There is a field in
tblOrderDetails called fldReleaseType with possible values of 0, 1 or 2. Is
there any way, to somehow note in the returned set of records, if any of the
records within a particular group has this fldReleaseType with a value of 1?

I would be happy to just somehow know that one or more of the grouped
records contains that value... it's not imperitive that I know which
particular record has it. Can I create a calculated flag field that is True
if a 1 is found?

Many thanks,

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)