I need to join four MS Access tables into one

[Sherman6789]

Hi,

I have four MS Access tables in my database that need to be joined into one. They each have many of the same fields. Each also has fields that are unique to its table. I have created a table with all of the fields. When they are joined, I would like for the records to be auto-numbered. I understand that many records will have blanks because the field is N/A to that type of record. Each record and the fields are unique, therefore additional tables maynot not be needed.

 

[lespaul]

create a union query:

SELECT Field1, Field2, Field3, Field4, FieldOnlyInThisTable FROM Table1
UNION
SELECT FieldJustinThisTable, Field2, Field3, Field4, "" From Table2
UNION
SELECT Field1, "", FieldOnlyInThisTable, field4, Field5 FROM Table3
etc.

you just need to make sure there are the same number of fields in the select statements of each of the queries.

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Sherman6789]

Thanks Leslie,

You said to make sure there are the same number of fields in the select statement of each of the queries. That may be a problem. Each table is different in some ways. The like fields are identical. Each table has from one to five fields that are not in the other tables. All of the unique field names are in the combined table and they may not be in the exact order. See example below:

combined table has these fields:

A B C D E F G H I J K L M N


Table A has these fields

A B C G I J K

Table B has these fields

A B D E K M N

Table C has these fields

A C F H L M N

Table D has these fiels

A B E F G

This entire example was make up to try to keep it as simple as possible. Field A is what is common to all of them. The information is separated by the ID in Field A.

Also, where do I put the statement that will combine the tables?

Any assistance you can give will be appreciated.

 

[lespaul]

Create a new query, switch to SQL view and then enter the Union query:

SELECT A, B, C, "", "", "", G, "", I, J, K, "", "", "" FRom TableA
UNION
SELECT A, B, "", D, E, "", "", "", "", "", K, "", M, N From TableB
UNION
SELECT A, "", C, "", "", F, "", H, "", "", "", L, M, N From tableC
UNION
SELECT A, B, "", "", E, F, G, "", "", "", "", "", "", "" From TableD

save that query and then use it as the source:

INSERT INTO NewTable (SELECT * FROM qryUnionQuery)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Sherman6789]

Thanks again Leslie,

I'll try this as soon as I find how to get to the SQL view. Is this a better way than using the "append" procedures? I always thought that append did not have to be in any particular order and the program looks for matching field names rather than positions. Therefore, "" would not be necessary to hold spaces. Is this true?

 

[lespaul]

From the Query Design grid, select View -> SQL;

An 'Append' query uses the INSERT INTO format, which means that, yes, you would still need every field OR you have to qualify each one and run four individual queries:

INSERT INTO NewTable (A B C G I J K) Values (SELECT * FROM TableA)

INSERT INTO NewTable (A B D E K M N) Values (SELECT * FROM TableB)

leslie