Query to show missing records

[ChristopherM]

I have a table for stock with two fields, KEY and DESCRIPTION; a table for manufacturers with two fields, KEY and NAME; and a third table which links to them both with a many-to-one join with three fields, KEY, STOCK-KEY and MFR-KEY. What I am trying to do is write a query that for a given manufacturer (entered via a parameter) shows a single line for all stock records that are NOT linked to it via the third table. I am sure it should be simple but all my attempts fail to exclude stock linked to the manufacturer if it is also linked to another manufacturer. Any ideas?

 

[ted.martin]

In the Query pane, when you select NEW, the New Query window has an option called Find Unmatched Query Wizard. This will (I hope) be the way to do this for you. The results can also be filtered for a particular Manufacturer.

 

[jdraw]

I have a table for stock with two fields, KEY and DESCRIPTION; a table for manufacturers with two fields, KEY and NAME; and a third table which links to them both with a many-to-one join with three fields, KEY, STOCK-KEY and MFR-KEY. What I am trying to do is write a query that for a given manufacturer (entered via a parameter) shows a single line for all stock records that are NOT linked to it via the third table. I am sure it should be simple but all my attempts fail to exclude stock linked to the manufacturer if it is also linked to another manufacturer. Any ideas?

As I understand your structures, based on your forum entry:

"table for stock with two fields, KEY and DESCRIPTION"
Stock
-- Key PK (is this Stock-Key)
-- Description

"table for manufacturers with two fields, KEY and NAME"
Manufacturer
--Key PK (is this Mfr-Key)
--Name

" a third table which links to them both with a many-to-one join with three fields, KEY, STOCK-KEY and MFR-KEY"
ThirdTable
--Key
--Stock_Key
--Mfr_Key

What is the structure of the Third table? Do you have some sample data?

Do you have Foreign Key constraints on ThirdTable?
eg

ALTER TABLE ThirdTable
   ADD CONSTRAINT FK_tblManufacturer
   FOREIGN KEY (MFR_key) REFERENCES
    Manufacturer ([Key])
   ;

 

[ChristopherM]

Thanks for your advice. In its "raw" state the query produced by the wizard selects a field from the "linking" record with the criterion "Is Null" which means it only shows stock not linked to any manufacturer. I tried adding another column with the manufacturer in the linking record and in the "OR" criterion typed "<>...." to the manufacturer in question (for not equal to) but that brings up the stock reords that are not linked to any manufacturer AND those linked to other manufacturers - with a line for each manufacturer they are linked to. If I put this second criterion on the same line as the "Is Null" the query returns no records at all.

 

[ted.martin]

You'll have to play with it but essentially that is the way to do it. Keep the logic simple and test some simple query examples one step at at time.

 

[ChristopherM]

Sorry - messages are crossing one another in the ether. In reply to jdraw, the tables are exactly as you describe them so the stock table might be:-

KEY Description
=== =========
1 Chair
2 Table
3 Sofa

and the manufacturers:-

KEY Description
=== ========
1 CompanyA
2 CompanyB
3 CompanyC

and the linking table:-

KEY STOCK-KEY MFR-KEY
=== ========= =======
1 1 1
2 2 1
3 1 2
4 3 2
5 3 3

So companyA supplies chairs and tables, and I want the query with companyA as the parameter to show sofas - ie what it does NOT supply.

I'm not sure I understand your comments about constraints as I have never used them. The records are created by a bit of code behind a button on a subform. The form holds the manufacturer details and the record source for the subform is determined by clicking on one button to add links to stock or another button to remove them. But for the "add links" I only want to show stock which isn't already linked.... but I can't get the query to work!

 

[ted.martin]

This may be a bit late in the day but why not in the Manufactures table, if the number of products is not too great, have a simple Check box for EACH of the Stock Variants. For a Manufactrer, if the CheckBox is blank (=0) then it is not supplied.

 

[ChristopherM]

Solution

Thanks for all the advice - fyi I have got a working solution using the "Unmatched Records" query wizard although the route is a bit counterintuitive. First I wrote a query for the linking table to select only those records for the manufacturer whose unlinked stock I wanted to list. Then I wrote a second query using the unmatched records wizard to find those stock records which didn't have a match with the first query.

 

[ted.martin]

Exactly the way to do it - well done.