How to combine two structuraly identical tables to one?

[Television]

Greetings!

I need to combine two tables that have identical structure to one table. Different people have used them to insert data from different parts of our project. I have tried to find information about how to do this but so far with little luck. What would be best (or even workable) solution to this?

Thank in advance!

Television

 

[CJ_London]

You haven't said whether this is a one of exercise or not and whether you need to avoid duplicates when combining the data. Also, do both tables have an autonumber field which needs to remain with the same values from both tables (e.g. because they are used as a parentID value in another table).

If it is a one off exercise and and duplicates/autonumbers are not an issue you can open Table1, highlight all the records and copy, then open table2, highlight the new record line at the bottom and then paste.

Alternativley this query will do the same thing:

INSERT INTO Table2
SELECT Table1.*
FROM Table1;

 

[Television]

Greetings.
I suppose I was not clear enough. Sorry about that.
Its a one-off job and auto-numbers are not a problem. Data in both tables are based on same mastertable's data and both contain different modifications and additional records (different versions so to speak), so simple append doesn't work. I need a way to identify changes and then merge these tables in to one table that doesn't contain duplicates and contains all the changes and added records.
Thank you!

You haven't said whether this is a one of exercise or not and whether you need to avoid duplicates when combining the data. Also, do both tables have an autonumber field which needs to remain with the same values from both tables (e.g. because they are used as a parentID value in another table).

If it is a one off exercise and and duplicates/autonumbers are not an issue you can open Table1, highlight all the records and copy, then open table2, highlight the new record line at the bottom and then paste.

Alternativley this query will do the same thing:

INSERT INTO Table2
SELECT Table1.*
FROM Table1;

 

[CJ_London]

combine two tables that have identical structure

So this isn't what you have? It would help if you can post the structure of each table so I can understand better. Post like this, using the names you actually have plus further explanation of the field if required (e.g. must be unique)

Table1
fld1Name text
fld2Name date
fld3Name Long Unique
etc

Table2
fld1Name text
fld2Name date
fld3Name Long
etc

 

[Television]

Both tables are structuraly identical (fields have same names, datatypes, lengths etc...). Originaly both contained same information. My users have modified some of the original records (but not the same ones thankfully) and added new different records in their own version of database. Now I need to identify changed and added records and combine them back to one table together with original unmodified records. I actually have several similar cases, but once I know how to do one I can do the rest.
Below is a structure of first table:

[FONT=&quot]ID[/FONT][FONT=&quot] (Autonumber): [/FONT][FONT=&quot] Primary key[/FONT]
[FONT=&quot]CompositeFeature (Text): [/FONT][FONT=&quot] Unique, indexed[/FONT]
[FONT=&quot]Comments[/FONT][FONT=&quot] (Text)[/FONT]
[FONT=&quot]Room[/FONT][FONT=&quot] (Number) [/FONT]
[FONT=&quot]RoomID ([/FONT][FONT=&quot]Number): Foreign key[/FONT]
[FONT=&quot]Trench[/FONT][FONT=&quot] (Text) [/FONT]
[FONT=&quot]Type[/FONT][FONT=&quot] (Text) [/FONT]
[FONT=&quot]Loci [/FONT][FONT=&quot](Memo) [/FONT]
[FONT=&quot]AssociatedCompositeFeatures[/FONT][FONT=&quot] (Text) [/FONT]
[FONT=&quot]DimensionsWidth[/FONT][FONT=&quot] (Text)[/FONT]
[FONT=&quot]DimensionsHeight[/FONT][FONT=&quot] (Text) [/FONT]
[FONT=&quot]DimensionsLength[/FONT][FONT=&quot] (Text) [/FONT]
[FONT=&quot]DimensionsThickness[/FONT][FONT=&quot] (Text) [/FONT]
[FONT=&quot]Discussion ([/FONT][FONT=&quot]Memo) [/FONT]
[FONT=&quot]Phasing[/FONT][FONT=&quot] (Memo)[/FONT]
[FONT=&quot]Interpretation[/FONT][FONT=&quot] (Memo) [/FONT]
[FONT=&quot]Earlier Documentation[/FONT][FONT=&quot] (Text) [/FONT]
[FONT=&quot]Sketch[/FONT][FONT=&quot] (Text) [/FONT]
[FONT=&quot]RoomFloorAltitude[/FONT][FONT=&quot] (Text) [/FONT]
[FONT=&quot]PhotoNumbers [/FONT][FONT=&quot](Memo) [/FONT]
[FONT=&quot]FieldRecording[/FONT][FONT=&quot] (Text) [/FONT]
[FONT=&quot]RecordDate[/FONT][FONT=&quot] (Date)[/FONT]
[FONT=&quot]DataEntry[/FONT][FONT=&quot] (Text) [/FONT]
[FONT=&quot]EntryDate[/FONT][FONT=&quot] (Date) [/FONT]

