A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Flag a value in a group...



 
 
Thread Tools Display Modes
  #1  
Old May 16th, 2010, 02:23 AM posted to microsoft.public.access.queries
Jack Leach
external usenet poster
 
Posts: 33
Default 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  
Old May 16th, 2010, 09:27 AM posted to microsoft.public.access.queries
Edwinah63
external usenet poster
 
Posts: 16
Default 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  
Old May 16th, 2010, 09:28 AM posted to microsoft.public.access.queries
Edwinah63
external usenet poster
 
Posts: 16
Default 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  
Old May 17th, 2010, 12:52 AM posted to microsoft.public.access.queries
Rob Parker[_4_]
external usenet poster
 
Posts: 30
Default 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  
Old May 17th, 2010, 07:48 PM posted to microsoft.public.access.queries
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:26 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.