Solved How can I run this update query without messing everything up? (1 Viewer)

[aian]

Hi all,

I have an MS Access 2007 database where I keep contact details of people. Data is input through a frm_CUSTOMERS and sent to the table tbl_CUSTOMERS which has the following fields:

• CUSTOMER_ID
• FULL_NAME (text field)
• ADDRESS (text field)
• COUNTY (lookup from tbl_COUNTIES)
• POSTCODE (text field)

After about 2 years of using it, I noticed that there have been several input errors in the Post Codes. I have therefore acquired a list of all the available post codes that could ever be typed in that field, and created a new table tbl_POSTCODES, with the following fields:

• POSTCODE (Primary key)
• COUNTY (text field)

The input errors include both typing the post code incorrectly in the form that populates the tbl_CUSTOMERS, as well as adding the wrong county for that post code.

What I want to do now is:

1. Update all the existing records of tbl_CUSTOMERS with entries from the newly created tbl_POSTCODES so that the post codes are pulled from there instead of the manually added post codes.
2. Force the COUNTY field in tbl_CUSTOMERS to change according to which County is assigned to the specific post code in the tbl_POSTCODES
3. Change the data enty form in such a way that when someone starts typing a post code, the field starts showing what can come after (hope this makes sense) and not allow any non-existent post codes to be typed.
4. When Tab is pressed, the COUNTY field in the form to be automatically updated with the one that correlates with this post code as in tbl_POSTCODES.
5. Then get rid of tbl_COUNTIES

I understand that I may have to use what is called an update query, but I have no idea how to do that. Can you please help? Can this be done using a query, or will I need to use the dreaded VBA of which I know nothing?
I have "sanitised" all the previous post codes and they are all correct now, but it was done manually, not using a reference to tbl_POSTCODES.

EDIT: Changed the tbl_POSTCODES fields

 

[Gasman]

If you now have a table of postcodes with counties, no need for county in the customer table, you just look it up.
Bring in the county with the postcode into the combo, then update a locked control with that column data.

 

[NauticalGent]

Check this out:

 

[aian]

Hi @Gasman thank you for replying.
I understand the first bit of your answer about the county in the customer table and you are right, no need for that.
But I lost you in the second part of your answer. How do I update all the existing entries?

 

[aian]

Check this out:

That's EXACTLY what I was looking for! Thank you very much @NauticalGent

 

[Gasman]

My understanding is you now have the correct postcodes in the customer table, as you have corrected the bad postcodes?
So now you just have to use the postcode table for new entries.
For that I would use a combo, so as you type, it will start at those characters. Then in the afterupdate of the combo, you can show it's county in an unbound control.

 

[NauticalGent]

That's EXACTLY what I was looking for! Thank you very much @NauticalGent

Thank Richard Rost, it's his video!

 

[aian]

My understanding is you now have the correct postcodes in the customer table, as you have corrected the bad postcodes?
So now you just have to use the postcode table for new entries.
For that I would use a combo, so as you type, it will start at those characters. Then in the afterupdate of the combo, you can show it's county in an unbound control.

Thank you, I got it now!