COMPARING AND UPDATING TABLE FROM ANOTHER TABLE 2

[drslade]

OK...here is my problem.

I have an excel spreadsheet that contains the data that I want to use to update my current table with. I have created a macro to import the data into a table called temp. This macro works fine and imports the data for me. Now the hard part.

I want to take the temp table and compare it to a table called Unit To Do List. I want the table to compare the name field (each table has a field called Name) and update the unit to do list table according to the following rules:

If a name is in the Unit To Do List and is not in the Temp table, delete the record.

If a name is in the Unit To Do List and it is in the Temp table I want it to compare the values in the following fields and update if needed:
Comapre Unit with Unit
Compare Rank/Rate with Rank/Rate

If a name is not in the Unit to do list, but is in the Temp table I want the record added to the Unit to do list table.

I am sure that this will have to be done in code (at least I think). I have not done any coding, just now getting into that. If someone could help me out I would greatly appreciate it.

Thanks

David

 

[kraxmo]

Assuming that the Name column is unique in both tables, you can accomplish this by creating 3 action queries.

Query #1: Delete all unmatched Unit To Do List rows.

DELETE * FROM [Unit To Do List] 
WHERE Name NOT IN (SELECT Name FROM Temp);

Query #2: Update Temp/Unit To Do List matched row data.

UPDATE [Unit To Do List] u 
INNER JOIN Temp t ON t.Name = u.Name 
SET u.Unit = t.[Unit], u.[Rank/Rate] = t.[Rank/Rate];

Query #3: Insert new Temp rows.

INSERT INTO [Unit To Do List] ( Name, Unit, [Rank/Rate] )
SELECT t.[Name], t.[Unit], t.[Rank/Rate]
FROM Temp AS t LEFT JOIN [Unit To Do List] AS u ON t.[Name] = u.[Name]
WHERE (u.[Name] Is Null);

1. For each query above:
   a. Create a new query (Design View).
   b. Click the Close button in the Show Table window.
   c. Click View menu, SQL View.
   d. Replace existing SELECT; with above SQL.
2. Run in listed sequence (Delete, Update, Insert).
3. Create a macro to run step 2 after Temp table is loaded.

Jim Kraxberger
Developing Access solutions since 1995
jkraxberger@scp4me.com

 

[drslade]

Jim,

I tried your Query #1 design and I am getting an error that says that it cannot delete from the specified tables. Here is what my query design looks like. I am sure that I just misunderstood what you saying to do and typed it incorrectly.
Column1 Column2
Field: Unit To Do List.* Name
Table: Init To Do List Temp
Delete: From Where
Criteria: Name {NOT IN}
or:

Hopefully I am just entering my quesry expressions wrong. Thanks for your help. I will try the other queries, and if I have problems I will let you know.

Thanks again

David

 

[drslade]

Jim,

WOW... I feel like a total moron now... the section that you said to type the Query in the SQL View, I thought that was just for the last one...I guess I should read a little more slowly. LOL Anyway I got it figured out....thanks for your help.

David

 

[wvandenberg]

David,

Thank you so much for using such a concise and descriptive title as well as clearly explaining your problem. I am just doing the exact same thing (importing/updating/appending).

Jim,

Thanks to you too for the detailed instructions that were easy to follow.

Wendy Vandenberg