Sorry about the length. Hope this makes it clearer.

Thank you!
Television

 

[CJ_London]

So are you saying one table is unchanged and the other was originally identical but over time new records have been added and original records have been changed.

Or are you saying they both started out identical and both have had new records added and original records changed?

 

[Television]

So are you saying one table is unchanged and the other was originally identical but over time new records have been added and original records have been changed.

Or are you saying they both started out identical and both have had new records added and original records changed?

Both started identical and to both were made changes and records added.

 

[CJ_London]

OK, sorry, just realised you had answered part of that already

Anyway to summarise. I've put in red what needs to be identified

We'll take Table1 as the original (which now includes new records and changes to original records so we don't need to do anything about that)

From table2 we need to add new records - at the moment these will have the same autonumber as a new record added in table1 so we need to be able to identify which these are since they will be allocated a new autonumber and we don't want to treat it as an 'update' to an 'original' record in table1 which is actually a new record. Can you tell me how to identify new records in table2 - perhaps one of the fields at the bottom?
RecordDate[FONT=&quot] (Date) - everything after this date?[/FONT]
[FONT=&quot]DataEntry[/FONT][FONT=&quot] (Text) - some sort of flag?[/FONT]
[FONT=&quot]EntryDate[/FONT][FONT=&quot] (Date) - everything after this date?
[/FONT]
Once we have identified which autonumber is a new record in table2, all preceding records are 'original' and can be compared with table1 to identify changes.

The alternative is to use CompositeFeature but this will only be viable if a) the data hasn't been changed and b) the same entry has not been added to both tables

Also just to confirm, autonumber is incremental and not random?

 

[Television]

[FONT=&quot]I think that EntryDate would be best candidate to identify new records. Anything added since beginning of July 2013 are new ones.[/FONT]
There are still some duplicates in CompositeFeature-field (these are old tables that several users have modified in different point of time and some of them were quite careless) at this point although they are weeded out eventually.
Autonumbers are incremental. In original tables there were no ID-field so I added them (old tables were made by person who was not so IT savvy).
Thank you for your help!
Television

 

[CJ_London]

In original tables there were no ID-field so I added them

So to confirm, both tables have the same autoID number for the same original record - important because we need to use it to identify 'matching' records, particulary if CompositeFeature is not actually unique!

 

[Television]

It looks that autonumber could be used to find additions, but CompositeFeature might be better since I am not 100% sure that autonumbers are same. It seems that CompositeFeature is actually unique (must have thinking other table). Find duplicate-query I just ran confirmed that.

 

[CJ_London]

Ok, take a copy of table1 so you can restore if required.

You'll need two queries, the first to append changes in table2 to original data. The query needs to be along these lines, you'll need to change names to suit:

UPDATE Table1 INNER JOIN Table2 ON Table1.CompositeFeature = Table2.CompositeFeature SET 
Table1.Comments=Table2.Comments, 
Table1.Room = Table2.Room, 
Table1.RoomID=Table2.RoomID,
[COLOR=red]etc[/COLOR]
WHERE (
Table1.Comments<>Table2.Comments 
OR Table1.Room <> Table2.Room 
OR Table1.RoomID<>Table2.RoomID
[COLOR=red]etc[/COLOR]
) AND ENTRYDATE<#07/01/2013#

The second query is to append new records

INSERT INTO Table1 ( CompositeFeature, Comments, Room, RoomID, [COLOR=red]etc[/COLOR] )
SELECT 
Table2.CompositeFeature, 
Table2.Comments, 
Table2.Room, 
Table2.RoomID, 
[COLOR=red]etc[/COLOR]
FROM Table2 LEFT JOIN Table1 ON Table1.CompositeFeature = Table2.CompositeFeature
WHERE Table1.CompositeFeature Is Null AND table2.ENTRYDATE>=#07/01/2013#

 

[Television]

Thanks! Have to try this tomorrow at work.
Television

Edit: Worked as it should! Thank you again!

 

[Kijindoragon]

I'm sorry, I know this is flagged as solved, but I have a similar issue and I'm still a little lost...
I have a master table and two other tables with identical fields. The secondary tables are updated weekly, and I want to add them to the Primary table quarterly. The primary table will keep all historical data.
I don't currently have a primary key but one field is locked to disallow duplicate entries. I also have "date reviewed" and "date of assignment" fields to work with.
Was your explanation for use in the SQL version of the editor? Some of my fields (as well as tables) have spaces in the name. Does that matter?
Field types include:
Date
Dropdown/lookup
Number
Yes/no box

The one time I thought I had it right it said something about duplicate entries to "date" and I have no clue what that means...

Sorry to reopen this or if it's in the wrong place... I'm new to posting on forums.