Query through multiple tables

[lord_kris]

Hello World!

I am working on this project but I am stuck in a little rutt concerning queries and how to set up one properly.
My project is set up so that I have several tables with different TV sizes (ie 20 inch, 22 inch, ... 60 inch) Each contain roughly the same types of info - like each have a TV Model no. and a product number with few containing specific info that only that TV would have, like a special part to the TV.

So what I want to do is relate these many TV TABLEs to the STAND TABLE that they would be using. To clarify, I have like 10 different tables of TV sizes and 1 stand list. The TV TABLEs are related to the stand list by TV model. Sounds easy enough right?

But my problem is this- how do I make a query where I am able to just put in TV Model and it will search throught ALL the tables and put out the necessary info - lets say I want TV Model, related Stand, and Product Number outputed. I only know how to do one specific query where it will output the search for only one specific table ... I want to seach through all the tables.

Because each TV TABLE didn't have any relation to each other what I've done thus far was make a new column on each TV TABLE named Brand. (I am only dealing with one TV company). So I have a feeling that having all these TV TABLEs related is the key, but I don't know how to go from here. (So each TV TABLE has like the same kinds of columns, but different numbers and what not, the only relation is that Brand column.)

My knowledge of access is not that deep and I have a feeling this is a simple answer but I currently cannot find it. To me its like complicated and simple at the same time. haha

Thanks in advance,
Kris

 

[boblarson]

First off, your table structure is not good. It should be normalized (see here for more about that) and not have tables for each tv size. I think you need to redesign your tables to be proper and then we can visit queries. If you don't have it properly normalized it is going to be a real pain in the butt to get any type of meaningful data out without going through a bunch of painful band-aid solutions.

 

[lord_kris]

So from what I understand, the repetition of same columns in each of the tables of various TV sizes is bad basically creating multiple records. It does make a lot of sense.

Since last week I have used this information and consolidated all the TV TABLEs into one massive table. Now the process of query's seems simple as I can easily pull out the data that I need using that massive new TV TABLE and relating it to the TV STAND table that I have.

The only thing that concerns me now is the ease of use for people who would potentially be using and editing the database. The TV TABLE alone has around 300 entry rows and 20 columns (don't know if thats considered a lot). So editing might become a little bit of pain in the butt.

Are there good ways to clean up the database? Maybe add more tables which separate the columns from the TV TABLE? - of course not making the mistake I made previously with same column names in different tables (I can't logically see this thought as helping to a great extent, but like I said before, my access knowlegde is not huge so I dont know if this my help)

Thanks once again,
Kris