Create synchronized form that shows values NOT asscoiated with parent

[jcfraun]

This one is hard to explain....

I have two tables.

Table1: AttributesFiltered
Field: RecordID
HeaderProduct
AttributeName
Table2: Category
Field: Category
AltProd

These two tables are related through the HeaderProduct and AltProd fields.

I currently have a form: FRM-MainForm with an unbound combo box to select the category.

The first subform is FRM-AttsSubform and has a parent-child relationship with the main form on the Category fields. It is datasheet view and has the following underlying query:

SELECT DISTINCT [TBL-Category].Category, [TBL-AttributesFiltered].AttributeName
FROM [TBL-Category] RIGHT JOIN [TBL-AttributesFiltered] ON [TBL-Category].Altprod = [TBL-AttributesFiltered].HeaderProduct;

The second subform is FRM-AllProds&Cats-ByAttribute and has the following parent-child relationship. It is datasheet view and has the following underlying query:
Parent: MainForm-Category Child: Category
Parent: FRM-AttsSubform-AttributeName Child: AttributeName

SELECT DISTINCT [TBL-AttributesFiltered].HeaderProduct, [TBL-Category].Category, [TBL-AttributesFiltered].AttributeName
FROM [TBL-Category] RIGHT JOIN [TBL-AttributesFiltered] ON [TBL-Category].Altprod = [TBL-AttributesFiltered].HeaderProduct;

So, if I select a category, the first subform gives me a list of all the associated attributes. Then when I select an attribute in the first subform, the second subform gives me a list of all the products in that category that have that given attribute.

Now, my question: I want a third subform that gives me the products in that category that do NOT have that selected attribute associated. It will have the same parent-child relationship as the second subform.

I was able to do this by creating a query (QRY-Cats&Prods&AllAtts) that gives me all the possible product/attributename combinations:

SELECT [QRY-AllProds&Cats].HeaderProduct, [QRY-AllProds&Cats].Category, [QRY-Cats&Atts].AttributeName
FROM [QRY-AllProds&Cats] INNER JOIN [QRY-Cats&Atts] ON [QRY-AllProds&Cats].Category = [QRY-Cats&Atts].Category;

The QRY-AllProds&Cats has the following SQL:
SELECT DISTINCT [TBL-AttributesFiltered].HeaderProduct, [TBL-Category].Category
FROM [TBL-AttributesFiltered] LEFT JOIN [TBL-Category] ON [TBL-AttributesFiltered].HeaderProduct=[TBL-Category].Altprod;

And the QRY-Cats&Atts has the following SQL:
SELECT DISTINCT [TBL-Category].Category, [TBL-AttributesFiltered].AttributeName
FROM [TBL-Category] RIGHT JOIN [TBL-AttributesFiltered] ON [TBL-Category].Altprod = [TBL-AttributesFiltered].HeaderProduct;


And then a query (QRY-DistinctAttsCats&Prods) that gives me the distinct product/attributename that exist:

SELECT DISTINCT [TBL-AttributesFiltered].HeaderProduct, [TBL-Category].Category, [TBL-AttributesFiltered].AttributeName
FROM [TBL-Category] RIGHT JOIN [TBL-AttributesFiltered] ON [TBL-Category].Altprod = [TBL-AttributesFiltered].HeaderProduct;


Then I have a third query that shows me all the products and attributenames from Query A and only shows the product name from Query B if there is a match. That way, when I show all the records with a null value for that last field (from Query B), I have all the combinations that don't exist.

SELECT [QRY-Cats&Prods&AllAtts].HeaderProduct, [QRY-Cats&Prods&AllAtts].Category, [QRY-Cats&Prods&AllAtts].AttributeName, [QRY-DistinctAttsCats&Prods].HeaderProduct
FROM [QRY-Cats&Prods&AllAtts] LEFT JOIN [QRY-DistinctAttsCats&Prods] ON ([QRY-Cats&Prods&AllAtts].Category = [QRY-DistinctAttsCats&Prods].Category) AND ([QRY-Cats&Prods&AllAtts].AttributeName = [QRY-DistinctAttsCats&Prods].AttributeName) AND ([QRY-Cats&Prods&AllAtts].HeaderProduct = [QRY-DistinctAttsCats&Prods].HeaderProduct)
WHERE ((([QRY-DistinctAttsCats&Prods].HeaderProduct) Is Null));


This worked fine, but Access crashes on the main form when I select a new category. I assume that there was something circular or not quite streamlined enough about this resulting query.

Any tips on starting over and creating a query for the third subform in a more direct manner?

 

[MajP]

A much easier way to show the products in that category not the "NOT IN " the selected attributes is to use a subquery.

http://www.java2s.com/Code/SQL/Select-Clause/SubquerywithNOTINcommand.htm

In general it is something like

Select * from tblX where fld NOT IN (Select * from qryY)

 

[jcfraun]

Thanks MajP, but will that work when my criteria is based on two fields instead of one field?

Select * from tblX where fld1, fld2 NOT IN (Select * from qryY)