Show Record If Our Group Is Not Listed (1 Viewer)

[jereece]

I have a query based on a table that has the fields as shown below. When I run the query, I will get a separate record for each group as shown below.

prb_no | seq_no | commitment_user_group_cd
602 | 3 | ERC
602 | 3 | MCE
602 | 3 | SRG
602 | 4 | MCE
602 | 4 | SRG
602 | 5 | SRG
602 | 6 | MCE

I would like to use a query to show only the records where "SRG" is not listed and where there is another group. So in the example data above, I would like the only record to show in the query to be the last record because it's the only one where SRG is not listed along with another group.

The logic behind this is when a problem is initiated and a group assigned, we do some things and document the results. So I am only interested in the records where we have not documented our results too.

Does anyone know how I can do this with a query?

Thanks,
Jim


I want the query to only show the data where there is a user group

 

[ejstefl]

I assumed your table was called tblGroup, just change it to match.

SELECT tblGroup.prb_no, tblGroup.seq_no, tblGroup.commitment_user_group_cd
FROM tblGroup
WHERE (((tblGroup.seq_no) In (SELECT tblGroup.seq_no
FROM tblGroup
GROUP BY tblGroup.seq_no
HAVING (((Count(tblGroup.commitment_user_group_cd))=1)))) AND ((tblGroup.commitment_user_group_cd)="srg"));

 

[ByteMyzer]

ejstefl,

Your query will return all records for which seq_no matches any entry containing the same sequence where the user group="SRG".

jereece, try something like the following (changing the names of the items in italics to the actual name):

SELECT Q1.*
FROM [i]MyQuery[/i] Q1
WHERE Q1.commitment_user_group_cd>''
AND NOT EXISTS
 (SELECT Q2.*
  FROM [i]MyQuery[/i] Q2
  WHERE Q2.prb_no=Q1.prb_no
  AND Q2.seq_no=Q1.seq_no
  AND Q2.commitment_user_group_cd='SRG'
 )
;

 

[jereece]

I appreciate your reply, but I am confused. Am I supposed to put this code into the Criteria field in my query for the field commitment_user_group_cd? This does not seem like the place to me but that's where I have been trying to make it work. I even opened Expression Builder for this field, changed the name of the query to in your code to match my query name. What I get is a an error saying "The Syntax of the Sub-Query is incorrect. Check the sub-query syntax and enclose the sub-query in parenthesis"

Any help is appreciated.

 

[ByteMyzer]

Perhaps I misunderstood your initial post. Are you attempting to build a query which looks at another query, or are you attempting to build a query which looks at a table? You showed us sample data with field names, but you did not specify whether that data was coming from a table or a query, nor what that table/query is named. The more specific you can be, the more we can help you.

 

[jereece]

Sorry if I was not clear. I am building a query that looks at a table. Well actually it's a linked table. The name of the query I am building is qry_Committment. The linked table that the query is based on is called dbo_Committment. If the linked table is a problem, I could use a separate query to copy the data to a local table first.

Thanks for the help.

Jim

 

[pdx_man]

Just put this in a new query. When you create a new query, don't add any tables (hit close on the table listing), then select View ... SQL View and paste this:

SELECT *
FROM dbo_Committment
WHERE seq_no NOT IN (SELECT seq_no FROM dbo_Committment WHERE commitment_user_group_cd = "SRG")