join multiple tables? (1 Viewer)

Hello, I hope someone can help me out here,


I've inherited a database from long ago which has a lot of bills of materials as separate tables and the table name is the part number for that bill of material.

I need to join all of these tables so they can be exported to PSQL.

The tables all have the same fields within them (part no, qty, etc.)
I'd like to join them altogether with an additional field that shows the name of the table it came from.

is there an easy way to do this, nothing springs to mind right now, though its been a while!

Thanks for any help.

not a good practice to have separate tables for each partnumber.

you will need to use Union Query
write down or identify each table name (the bom)

select "theTableName1Here" As

Not sure why you need the name of the original table as after merging you should delete all of the original tables.
Anyway, make a new table with the same fields, a table name field (if you want this) and an autonumber primary key field. This can be in your current database or an external database (if the latter, link the table to your Access dB)
Create a union query with each table fields and add the table info


Save your query.
Now make an APPEND query to insert all the fields from your union query into your new table

Agreed very wrong to have a table for every part, pretty much everything in this DB is in single tables!

Ah thanks guys this seems to be working out well

Only issue im having is tables that have a - in the name (ie)
1600XLV-24V

I get the error can not find '1600XLV' Its not a big deal though i can do these manually.

the reason for having the table name in the field is that this identifies which bom the part is related to.

thanks again for your help guys

enclosed them in squre brackets:

select "1600XLV-24V" As

oh perfect thanks

Just to be clear, there is an inherent problem in having any special characters in the name of something structural. (I.e. a table, field, query, form, etc.) You are acting to get rid of the multiple tables that can be merged. So that is actually very good. It will not matter if the CONTENT of a table contains special characters - but try very hard to prevent the use of special characters in object names. If you can do that, you won't need the bracketing. Further, looking to the future, if you EVER have to migrate to some other DB system, they might not accept the object names with special characters.

Consider this as recognition of generally going in the right direction and just nudging your course just a little bit. By the way, you might not recognize this because your post indicates that you are either a novice or very rusty. What you are doing is one of the many steps involved in normalizing a database. If you are not familiar with that term, you should do some reading up-front so you can try to avoid other mistakes.

If searching this forum, search for normalization. The search option is in the thin ribbon near the top of each page.

If searching the open web, search for "database normalization" because using "normalization" by itself, you might also run into political, diplomatic, chemical, and mathematical normalization. In that case, when picking articles on the topic, you might limit your initial reading to .EDU sites because of less advertising and more orientation to learning.

Hi The_Doc_Man thank you for chiming in!

Well, its a bit of both.
I was a novice learning access for work, but after a few months the company decided to move to a new ERP system. Using PSQL. I pretty much dropped any further Access research as it wouldn't be needed.
So I'd say I'm a rusty novice !

I do want to make it clear, I didn't make this db with the tables and naming conventions. One of the first things I learned in access is properly naming objects. It was done by one of the founders of this company some ten+ years ago. Incidentally his parting words to me regarding access was, "you might see somethings in there that dont make sense, but it works" great!

I'm simply trying to get the data into a sensible state so it can be imported into PSQL. Just as you eluded to in your post.


i think I've hit a snag after 50 lines in the union qry i get
qry too complex. i guess I'll have to do it in parts!
Only 150 tables to go

Probably too far along. Another idea would have been to use vba. Create a table or query containing the list of tablenames (maybe can get from msysobjects) then have a recordset loop for each table name that will run an append query containing the standard field names of what will be the merged/unioned table and use variables for the table name to pass to it.

Not Tested...I copied the sql from Arnel's example, so that definitely would need editing to run and not sure if I put the brackets in the right spot. Something like: