modifying data in a table (deleting/adding records) access 2013

[Bassem]

Hello,

I'm trying to find a way to update a table that I have with new information. The thing is that the old table is a table that has a field that needs to be manually updated and I don't want to lose that...

What I would like to do is add the new records from the new table into the old one and also delete the ones that are no longer relevant (no longer showing in the new table) but keep the ones that are matching without any changes so that my manually updated field doesn't get affected. Example below:

Old Table:


[TABLE="width: 426"]
<tbody>[TR]
[TD]Field 1
[/TD]
[TD]Field 2
[/TD]
[TD]Field 3 (Manually updated)
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]a
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]b
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]c
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]d
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]e
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]f
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]g
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]h
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]i
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]j
[/TD]
[TD]Yes
[/TD]
[/TR]
</tbody>[/TABLE]


New Table:


[TABLE="width: 185"]
<tbody>[TR]
[TD]Field 1
[/TD]
[TD]Field 2
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]f
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]g
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]h
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]i
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]j
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]k
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]l
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]m
[/TD]
[/TR]
</tbody>[/TABLE]


Hoping to update the old table by using the new table into:

[TABLE="width: 426"]
<tbody>[TR]
[TD]Field 1
[/TD]
[TD]Field 2
[/TD]
[TD]Field 3 (Manually updated)[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 426"]
<tbody>[TR]
[TD]6
[/TD]
[TD]f
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]g
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]h
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]i
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]j
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]k
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]l
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]m
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Needed result: Records 1-5 in old table are no longer relevant so they get deleted and rows 11-13 in new table are now added to the old table while keeping record 6-10 and their corresponding manual entries (Yes/No) the same without having to do the manual update again

I have tried using an append query (not very familiar with it) to solve my problem but it seems to be giving me the opposite of what I'm looking for.

Thanks to anyone who can help.

Regards,

 

[Bassem]

When I run the sql code that you sent, it is asking to enter parameter value for Table1.Field1

I think it's still missing something

 

[alansidman]

Do you have both tables in the grid. This is difficult to diagnose now without seeing your db. Can you post your db (or reasonable facsimile/sample file that is representative) to box.net or dropbox for analysis. It has reached the point where it is a guessing game because we don't have all the facts.

 

[Bassem]

I'm currently at work and we have restrictions for accessing these websites. Will hopefully try to upload my db in the evening from my personal laptop.

Thanks for bearing with me.

 

[Bassem]

Eventually we got it to work with a bit of help from a friend of mine.

Thanks for pointing me in the right direction Alan.

Regards,

 

[alansidman]

You are welcome. For the benefit of others who may have a similar issue at some time in the future, would you post your solution.

 

[Bassem]

Yes sure.

I had to use two action queries to achieve this. One append and one delete.

For the append query in design view the joint was the problem. The arrow had to be from the Field1 in the new info table to the Field1 in the main table. For the columns I chose Field1 & Field2 from the new info table and Field1 (with a criteria: Is Null) from the main table.

For the delete query in design view I only had the main table and used the Field1 column from it. For the second column I used a formula DLookUp("[Field1]","tbl_New Info","[Field1]=" & [Field1]) also with a criteria: Is Null

I didn't write these with SQL but I will paste it here in case it might help someone.

Append Query:
INSERT INTO [tbl_Main table] ( Field1, Field2 )
SELECT [tbl_New Info].Field1, [tbl_New Info].Field2
FROM [tbl_Main table] RIGHT JOIN [tbl_New Info] ON [tbl_Main table].Field1 = [tbl_New Info].Field1
WHERE ((([tbl_Main table].Field1) Is Null));

Delete Query:
DELETE [tbl_Main table].Field1, DLookUp("[Field1]","tbl_New Info","[Field1]=" & [Field1]) AS N
FROM [tbl_Main table]
WHERE (((DLookUp("[Field1]","tbl_New Info","[Field1]=" & [Field1])) Is Null));


Hope it helps