If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Flag a value in a group...
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) |
#2
|
|||
|
|||
Flag a value in a group...
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 |
#3
|
|||
|
|||
Flag a value in a group...
Above should read
SWITCH(fldReleaseType=0, "This is a 0", fldReleaseType=1, "This is a 1", fldReleaseType=2, "This is a 2") AS MyCalculatedField |
#4
|
|||
|
|||
Flag a value in a group...
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 ... 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) |
#5
|
|||
|
|||
Flag a value in a group...
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) |
Thread Tools | |
Display Modes | |
|
|