Query to show non-duplicates

[jereece]

I have a query that includes the following fields

Year
EventNumber
CorrectiveActionNumber
ResponsibleGroup
Committment

Each event will have multiple corrective actions. For each corrective action assigned to a group, the "committment" is also assigned to them, but I also have to assigned a committment to our group (SRG). So this makes the query results show two records for each corrective action, one with the group assigned committment and one for my groups committment. I need a query that will show corrective actions that have the group committment assigned but not a corresponding SRG committment assigned. However since my query results in two separate records, I am unsure how to do this.

Any suggestions?

Thanks,
Jim

 

[DrSnuggles]

Not too certain of the above text, but this might work:
In your query, under the field Responsible, in the criteria put <>"SRG"

 

[jereece]

Not too certain of the above text, but this might work:
In your query, under the field Responsible, in the criteria put <>"SRG"

That would not work because then the query would simply retun ALL the records for ALL groups other than SRG. What I need is all records where there is a Responsible group other than SRG but not a corresponding record where SRG is listed as a Resonsible group.

As a note of clarification, having both records indicates that we (SRG) have concurrence. When a record exists and there is no corresponding SRG record, this indicates that we do not have concurrence and need to add it. So my goal with this query is to return no records which indicates that each problem assigned to a Responsible group also has SRG as a Responsible group meaning we do have concurrence.

As an alternative, is there a way to use a query to merge the two records so that one record will have both Responsible groups listed in the same field? For example, lets say there are 2 records, one having the ENG group as Responsible and the other SRG. Is there a way a query can merge the two records so one record lists the Responsible group as "ENG, SRG"? This way I could see which records are missing the SRG and accomplish my need.

I hope this makes sense. Any help is appreciated.

Jim

 

[neileg]

An inefficient, but possibly workable approach is to create two queries, one which pulls all the records with SRG as the group, and one that pulls all the other records. Then join these two queries in a third query, and test for a null record from the SRG query. This will identify those where there is no matching SRG record.

 

[jereece]

An inefficient, but possibly workable approach is to create two queries, one which pulls all the records with SRG as the group, and one that pulls all the other records. Then join these two queries in a third query, and test for a null record from the SRG query. This will identify those where there is no matching SRG record.

Now that's a thought. I never considered doing it that way. I'll give it a try.

Thanks,
Jim