Append Query problem

[BobW2961]

I have Parts table and Transactions Table. Parts table has two text fields, part number and description. When a transaction takes place, the date and the part number are put into my Transaction table.
Now I am altering the design of my database. I have added a autonumber primary key field to my Parts table. This field is Part ID.
Now I would like to run some sort of query on my Transaction table to replace the part number with the primary key of that part number in the Parts table.


Unsure how to get this done.

[orange]

What's a transaction? We only now what you tell us, so we know nothing of you, your environment or your business. Please add some context so we understand what the problem/opportunity is (that you are trying to correct). Focus on what you are trying to do in plain English. There may be options for HOW to do it.

[KH Ahmed Bara]

Just let be the two table posted so that we can help you better

[BobW2961]

Ok, If I have several tables that have one field that is the same. This field is a 30 character text field called Part Number.
I see that this design has a lot of redundancy. To reduce it, I have created a Parts table with Part ID field and Part Number field.
I have populated this Parts table with all of my possible parts.

Now I still have several populated tables that have the field Part Number and that field still contains the actual text value of the part number.
I want to replaced the text value for part number in each of these tables with the Part ID key from my new Parts table.

This way, less redundancy and better reference.

[John_G]

You would use an Update query, not an Append query.

For the tables you want to add the Part_ID field to, add the new field to them (Call it Part_ID if you want to), but keep the old Part Number for the moment.

Now create an Update query with the Parts table and the Table you want to add the Part_ID to. Join these two tables on the existing 30-character Part Number field.

In the "Update To" field for the new (unpopulated) Part_ID of second table, enter [Parts].[Part_ID]. When you run the query the Part_ID will be copied to the second table.

Do that for each of the table you want to add the new Part_ID to.

Whether you delete the existing Part Number fields or not is up to you, but I wouldn't until I was sure everything was working properly, and I wouldn't delete it from the Parts table at all.


A suggestion is to make it a select query first, so you can verify you are getting the correct data.

[BobW2961]

John_G
That worked great !
Thanks

[KH Ahmed Bara]

Fantastic Approach