Use Table1 to update Table2? Urg Help needed

[munkifisht]

I have 2 tables, I will simplify the situation. Say I have table1, it has the fields "ID", "Tag", "Date", and table2 and the fields "ID" "TAG". Table1 contains an old version of the dataset, and table2 is updated every so often.

I want to do two things. First, I want to update Table1 with the new dataset on the either automatically or with the click of a mouse with the data that is in Table2.

The other thing I want to do is I want to check that the data in Table1 is not unique, ie, if a value exists in table1 and not in table2, I want it removed, but I want it moved to a table called Dump so it can be reviewed before it is removed.

Take this as an example

Table1 "Tag" dataset
Tag1
Tag2
Tag3
Tag4

Table2 "Tag" dataset
Tag1
Tag2
Tag4
Tag5

This will become

Table1 "Tag" dataset
Tag1
Tag2
Tag4
Tag5

Dump "Tag" dataset
Tag3

 

[DrSnuggles]

Ok. Here we go:

1. Create a column in Table1 and name it 'Delete' and set to field type 'Yes/No'.

2. Create a column in Dump and name it 'Deleted' and set to field type 'Yes/No'.

3. To Update Table1 with new Table2 data. Copy the SQL below in to a new SQL Window, also add in more fields where needed. Save and Run:
INSERT INTO Table1 ( ID, Tag )
SELECT Table2.ID, Table2.Tag
FROM Table2 LEFT JOIN Table1 ON Table2.Tag = Table1.Tag
WHERE (((Table1.Tag) Is Null));

4. To Update Dump Table with obselete data in Table1 (not in Table2). Copy the SQL below in to a new SQL Window, also add in more fields where needed. Save and Run:
INSERT INTO Dump ( ID, Tag )
SELECT Table1.ID, Table1.Tag
FROM Table1 LEFT JOIN Table2 ON Table1.Tag = Table2.Tag
WHERE (((Table2.Tag) Is Null));

5. Review Dump table. Tick the Deleted field for the rows you want to delete.

6.a Delete obselete data from Table1. Copy the SQL below in to a new SQL Window. Save and Run:
UPDATE Dump INNER JOIN Table1 ON Dump.Tag = Table1.Tag SET Table1.[Delete] = True
WHERE (((Dump.Deleted)=True));
6.b. Copy the SQL below in to a new SQL Window, also add in more fields where needed. Save and Run:
DELETE Table1.*, Table1.Delete
FROM Table1
WHERE (((Table1.Delete)=True));

NOTE:
1. You have to be careful about all your names of tables and fields. Otherwise you'll get errors.
2. Have a look at the Queries in the Design View too before executing as this will give you an idea of what is going on. Plus as you do, if you have named anything incorrectly it with tell you which object.
3. Make certain to add more fields in where necessary as you may lose data.
4. I'm using the TAG field as the Primary Key. You should really use the ID.

Hope that helps and Good Luck!

 

[munkifisht]

Ok. Here we go:

1. Create a column in Table1 and name it 'Delete' and set to field type 'Yes/No'.

2. Create a column in Dump and name it 'Deleted' and set to field type 'Yes/No'.

3. To Update Table1 with new Table2 data. Copy the SQL below in to a new SQL Window, also add in more fields where needed. Save and Run:
INSERT INTO Table1 ( ID, Tag )
SELECT Table2.ID, Table2.Tag
FROM Table2 LEFT JOIN Table1 ON Table2.Tag = Table1.Tag
WHERE (((Table1.Tag) Is Null));

4. To Update Dump Table with obselete data in Table1 (not in Table2). Copy the SQL below in to a new SQL Window, also add in more fields where needed. Save and Run:
INSERT INTO Dump ( ID, Tag )
SELECT Table1.ID, Table1.Tag
FROM Table1 LEFT JOIN Table2 ON Table1.Tag = Table2.Tag
WHERE (((Table2.Tag) Is Null));

5. Review Dump table. Tick the Deleted field for the rows you want to delete.

6.a Delete obselete data from Table1. Copy the SQL below in to a new SQL Window. Save and Run:
UPDATE Dump INNER JOIN Table1 ON Dump.Tag = Table1.Tag SET Table1.[Delete] = True
WHERE (((Dump.Deleted)=True));
6.b. Copy the SQL below in to a new SQL Window, also add in more fields where needed. Save and Run:
DELETE Table1.*, Table1.Delete
FROM Table1
WHERE (((Table1.Delete)=True));

NOTE:
1. You have to be careful about all your names of tables and fields. Otherwise you'll get errors.
2. Have a look at the Queries in the Design View too before executing as this will give you an idea of what is going on. Plus as you do, if you have named anything incorrectly it with tell you which object.
3. Make certain to add more fields in where necessary as you may lose data.
4. I'm using the TAG field as the Primary Key. You should really use the ID.

Hope that helps and Good Luck!

Great reply, thanks DrSungges, I have asked in a few different forums, but this is the only answer that did what I hoped it would, assume that I am a complete newb. I will give it a try.