Selecting Certain groups with a particular record

[bbmg2010]

Hello all,

I am trying to form a query to select on certain groups with a particular record, but still show the other records in that group, while excluding groups that do not have that record. for example:

Group1 special record
Group1 other record
Group1 special record
Group2 other record
Group2 other record
Group2 other record

So while I would need to show all records from group 1, since it contains at least one special record, and not show any records from group 2. I have tried a few IIf-then statements that did not work. Any help would be appreciated.

 

[Joe4]

Welcome to the Board!

This can be done pretty easily with a series of two queries.

Query1 - return groups with special records. Use an Aggregate Query, something like:

SELECT [GroupNumber]
FROM [Table1]
WHERE [RecordType]="Special"
GROUP BY [GroupNumber];

Then, create a second Query where you join your original table in with your Query above, joining on the GroupNumber field, i.e.

SELECT [Table1].*
FROM [Table1]
INNER JOIN [Query1]
ON [Table1].[GroupNumber] = [Query1].[GroupNumber];

That should give you what you want.

 

[bbmg2010]

Thanks so much for your response Joe4! I have gotten to a point where I have most of what I want. I am currently trying to learn access on the job and this is only my second week. One last question I had. Is there a way to eliminate the "other records" in groups that contain the "special record" and just show the rows with special records, while showing all rows of groups without "special records". For example could I do a query to get from what I have in my first post to this:
Group1 Entity1 special record
Gruop1 Entity2 special record
Group2 Entity2 other record
Group2 Entiy 3 other record

As you can see the "other record in group1 would be eliminated.

Again thanks for your help!

 

[Joe4]

I am a little confused. That doesn't resemble the original records you posted in your initial post. That was no "Entity" field.
Are you looking for a single record for each Group/Entity combination?

What are the exact fields that you want to return in this query?
A real or representative data sample would actually be very helpful, so we can see exactly what you want to return - it might affect how we want to write the query.

 

[bbmg2010]

Sorry for the lack of information in my previous post, as I was just trying to get a start on the direction I needed to head. Here is a more comprehensive look at the data set I have now, which I got by combining a few queries I did:

SSN Gruop_ID Group_Name Entity_ID Entity_Name Record_Type Record_Number
### 111 Group1 1 Entity1 special record 00000001
### 111 Group1 1 Entity1 other record null
### 111 Group1 1 Entity1 other record null
### 111 Group1 1 Entity1 other record null
### 111 Group1 2 Entity2 other record null
### 111 Group1 2 Entity2 special record 00000002
### 111 Group1 3 Entity3 other record null
### 111 Group1 3 Entity3 other record null
### 111 Group1 4 Entity4 other record null
### 111 Group1 4 Entity4 other record null
### 111 Group1 5 Entity5 other record null


To give a little more overview. There are thousands of lines just like this with hundreds of groups with different entities. The null values in record_number are not actually null. I just have them showing null if it is a different record than the "special record", which I did with an IIf-Then statement. So what I am trying to sree at this point is can I remove the duplicate entity names. For instance in Group1, their is 1 special record and three other records. I want to show the entity name only once for the special record and omit the duplicates for that same entity name with "other records". As you can see Entities 3, 4 and 5 do not contain a "special record but do belong to Group1. I also want to show each entity name that contain No "special record" once. So the data set above would look like this:

SSN Gruop_ID Group_Name Entity_ID Entity_Name Record_Type Record_Number
### 111 Group1 1 Entity1 special record 00000001
### 111 Group1 2 Entity2 special record 00000002
### 111 Group1 3 Entity3 other record null
### 111 Group1 4 Entity4 other record null
### 111 Group1 5 Entity5 other record null



If I did: IIf([Record_Type]="special record", [Record_Type],Null. This would eliminate entity names for entities that do no have a special record, which is not what I want. I want to show each entity name once. If the entity name had two special records then of course it would have to show twice in order to show both special records, which is fine. Is this possible? Either way thanks again for your help!

 

[Joe4]

As you can see Entities 3, 4 and 5 do not contain a "special record but do belong to Group1. I also want to show each entity name that contain No "special record" once.

If a Group/Entity combination has no "special record", but multiple "other records", how do you determine which one of these "other records" to show? Or will they be exact duplicates of each other (so it doesn't really matter which one is returned)?

Also, is SSN part of your "grouping", or just Group & Entity?

 

[bbmg2010]

Joe,
For the entities that contain no "special record", it does not matter which record I show as long as the entity name is shown just once. I hope this makes since.

 

[bbmg2010]

I dont really understand what you are asking as far as SSN. Each entity has its own SSN, so there can be many SSN in a Group

 

[Joe4]

I dont really understand what you are asking as far as SSN. Each entity has its own SSN, so there can be many SSN in a Group

Let's look at an example. Let's say that there are two different SSNs associated with Group1/Entity1 (see below):

[COLOR=#333333][FONT=Verdana]SSN  Group_ID  Group_Name  Entity_ID  Entity_Name   Record_Type       Record_Number[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]123    111         Group1             1              Entity1             special record     00000001
[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]123    111         Group1             1              Entity1             other record       null[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]789    111         Group1             1              Entity1             other record       null[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]789    111         Group1             1              Entity1             other record       null[/FONT][/COLOR]

What is your expected outcome here?
Just one record for Group1/Entity1?
Or two records, one for each Social Security Number?

 

[bbmg2010]

Each entity will only have 1 SSN. So Group1/Entity1 will have 1 SSN:


SSN Group_ID Group_Name Entity_ID Entity_Name Record_Type Record_Number123 111 Group1 1 Entity1 special record 00000001123 111 Group1 1 Entity1 other record null123 111 Group1 1 Entity1 other record null123 111 Group1 1 Entity1 other record null321 111 Group1 2 Entity2