Comparing data from multiple tables

[Ms Kathy]

We have an "item" table that contains information specific to an item (the item number is unique); and multiple "production line" tables. There is a relationship between the Items table and the Production Line tables (all 8 of them). Each item can be entered on 1 or more production line tables because one item may be produced on multiple lines and the settings vary based on the line they're produced. When entering an item in the Production table we MUST make a selection from the Item table.

Now, what I want to do is identify which item number(s) from the Items table does not have a specific field entry in ANY of the Production Line tables. Any help is appreciated. I do not have an understanding of writing code.

 

[plog]

First let's get the admonishment out of the way: why do you have multiple Production Line tables? Do all of these tables have the same structure? If so, then all that data needs to be put into one table with a new field to distinguish what production line each row is for.

For your issue you will need to first build a UNION query (http://www.techonthenet.com/sql/union.php) to get all the item data in those Production Line tables into one data source. Since all you care about is the presence of an item number, the below code will be the template for that UNION query

SELECT ItemNumberField FROM ProductionLineTable1
UNION
SELECT ItemNumberField FROM ProductionLineTable2
UNION 
SELECT ItemNumberField From ProductionLineTable3
UNION
...

Then you build another query using your item table and that query. You will LEFT JOIN from your item table to the above query (meaning you show all from the Item Table). Make the query an aggregate query by clicking the Sigma/Summation symbol in the ribbon. Underneath each item field it should say 'GROUP BY', change the one under the Item field from the UNION query to 'Where' and then put this in the criteria section 'Is Null'. Run that query and you have the data you want.

 

[Ms Kathy]

First of all, thank you for your reply. It will take me some time to follow through with this. In response as to why multiple production line tables, there are different fields in the tables. For example, Line 1 may require 30 fields/settings whereas Line 2 may only require 4, etc.

 

[Ms Kathy]

Do I need to type in "field" or "table"? I am getting an error message indicating that "could not find the object '90_ftItemNumber' . . . I confirmed the spelling, etc. Your thoughts please?

SELECT 90ft_ItemNumber FROM T_ProductionLine_90ft
UNION
SELECT 80ft_ItemNumber FROM T_ProductionLine_80ft
UNION
SELECT Jelly_ItemNumber FROM T_ProductionLine_JellyRoll
UNION
SELECT Donut_ItemNumber FROM T_ProductionLine_Donut
UNION
SELECT JellySpur_ItemNumber FROM T_ProductionLine_JellyRoll_Spur
UNION
SELECT IcingSpur_ItemNumber FROM T_ProductionLine_Icing_Spur
UNION
SELECT Repack_ItemNumber FROM T_ProductionLine_Repack
UNION
SELECT Cake_ItemNumber FROM T_ProductionLine_Cake

 

[plog]

Well, you misspelled something somewhere. Your error message says '90_ftItemNumber', but in your query you have '90ft_ItemNumber'.

Whichever it is, it doesn't exist in the table you referenced. Each of those SELECT statements should be a valid query. You can test them individually by pasting them into a new blank query and running it. If it returns results, that portion is valid and you can add it to the the UNION.

 

[Ms Kathy]

My typo when typing the error message on my post. The field name is correct. I went into the database and copied and pasted the field name. And double checked. There must be something else I'm missing?

 

[plog]

Paste this into a new query and run it:

SELECT 90ft_ItemNumber FROM T_ProductionLine_90ft

If it doesn't work, open it up in design view and see what you get.

 

[Ms Kathy]

I get the error "Syntax error (missing operator) in query expression '90ft_ItemNumber'."

In design view this is highlighted: ft_ItemNumber

 

[plog]

Ok--you need brackets around your field names because they begin with numbers:

SELECT [90ft_ItemNumber] FROM T_ProductionLine_90ft

 

[Ms Kathy]

Bingo!! Thank you!!!!!