Quick table links - help!

[jjerge]

I am working with 100+ product tables. For each product, I have uploaded a table of part numbers and another table of part number matches to the first table. Common to all tables is part number.

I am setting up queries to go combine all part numbers reviewed, only matched part numbers, non-matched part numbers, etc.

How do you link the part number relationship in all of these tables without doing it manually, IE, dragging and dropping in a new query. Plus, I'll be adding additional tables and I want the field "Part Number" automatically linked in all of the tables.....

 

[Norie]

Have you considered not having 100+ product tables in the first place?

 

[jjerge]

That's sort of an odd response..why do databases exist anywhere? These product tables have been built in access over time, and the question is how do you link them quickly to eachother. The different tables are from different sources with different formats. Not sure I understand your point.

 

[Norie]

Well it's kind of hard to explain without more information about your data structure.

But it sounds to me as though it's not normalised.

Normalised data is much easier to handle.

Normalising data can sometimes be a bit of a chore but the time put into doing it can reap benefits.

 

[NumbersMax]

J,

I was wondering the same thing as Norie, about your data when I read your post..."I wonder if they could benefit from normalizing their data?" I don't have a link to post here, but if you look at some of Vic Rouch's (sp?) posts, he has a link to information about normalizing your database that could be helpful.

I can understand the desire to just fix it so it works and forget about "the best way" to do something (Note: I know you haven't indicated you feel this way), but with so many tables, you could very easily be fighting queries with duplication of data for many months to come. It is that time spent (or not if you normalize) that Norie speaks of and the potential benefit outweighs the time lost if you are willing to bite the bullet.

From your post, you make it sound like you have two tables per product with only part numbers in them or little else besides that. What normalizing would do it cause you to combine these to one, or at most a few, tables that have columns:

Product ID
Part #
Matched Part #
Other common field names
Product specific field names

There may be several part numbers per product ID, and several matched part #s per Part #, but it would be all in one table and the need to

"link the part number relationship in all of these tables without doing it manually, IE, dragging and dropping in a new query."

would probably disappear as you could filter using just the criteria from the single table.

If, however, you choose not to normalize, you would probably have to go through the hoops of adding a relationship between each of your 100+ tables. I believe this can be done by choosing Tools/Relationships from the menu, adding each table and clicking and dragging manually.

Hope this helps.
Max