Quick table links - help!

jjerge

New Member
Joined
Dec 6, 2004
Messages
24
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.....
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Have you considered not having 100+ product tables in the first place?
 

jjerge

New Member
Joined
Dec 6, 2004
Messages
24
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-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 19, 2007
Messages
200
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
 

Forum statistics

Threads
1,181,053
Messages
5,927,848
Members
436,572
Latest member
khalid hussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top