Update some fields by appending query

[lizzywu]

Hi there,

I'm trying to append my active address information from table1 called taddress (which contains customer_id) to the corresponding fields in table2 called tcustomer to update address information in tcustomer. The fields have the same data type.

When I tried to append the new activated values from taddress to the corresponding fields in tcustomer, a dialog about type conversion failure prevented me from appending.

Is there any way to make such appending perform successfully?

I attach my files here. Could you help me check?

Thank you very much!

[aytee111]

Are you wanting to add the records from taddress to the table tcustomer? Or do you want to update the existing address for each customerid on tcustomer?

If you are trying to add new records to tcustomer then each record will need a unique primary key, the link_id. Your query does not address that field and it is not an autonumber field so needs to be populated manually using your query. I tried running your query a number of different ways and each time got a different message - I don't think Access knows what to do!

[aytee111]

Also your postal code field is text in one table and number in the other. This may also cause problems.

[lizzywu]

Aytee111, I actually want to replace the old address in tcustomer with the new address in taddress. So it's not adding new records but updating the old records. But if I use update query, it won't allow me to replace the whole column.

Also, even after I change the data type of postal code field into number, the same dialog pops up.

I attach the modified one here. Could you help me check again? Thanks a lot.

[aytee111]

Append queries adds new records to a table - update queries update existing ones. You definitely do NOT want an append query, but must get the Update one to work correctly. I don't understand what you mean when you say it won't let you update the whole column?

I would rather you learn to do it than me do it for you. Lesson 1: do a select query first, then afterwards we can change it to an update query. So send me the SQL of a query that displays all the fields you are using, both the new addresses and the old, all in one query.

On a database design note: are you sure you want to remove the old addresses? You don't need to keep them for past reference purposes?

[aytee111]

Another design question - why are you keeping the address more than once in the database? This breaks rules of "normalization" where data is kept only once in a database and is linked to via queries when needed.

[lizzywu]

Thank you.

I want to remove the old addresses in tcustomer, and the old addresses are kept in taddress. When there is any new address coming for a specific customer, I add it into taddress and make it active and I also deactivate the old addresses.

Here's what I did:
UPDATE tcustomer INNER JOIN taddress ON tcustomer.customer_id = taddress.customer_id SET tcustomer.addr = [taddress].[address], tcustomer.addr_city = [taddress].[city], tcustomer.addr_state = [taddress].[state], tcustomer.addr_postal_code = [taddress].[postal_code]
WHERE (((taddress.active_addr)=Yes));

It works!

Thank you so much!

[aytee111]

Well done - you've come a long way!