Merge Tables

[cfobare79]

It there a way to merge information from one table into another? I thought creating a relationship would make this connection.

[Perceptus]

You could use a Union All Query.

Select * from Table1
Union All
Select * from Table2

Both tables need the same number of fields.

[ItsMe]

Creating a relationship in the relationship window will not make this happen. Perhaps a query will suffice. You can create a query using the Query Designer. While there, create a JOIN. This will cause the query to retrieve data that is relevant from two or more tables.

And yes, it is possible to merge two tables into one. The end result would be one table.

[cfobare79]

Is there a way to merge data from one table into another. I believe the queries above would create a new table.

[Perceptus]

You could make an append query to append all data from one table into the other.

Queries do not make a new table.

[June7]

Can do an INSERT SELECT sql action to copy records from one table to another or do copy/paste.

Why do you need to do this? Is this a one-time event?

What exactly are you trying to do? Do you want to UNION or JOIN datasets?

[cfobare79]

This issue I am dealing with is I receive an Excel sheet with billing information. The Excel only has the AgencyName and other billing infomation. All my Reports are ran using an AgancyID. I was thinking if I imported the Excel sheet into a table which had a column for AgancyID that the AgancyTable would be able to identify the AgencyName and add the AgencyID. Any suggestions would be wonderful.

[Perceptus]

If you can do this by hand without editing large amounts of data, you should be able to make it happen 'automagically' Your thoughts sound correct.

I would make a query which prepares the data that you imported from excel. In this query you would resolve what AgencyName should be based off the Agencies table.

then have that data appended to the 2nd table.

[June7]

This all depends on confidence that the names are always spelled correctly in the Excel file. Any variation in name and record will not match to the ID.

How complex is the Excel sheet structure? Can you set a link to it?

[cfobare79]

Sorry, I can't let you see the sheet because of the information on it. I'm very confident the names will be correct since it is computer generated. basicly what I want to do is a DLookup function within access.

[June7]

Computer generated or not, someone somewhere sometime entered data into a database. Names are often unreliable key identifiers.

[cfobare79]

I think that append query should work but I can't get it to function properly. This is the query I'm running. I know this sounds backward from what I was asking but it's because this is test data and has AgencyID but not AgencyName in the 'David" file.

[June7]

First you need to do a query that joins AgencyList to the imported (or linked) Excel records then use that dataset as source for the INSERT. An all-in-one SQL statement would be like:

INSERT INTO datatablename(AgencyID, {other fields as needed}) SELECT AgencyID, {other fields as needed} FROM AgencyList INNER JOIN ExcelImport ON AgencyList.AgencyName = ExcelImport.AgencyName

[cfobare79]

I was going to import the excel prior to doing the query. I'm sure the query would be similar but it sounds like you are telling me I cant do an Append Query.

[June7]

That is an append query.

What table do you want these new records appended to?