Find matching entries in other tables?

[DeanRowe]

Hi,

Im using Access 2000.

Essentially I want to find out which of the entries in my master table have matching entries in my other tables, and list the ones that do.

So if I have an entry in the master table for “productA”, and there are also matching entries for “productA” in tables “SupplierC” and “SupplierD”. I want to perform a query that will output a list showing “SupplierC” and “SupplierD” (I have A & B tables but if there isn’t an entry in them for “ProductA” I don’t want them on the list.)

I intend to use the results to populate the values of a combo box in future so I require the list to be in a single column, rather than across many columns. Does anyone know if this is possible?

My master table is called “OurProductsTable” and the four supplier tables are called “SupplierA”, “SupplierB”, “SupplierC”, and “SupplierD”.

Each Table has a primary key called “ProductID” and I have linked them together on the relationships screen.

I’m not sure if this is the proper method but I also made another field in each of the supplier tables called “CompanyName” and set the default value as the name of each supplier, so if the entry for supplierC matches the master table entry I can return the “CompanyName” value of “SupplierC”.

Here’s what I thought the code should kind of look like but I don’t know how to apply it properly in a query:

If ([SupplierA].[ProductID] = [OurProductsTable].[ProductID] then [SupplierA].[CompanyName])

If ([SupplierB].[ProductID] = [OurProductsTable].[ProductID] then [SupplierB].[CompanyName])

If ([SupplierC].[ProductID] = [OurProductsTable].[ProductID] then [SupplierC].[CompanyName])

If ([SupplierD].[ProductID] = [OurProductsTable].[ProductID] then [SupplierD].[CompanyName])

Any ideas would be welcomed gratefully. Thank you

 

[tehNellie]

SELECT supplierA.[companyname]
FROM supplierA
INNER JOIN Ourproductstable ON
supplierA.[productID] = ourproductstable.[productid];

Will return you all companynames from table SupplierA that have a matching ProductID in Ourproductstable.

If you want them all in a single list you can use the UNION as follows:

  SELECT supplierA.[companyname]
  FROM supplierA
  INNER JOIN Ourproductstable ON
  supplierA.[productID] = ourproductstable.[productid];
UNION ALL
  SELECT supplierB.[companyname] 
  FROM supplierB
  INNER JOIN Ourproductstable ON
  supplierB.[productID] = ourproductstable.[productid];

However if you're going to want to do something with these in a list box etc, you'll need to be able to figure out which table they came from to begin with:

  SELECT supplierA.[companyname], "SupplierA" as Table
  FROM supplierA
  INNER JOIN Ourproductstable ON
  supplierA.[productID] = ourproductstable.[productid];
UNION ALL
  SELECT supplierB.[companyname], "SupplierB" as Table
  FROM supplierB
  INNER JOIN Ourproductstable ON
  supplierB.[productID] = ourproductstable.[productid];

 

[DeanRowe]

Thank you for your help tehNellie, I never even knew or looked at the SQL side of queries before and especially not Union queries so it’s opened up a wealth of options.

I’ve used the following code and it produces the list which is just as I want it and described:

SELECT SupplierA.[companyname], SupplierA.[ProductID]
FROM SupplierA
INNER JOIN Ourproductstable ON
SupplierA.[productID] = ourproductstable.[productid];
UNION ALL SELECT SupplierB.[companyname], SupplierB.[ProductID]
FROM SupplierB
INNER JOIN Ourproductstable ON
SupplierB.[productID] = ourproductstable.[productid];
UNION ALL SELECT SupplierC.[companyname], SupplierC.[ProductID]
FROM SupplierC
INNER JOIN Ourproductstable ON
SupplierC.[productID] = ourproductstable.[productid];

Do you know how I can add a “where” clause to this argument, such as only returning the results where productID = “XYZ”? (I’ll change it to a text box on a form later on using [Forms]![FormName]![TextboxName])

Thank you again for your help, you’ve been great.

 

[DeanRowe]

sorry that might not have been very clear, at the moment the list produces results for every product in the "ourproductstable" - I want to restrict it to a single product, in this case product "XYZ"

I've tried the following code but to no joy:

WHERE (((Ourproductstable.[productid]) = "XYZ"));

I've tried putting it at the end of the whole SQL statement and before each "UNION ALL SELECT" part.

Thanks again TehNellie

 

[DeanRowe]

Got it in the end, I made a query against the union query to return the specific data required.

Thank you very much for the time you spent helping me TehNellie, I thank you very much indeed.