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.

 

[bbmg2010]

Is there something Im doing wrong with the formatting on the site. My post are not showing in the format that I type them in.

 

[bbmg2010]

Im obviously doing something wrong as far as formatting on this site. My post are not showing up the way Im typing them in.

 

[Joe4]

Im obviously doing something wrong as far as formatting on this site. My post are not showing up the way Im typing them in.

You cannot post images unless you use on the tools mentioned here: http://www.mrexcel.com/forum/about-board/508133-attachments.html
And if you are trying to post your data uses multiple spaces to line things up, multiple spaces aren't maintained going across unless you use the CODE tags. Note that you can use the "Preview Post" button under the Advanced Reply section to view what your reply will look like before submitting it.

 

[Joe4]

OK. I can get what you want, but it involves multiple steps.

The first thing, is to make sure that you have a Primary Key/Unique field in your original data table. If you don't, just use the built-in Autonumber feature Access has. I used that, and named this field "ID". This will be important going forward.

Now, we want to build a query that returns all of our "special records". Assuming the data we are working from is "Table1", the SQL code for that query will look something like this:

SELECT Table1.*
FROM Table1
WHERE Table1.Record_Type="special record";

Pretty basic query, it just returns all "special records". I named this query "qry-Special_Records".

Now, we want to build another query that returns the first ID number of Group_ID/Entity_ID listings that do NOT have any special records. The SQL code for that query looks like this:

SELECT Table1.Group_ID, Table1.Entity_ID, Sum(IIf([Table1]![Record_Type]="special record",1,0)) AS Special_Check, First(Table1.ID) AS FirstOfID
FROM Table1
GROUP BY Table1.Group_ID, Table1.Entity_ID
HAVING (Sum(IIf([Table1]![Record_Type]="special record",1,0)))=0;

If you add that to the SQL View section of your query and then switch to Design View, you should have a better view of what is going on.
I named this query "qry-No_Special_Records".

Now, we want to return the records from our original table where the ID are found in either of the two queries we just built above. The SQL code for that query looks like:

SELECT Table1.*
FROM Table1
WHERE 
(Table1.ID In 
(SELECT [qry-Special_Records].ID
FROM [qry-Special_Records]))
OR
(Table1.ID In 
(SELECT [qry-No_Special_Records].FirstOfID
FROM [qry-No_Special_Records]));

That should return what you want.

 

[bbmg2010]

Had to restructure some tables but it did work! Thank you so much for your patients and direction! I will be reading this site more often.

 

[Joe4]

Glad we got it worked out!