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:

Field 1	Field 2	Field 3 (Manually updated)
1	a	Yes
2	b	No
3	c	No
4	d	Yes
5	e	No
6	f	Yes
7	g	No
8	h	No
9	i	Yes
10	j	Yes

<tbody>
</tbody>

New Table:

Field 1	Field 2
6	f
7	g
8	h
9	i
10	j
11	k
12	l
13	m

<tbody>
</tbody>

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

Field 1

Field 2

Field 3 (Manually updated)

<tbody>
</tbody>

6	f	Yes
7	g	No
8	h	No
9	i	Yes
10	j	Yes
11	k
12	l
13	m

<tbody>
</tbody>

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,

 

[alansidman]

In a relational database, it is not always wise to delete records. You would be better advised to add a field Yes/No that indicates whether the data was active or inactive.

Having two tables with the same characteristics is a violation of data normalization. You should be using only one table. If you are unfamiliar with data normalization and 1st, 2nd, 3rd normal form then read the following on data base design.

http://sbuweb.tcu.edu/bjones/20263/Access/AC101_FundamentalsDB_Design.pdf

 

[Bassem]

Thanks for your quick reply alan,

The tip of using a Yes/No field to show active/inactive data is great and I will probably use it.

However, the reason I am thinking of using two tables is only because I need a field that is manually updated while the rest of the data is copied from a different source on a regular basis (lets assume on a weekly basis). I'm not sure of how to leave repeating data unchanged while only adding new records that are showing up in the weekly updates.

Do you have any ideas?

Thanks

 

[alansidman]

Append the new records to the Old Table and add the manual updates as necessary. Not sure what this means

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.

Please clarify what happens that is not correct

 

[Bassem]

When I use the append query it gives me the matching records between the two tables and not the new records. What I want is to have the new records added and the old ones remain the same

So basically, if you refer to the example I gave before, I get:

Field1	Field2	Field3 (Manually Updated)
1	a	Yes
2	b	No
3	c	No
4	d	Yes
5	e	No
6	f	Yes
7	g	No
8	h	No
9	i	Yes
10	j	Yes
6	f	No
7	g	No
8	h	No
9	i	No
10	j	No

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

Instead of getting what I'm hoping for:

Field1

Field2

Field3 (Manually Updated)

<tbody>
</tbody>

1	a	Yes
2	b	No
3	c	No
4	d	Yes
5	e	No
6	f	Yes
7	g	No
8	h	No
9	i	Yes
10	j	Yes
11	k
12	l
13	m

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

f, g, h, i, j are repeating instead of adding k, l, m.

Appreciate your help

 

[alansidman]

I suspect that you are not running the append query correctly.
Post your SQL statement for what you are doing for analysis.

 

[Bassem]

INSERT INTO Table1 ( Field1, Field2 )
SELECT Table2.Field1, Table2.Field2
FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1;

 

[alansidman]

The issue is your inner join. This is what is causing your issue. Your SQL statement should be:

INSERT INTO Table1 ( Field1, Field2 )
SELECT Table2.Field1, Table2.Field2
FROM Table2;

 

[Bassem]

Thanks Alan, but this has only partially solved my problem. I'm now getting the below result. k,l,m are now being added which is great but f, g, h, i, j are being repeated. Do you know how I can tell it no to repeat existing records?

Field1	Field2
1	a
2	b
3	c
4	d
5	e
6	f
7	g
8	h
9	i
10	j
6	f
7	g
8	h
9	i
10	j
11	k
12	l
13	m

<tbody>
</tbody>

 

[alansidman]

So you have the same data in both tables? This appears to be a violation of the normalization needed for RDBMS. But since you already have it, then the Append query should read

INSERT INTO Table1 ( Field1, Field2 )
SELECT Table2.Field1, Table2.Field2
FROM Table2 where Table1.Field1 <> Table2.Field1;

Please read the whitepaper in my signature block on database design. It will make problems like this go away hopefully.