Flag a value in a group...

J

Jack Leach

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)
 
E

Edwinah63

Maybe try the Switch function:
SELECT fldPart,
    fldRev,
    fldDescription,
    Min(fldDueDate) AS cfldFirstDue,
    Sum([fldQty])-Sum([fldQtyToBE]) AS cfldQtyToProcess,
Click to expand...

SWITCH(fldReleaseType=0, "This is a 0", fldReleaseType=1, "This is a
1", fldReleaseType=2, "This is a 2")
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;
Click to expand...


Or just try the IIF(condition, true, false)

IIf(releasetypeid = 1, "Yippee", "foo!")

The true and false parts of the statement can be replaced with
calculated values

HTH
 
E

Edwinah63

Above should read

SWITCH(fldReleaseType=0, "This is a 0", fldReleaseType=1, "This is a
1", fldReleaseType=2, "This is a 2") AS MyCalculatedField
 
R

Rob Parker

Hi Jack,

Adding this field to the SELECT clause seems to work:

DCount("*","tblOrderDetails","[tblOrderDetails].[fldOrder] = " &
[tblOrderDetails].[fldOrder] & " AND fldReleaseType = 1")>0 AS
HasReleaseType1

If you've got a lot of records it may be slow, being a domain aggregate
function that's running for each record. I tried using a sub-query (which
is likely to be faster), but couldn't get the syntax right; I kept getting
"missing operator" syntax errors.

HTH,

Rob
 
D

Daryl S

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;
 
Top