INNER JOIN query question

[rookie7]

I'm a novice at Access, so please be patient with me.
My database has 3 tables: Chairs, Tables, and Status


* "Chairs" table contains: (data for chairs)

ItemNo --- Matl ------ Color ------- Type ------- Price
120 ------- Cherry ---- Drk Brwn ---- Dine Set ---- $50
141 ------- Mable----- Lt Brwn ------- Rocking ---- $150
.
and so on...
.
.
.

* "Tables" table contains: (data for tables)

ItemNo --- Matl ------- Color ------ Type -------- Price
129 --------- Cherry ---- Lt Brwn ----- Patio -------- $45
174 --------- Cherry ----- Lt Brwn ---- Dine Set --- $200
.
and so on...
.
.
.


* "Status" table contains: (table linked to Excel. Data changes daily)

ItemNo ------ Matl --- Ordered --- Completed --- Shipped
120 ----------- Cherry ---- 420 -------- 320 ------------- 200
129 ----------- Cherry ---- 4 ------------ 4 --------------- 4
141 ----------- Mable ----- 95 --------- 78 --------------- 60
174 ----------- Cherry ---- 100 --------- 50 -------------- 50
.
and so on
.
.
.
.



As you can see, all 3 tables have the "ItemNo" field in common, but I can't get the INNER JOIN query to work. The "Tables" and "Chairs" table contains more than 50,000 unique records. The "Status" table contains about 800 unique records.

I'm trying to build a query that will look in the "Tables" and "Chairs" tables for the ItemNo of the "Status" table and display the data from all 3 tables that have the matching ItemNo. So, the resulting spreesheet would look like this:

ItemNo --- Matl ------ Color --------- Type ----------- Price --- Ordered --- Completed --- Shipped

120 -------- Cherry ---- Drk Brwn ------ Dine Set -------- $50 ----- 420 ------------ 320 --------- 200
129 -------- Cherry ---- Lt Brwn ------- Patio --------------- $45 ----- 4 ------------- 4 ---------- 4
141 -------- Mable ------ Lt Brwn ------- Rocking -------- $150 ----- 95 ------------- 78 --------- 60
174 -------- Cherry ----- Lt Brwn ------- Dine Set -------- $200 ---- 100 ------------ 50 -------- 50


Thank you very much in advance for all your help.

 

[Norie]

You should combine the Tables and Chairs tables into one table.

It would have exactly the same fields plus a field for say ItemType. This would be populated with chair/table etc.

 

[rookie7]

Thank you very much for your response. I did that at first, but I have about 6 more tables (not just Tables and Chairs). I imported the data of those tables from a txt files (master lists). I run the master lists weekly to get additional records into the database. So, if the master list for "Tables" has additional records but the "Chairs" master list doesn't, I only have to update the "Tables" table in access. If I combine them, I'd have to reimport all of them.

Sorry if the explanation above is now clear. Thanks for suggesting the additional field of chair/table. It's definitely a must!

 

[Norie]

There is another way around your problem - using a UNION query to combine the tables and then using that query in another query with your Status query.

But the best way to go would be to import everything into 1 table, which has the extra field to indicate the type of furniture.

There are various ways to do that.

One way is to individually import the data into the table then use an update query to populate the ItemType according to where the data came from.

How are you actually importing the data?

 

[rookie7]

Thank you once again for your response. I "downloaded" a master list (data for "Tables" and "Chairs") from a different database software into a notepad file. Then I imported the notepad into Excel to clean it up. I then imported the Excel file into Access using the "Get External Data Function."

Can you show me how to use the Union or any other way to join the "Chairs" and "Tables" tables together without having to import them manually into 1 single table?

Thank you once again for all your help.