Mapping Fields

[chewbears]

I have 2 tables with different fields. The fields as an example are as follows.



Table 1

Name
Address
Phone

Table 2

Person
Location
Cell

I would like to merge the tables by mapping the fields.

How can this be accomplished. I know that if I wanted to export them. I could have an Export SQL expression to do this, but how can I maintain it in access?

[alansidman]

You could merge the two tables with a Union Query.

Select *
From Table1
 
Union
 
Select *
From Table 2

This will work so long as there are the same number of fields in each table.

[chewbears]

Thank you for the response, but while my generic example may over simplify things, the tables are very different. They hold from 30-75 fields, and in all cases will never have the exact number of fields, or always have a corresponding fields.

[alansidman]

A union query is still doable but will require a little bit of preparation. For those tables that don't have a corresponding field, type in a new field in the same location (on the QBE grid) the name of the correponding field in the table that does have a field followed by a colon and 2 double quotation marks. ie. (NewField: ""). Do this for all the fields. Save the query as a select query. for the other table create a select query for all fields. Save it.

Open a new query. Open it in SQL format. Open the select queries in SQL. Copy the SQL statements from each of the select queries and paste it to the new query. Insert the Union between the two SQL statements.

Good Luck.

[chewbears]

Is there then no template mapping for fields you can do in Access? I am very surprised, as I know for previous versions, there were quite a few field mapping 3rd party softwares developed. Will go ahead with your suggestion, but ultimately I can do this method when exporting to excel, so it really is another version of an existing solution. Will tell you how it goes. Additionally this forces me to always have order in the fields position otherwise they will just merge consecutively. I would much rather be able to say (in code) Field1 = FieldOne on in the real work Closed_ticket = Status_none

[alansidman]

I am not aware of a mapping procedure in Access. Perhaps you could try using an Append query.

http://office.microsoft.com/en-us/ac...010076526.aspx

Alan

[chewbears]

thanks for the tip. Will check the excel forums to see if they know anything : P. Otherwise I will just keep things as they are.