Combining two tables with no matching field name

[Miche]

I am working with two tables that have the same information but the field
names are not the same, some examples are Ship To/ Customer, Ship to Address/
Customer Address, Market/ Branch. I need to bring both tables into a single
table. This will be an ongoing process so I want to avoid having to change
the field names in one of the spreadsheets to match the other before each
time I import the data. How can I tell Access that one table’s field is the
same as another table’s field? I will use the append to bring both tables
together once I establish a link.

 

[Douglas J. Steele]

Create linked tables to the spreadsheets.

You can create queries based on the linked tables and assign aliases to any
(or all) of the fields. Alternatively, if you're putting the data in a new
table, your Append query would simply indicate the names of the from fields
and the names of the to fields.

 

[Miche]

This will not work since my source data constantly changes monthly. A new
spreadsheet is created for each month because of the number of rows excel can
have. I need to keep the existing table and append the new month’s data in
access.

 

[Douglas J. Steele]

So each month, you delete the existing linked table and link to the new
spreadsheet, naming the linked table the same as the previous month so that
your append queries still work.

 

[Miche]

That is correct.

So each month, you delete the existing linked table and link to the new
spreadsheet, naming the linked table the same as the previous month so that
your append queries still work.

 

[Douglas J. Steele]

Um, what I was suggesting was the solution to your problem.

You have a linked table with a known name.

You have an append query that maps the fields from the linked table to the
fields in the "real" table.

How does that not meet your requirements?

 

[Miche]

Appending is not my issue.
My issue is I have two tables with alot of information in them that I want
to combine. These tables were spreadsheets are from two different
applications that have the same information but the field names are
different. I am looking for a way to combine these two tables into one by
telling Access that example- Customer is the same as Ship To and should
combine the two into Customer in my new table. Customer Address is the same
as Ship To Address and should combine the two into Customer Address in my new
table and so on.

I think Douglas is saying you would delete the monthly table and link to the
new spreadsheet using a table of the same name. Last month's data has been
appended from the monthly table to the main table, so there is no need to
keep that table. Since the monthly table has the same name each month, the
append query will continue to work.

That is correct.

So each month, you delete the existing linked table and link to the new
spreadsheet, naming the linked table the same as the previous month so that

[quoted text clipped - 31 lines]

tables
together once I establish a link.

 

[Douglas J. Steele]

Based on what I'm understanding, you should have a linked table pointing to
a spreadsheet and one of the fields in that spreadsheet is named Ship To
Address and a query that takes the data from that linked table and appends
it to another table. In that append query, you tell Access to append the
value from the field named Ship To Address to the field named Customer
Address.

Am I misunderstanding the situation?

Even if you're not trying to append to a table, but instead are trying to
Union together the two linked tables and you want to control the names of
the fields, you can use aliases in your Union query.

SELECT FieldA AS F1, FieldC AS F2, FieldD AS F2
FROM LinkedTable1
UNION
SELECT Field1 AS F1, Field4 AS F2, Field6 AS F3
FROM LinkedTable2

will result in a recordset with fields named F1, F2, F3, regardless of what
the field is named in the original table.

In actual fact, you don't even need the aliases in the second part of the
query, since Union queries get their field names from the first subselect.
In other words, the follow SQL is all you really need:

SELECT FieldA AS F1, FieldC AS F2, FieldD AS F2
FROM LinkedTable1
UNION
SELECT Field1, Field4, Field6
FROM LinkedTable2

If your query was

SELECT FieldA, FieldC, FieldD
FROM LinkedTable1
UNION
SELECT Field1, Field4, Field6
FROM LinkedTable2

then the resultant recordset would have fields FieldA, FieldC, FieldD, even
though some of the rows would actually have called the fields Field1, Field4
and Field6.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Appending is not my issue.
My issue is I have two tables with alot of information in them that I want
to combine. These tables were spreadsheets are from two different
applications that have the same information but the field names are
different. I am looking for a way to combine these two tables into one by
telling Access that example- Customer is the same as Ship To and should
combine the two into Customer in my new table. Customer Address is the
same
as Ship To Address and should combine the two into Customer Address in my
new
table and so on.

I think Douglas is saying you would delete the monthly table and link to
the
new spreadsheet using a table of the same name. Last month's data has
been
appended from the monthly table to the main table, so there is no need to
keep that table. Since the monthly table has the same name each month,
the
append query will continue to work.

That is correct.

So each month, you delete the existing linked table and link to the
new
spreadsheet, naming the linked table the same as the previous month so
that
[quoted text clipped - 31 lines]
tables
together once I establish a link.

 

[Frank H]

It seems you might want to go read up on append queries. It really sounds
like what you are asking for is just what an append query will do. So, unless
you can frame your question differently, you will probably keep getting the
same answers.

Apologies if this is repetitive: when you change the select query to an
append query, you get another row in the design grid. The top row names the
field in the table you are pulling the information FROM (i.e, the linked
table which is reading the excel spreadsheet columns). The lower row is the
name of the field INTO which you want to append the data. So, it appears as
if this is how you should get the fields "mapped" onto each other. Each
excel spreadsheet would use a separate append query to get the proper fields
into the "native" table's proper fields.

I hope that helps.
--
Frank H
Rockford, IL

Appending is not my issue.
My issue is I have two tables with alot of information in them that I want
to combine. These tables were spreadsheets are from two different
applications that have the same information but the field names are
different. I am looking for a way to combine these two tables into one by
telling Access that example- Customer is the same as Ship To and should
combine the two into Customer in my new table. Customer Address is the same
as Ship To Address and should combine the two into Customer Address in my new
table and so on.

I think Douglas is saying you would delete the monthly table and link to the
new spreadsheet using a table of the same name. Last month's data has been
appended from the monthly table to the main table, so there is no need to
keep that table. Since the monthly table has the same name each month, the
append query will continue to work.

That is correct.

So each month, you delete the existing linked table and link to the new
spreadsheet, naming the linked table the same as the previous month so that
[quoted text clipped - 31 lines]
tables
together once I establish a link.