combine tables into one tabe (1 Viewer)

hi,

Our company has a (parts record) database this database has about 50 tables in it
for example , say table 111,222,333,444 etc
each table will have parts in it with referencing I it say there are 5 parts in table 111 the parts could be

111-001
111-002
111-003

and table 222 would have parts in it called
222-001
222-002 etc etc

the engineering department want to keep this database

I have another access database that needs to access the part numbers and select them from a drop down box but I need to see them all at the same time.

how can I get all the tables into one table then link that database (parts record) to my database to select the parts i need from the drop down box

the data base that has all the table in it must always be upto date with the table that will have all the records in it

thanks steve

You wouldn't. First you would explain to engineering that what they want is breaking the rules of database normalization and improperly storing the data. You would tell them that data decisions should be left up to people who know about data. You would explain that the data is the same in every table, therefore it should be stored in just one table and queries should be used to extract out just the data from that main table they want.

Then after they make an non-sensical argument about how things have always been done and management agrees with them, you would use a UNION query.

For your purposes, you wouldn't create a new table, you would build a query to get all the data you need. A UNION query brings together many data sources into one data source which will allow you to use it as you need it. Here's the documentation on it:

https://www.w3schools.com/sql/sql_union.asp
Assuming all your tables have the exact same structure, in your instance the query would look something like so:

rainbows said:

.....the data base that has all the table in it must always be upto date with the table that will have all the records in it

thanks steve

Click to expand...

As plog says, this does not make sense, Steve. The above is a chore (which will likely turn into a headache) which should not exist. Consolidate everything into one table where the section or department (which now defines a table) becomes a column, or attribute, of a single table. If the fields of the existing tables are not always the same for some parts, it does not matter, the new table should accommodate all the variances, or of need be, create details to those parts. But the bottom line is, your parts "database" will always be in sync with itself. You will never have to worry about that! Here is an outline of how you create the big table (warning : this is an 'air-code' based on a working application but not tested) :


You need to make sure that that the big table in your other database (called here "DataPath&Name") has all the fields the individual tables have and they are called the same. If not you would have to go table by table and field by field. Good Luck

Best,
Jiri

The original design is wrong on so many levels it's hard to even address them. Do you think Kenmore (the Sears brand) has a separate table for each type of product they make - Stoves, refrigerators, washers, dryers, dishwashers, microwaves, snow blowers, lawn mowers, sewing machines, vacuum cleaners, etc.

I can assure you they don't. Nor does CitiBank have a separate table for each customer.

There are ways to handle items having a different set of attributes but that doesn't seem to be an issue for you so it sounds like all the tables are the same format. What do they do if they make a new type of widget?

It also sounds like the engineers are directly accessing the database objects rather than using forms or reports. Perhaps creating a few forms and reports that let them filter by widget type would help move them off their current bad design.