Hi Everyone

I'm trying to merge two tables which are links from other databases to form one single query in a third database. The problem comes when I try to link the data, because both tables have the same structure but different data. So the field names are identical in both tables.

So when I try to create a query, it asks for which table the data should come from. But I want it to come from both tables.

Is their anyway I can do this using a access query.

Thanks.

Hi,

   I would do this with VBA if it has to be dynamic.

   Create a table that has all the fields from both tables in it. If they are the same, then just copy one table structure and leave it empty.

   Then, using a button on a Form or Report event, do the following:

Empty all the data in the table.
Call the first database, and copy all the data into the table, minus the primary key.
Call the second database and copy all the data into the table minus the primary key.
Open the form or report.

This may require two System DSN's, but once you write code for the first table, you already have the code for the second table.

If you just want to do this once to consolidate the tables into one database, then create the table, and do an Insert>Table>Import Table, and import all the data from each table, minus the primary key column, into the existing table.

HTH


mmcdonal

I'm not clear what you're trying to do. It sounds like you want all of the records from both tables. If that is the case, what you want is a union query.

Since both tables are identical, create a standard select query for one of the tables. Change to SQL view of that query and you'll see the select statement. Something like,

SELECT tbl1.fld1, tbl1.fld2 FROM tbl1;

I would start by removing all of the "tbl1." from the fields. You won't need them.
Copy all of the text (except the semicolon at the end).
Change the Query type to "SQL Specific>" "Union" (or simply type UNION before the semicolon).
Paste the copied text and replace the tbl1 with tbl2 in the FROM. This is why I recommended removing all the "tbl1.". If you didn't you have to change all of the "tbl1." with "tbl2.".

This will give you all the records from both tables in no particular order.

Save the query and you can treat that query as if it was a single table in your third database. If data is updated in either of the other databases your query will automatically pick up the change.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Thanks for the help guys I'll try both methods and see which is quicker. I was trying to avoid creating a new table in the database, since I don't really wont the database to grow too large.

But I like the idea of trying to use a UNION query to join to tables of data together. And yes the table would need to be updated, since new data is put into the other database's every few hours.

Yikes,

  This gets more complicated every time I look at it. Did you need to maintain the primary keys in each of the two tables for look up purposes? Is there anyway you can rename the tables in the primary databases to distinguish them?

   Just curious, why two identical database tables that are being updated independently?

Thanks,


mmcdonal

Didn't need to maintain the primary keys in the tables, luckly both tables were called different things in the two databases.

As for the reason, well it's mainly because the two databases do tottally different things but at the end of the day produce the same structure of data. The third database needs the data to calcuate results, but doesn't care which method was used to collect the data.

But anyway think I've sorted it. I cdreated a union query that joined the two tables together, then create a basic select query so that I can put my extra calculations in. So far so good, it appears to be working.

Once again, thanks for the help guys.

Just needed somebody to push me in the right direction.