Merge tables - help

[prabhu]

Hello,

I have two different tables from different users and i want to merge that in to single table. Fields in both the tables are same and only the velues are different so i want to merge this in to one. Users update the data daily. I used append Query Option. But when i run the append query everyday its mutiplying the data.( for example today A =1 nextday when i run append query A=2 simply duplicating the value and adding ) I may be wrong in using append query. Please suggest me to use the best way.

 

[JamesMcS]

Have you specified a date range in your append query? If the users are constantly updating the same table, you'll just end up appending the same data over and over... unless you specify a date.

 

[Lightwave]

Re-write

What you might not know is that within access (probably within all databases come to think of it) a field marked as the primary means that it must be unique. There can be no duplicate values.

This means that if you are tranferring one table into another table the receiving table will only accept values within the primary field that are unique.

This can be useful when you want to strip out duplicate values. I created a database for a friends athletic company and they have many runners passing matts but all these runners. Because the system is highly sensitive they will get multiple readings of the same time. Accurate to tenths of seconds anyway. Obviously I only need one time everytime they come over the matt. Thus importing these reads into a table with the primary key set to time means that all of the duplicates are simply ignored (a warning message comes up but that can be turned off)

So in your example you need to make sure that there is a unique value for each table which is separate between tables but not necessarily within the table.

See next post for example

 

[Lightwave]

Here's an example proof of principle where I've created an unique index from two tables (A & B) and concatenated into a third append table. When you use the append queries to put the records into the new table it is important to make the primary key of the receiving table as the compound field.

I'm not sure if this is the best way but it what comes immediately to mind.

Access 2003

 

[JamesMcS]

So it's not to do with the date then?

 

[Lightwave]

I think you could do something with the date - making it into some kind of primary key. Just thought there's a chance that people in independent tables might enter things at exactly the same time. Therefore date by itself couldn't be a primary key so you still have to combine it with a different field.

 

[JamesMcS]

Good point, well made, perhaps a userID or something would be good there.

 

[prabhu]

Thank you all.

source tables which i am using are ODBC tables, so i think i cant give the primary keyto any of the fields... how can i do this...

 

[Lightwave]

Shouldn't necessarily matter because you don't want to change the primary key of the source tables. Its the primary key of a field in the table you are importing things into that you will need to change. Depending on the structure you should then be able to get it into your other table.

So you will need to make two (or more queries) for each of the tables you are taking information from and make sure you create a unique field. These queries are then incorporated into two further append queries that are consecutively fired. These take the information from the tables and place in the appended table..

This is exactly what was done in the attached database. Carefully study within design view how I have created the compound key in the first two queries and how in the second two queries I append the one source field into a compound field that has the primary focus.