Merge info

[travismp]

I have 3 tables: tbl_1, tbl_2, tbl_3
All 3 have the same 3 columns: name, email, code

Can I write some queries or a code or something that can create one "tbl_MASTER" with the same 3 columns?

Ex:
tbl_1 - 3 records
tbl_2 - 7 records
tbl_3 - 21 records

tbl_MASTER - 31 records. Now the information in the other 3 tables changes everyday so I want it to all be automatic, I dont want to execute 3 different queries or anything like that. Is this doable. Thanks.

Travis

 

[carash77ash]

what is the point of having 3 tables with the same information

Ash

 

[travismp]

There is different information in each, but the same format. Sorry if I worded it incorrectly

 

[carash77ash]

How are the tables related to each other?

Ash

 

[travismp]

I have 3 different internal databases that track customers. I want to create one "master" database with ALL of my clients.

I have a creat-table query in each one of the 3 that send "name, email, code" to a new database. So now I have this new database with 3 tables. Now I want to find a way to Merge the data from those 3 into one Main table.

This is something that will change every couple days for new clients we add to the 3 internal databases.

 

[john471]

Yes but why have three tables with the same structure ??? Are they three seperate external data sources ?
Do you want to delete the current contents of tbl_master before adding the contents of tbl_1, tbl_2 and tbl_3 ?

You could write a union query to bring the data from the three tables together, then write a single append query to append the combined data, which would mean you only need to "run" one query, unless you want to delete the existing contents first, in which case you would need a seperate delete query to run first....

qryCombineData
---------------
SELECT tbl_1.name, tbl_1.email, tbl_1.code
FROM tbl_1
UNION SELECT tbl_2.name, tbl_2.email, tbl_2.code
FROM tbl_2
UNION SELECT tbl_3.name, tbl_3.email, tbl_3.code
FROM tbl_3;


qryAppendData
---------------
INSERT INTO tbl_MASTER ( name, email, code )
SELECT qryCombineData.name, qryCombineData.email, qryCombineData.code
FROM qryCombineData;

But the very real design question remains of; why do you have three tables with the same structure ?

 

[john471]

OK, so it seems you have answered the design question in the same minute that I posted my above reply. Does the other content of my reply above help you out at all?

 

[travismp]

I think these will work. If I am reading them correctly it should do the trick. I will try to configure these. I have never done this stuff before... I take it I just create 2 different queries and enter your text as is into SQL view?

Thank you so far. If I have follow up questions I will enter more. Thanks.

Travis

 

[travismp]

The "qryCombineData" worked great for what I needed to see. Thank you.

 

[carash77ash]

Just a thought but when you send information from one db to the other why not just use an append table query form each db to update master on new db. If it is all the same structure then it should work. Instead you are copying data first and then appending three tables.

Does this make sense?

Ash