Prev: Sum using totals
Next: Determining EVERY month between 2 given dates
From: Jack Leach dymondjack at hot mail dot on 15 May 2010 21:23
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)

From: Edwinah63 on 16 May 2010 04:27
Maybe try the Switch function:

> SELECT fldPart,
>     fldRev,
>     fldDescription,
>     Min(fldDueDate) AS cfldFirstDue,
>     Sum([fldQty])-Sum([fldQtyToBE]) AS cfldQtyToProcess,

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


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
From: Edwinah63 on 16 May 2010 04:28
Above should read

SWITCH(fldReleaseType=0, "This is a 0", fldReleaseType=1, "This is a
1", fldReleaseType=2, "This is a 2") AS MyCalculatedField
From: Rob Parker on 16 May 2010 19:52
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


"Jack Leach" <dymondjack at hot mail dot com> wrote in message
news:E451DCCA-B296-4383-A1C9-30A9D979245C(a)microsoft.com...
> 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)
>

From: Daryl S on 17 May 2010 14:48
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)
>
 | 
Pages: 1
Prev: Sum using totals
Next: Determining EVERY month between 2 given dates