Using Post/Zip Codes as criteria to update another column

[robertmcallister]

Hi all,

I hope you're all doing well and that there's someone here who can help!

I am very new to Access and in my line of work I help manage a huge database within a healthcare setting. Right now my team are receiving referrals and for years they have been inputting the district as the city, however the city itself is split up into three main healthcare districts (district one, district two, district three). Which district each belongs to is determined by their postcode.

Of course there are thousands of postcodes with slight variations: "D21 5DT" and "D21 9FH". With this example the third-last character is the difference between district one and district two, so I need Access to be able to find postcodes that begin with "D21 1" through to "D21 7" and have their district updated to district one, and similarly I need "D21 8" and "D21 9" updated to district two.

This is just one example, but if I could be assisted with this one example I would then know how to go on and do the rest.

Currently I have tried updating using the criteria 'like "D21 5"' and updating the district to 'District One', however this just erases the entire referral list!

Any help or advice would be greatly appreciated.

Many thanks in advance

UPDATE: I have managed to use wildcards to update the tables (e.g., 'like "D21 8**"') but then I just get one column with 1384 rows (the number of entries I replaced) saying 'District One' - how do I update information in one column based on information from another column?

 

[theDBguy]

Hi. Welcome to AWF!

Can you show us your table structure? Do you have a separate field for storing the district? Also, if the district and be determined through the zip codes, then you may not need it at all in your table. You can just calculate it whenever you need to know the district.

 

[CJ_London]

might be easier to have a table to cross reference post code to district e.g.

e.g.
tblPostCodeDistrict
PK autonumber
PostcodePart (e.g. D21 5)
DistrictFK long - link to tblDistricts

then in the future if districts change the area they cover it is easy to maintain.

Note, you need to clarify your requirement - you are saying users have put the city name in the district field, but you want to retain the city name? so does this get moved to a city field? or does district become 'cityname district one'

 

[robertmcallister]

Hi. Welcome to AWF!

Can you show us your table structure? Do you have a separate field for storing the district? Also, if the district and be determined through the zip codes, then you may not need it at all in your table. You can just calculate it whenever you need to know the district.

Hello! Thank you!

Due to confidentiality I cannot show the table, however the headings are as follows: Surname - First Name - DOB - Address Line 1 - Address Line 2 - Postcode - District - Comments... and so on.

The districts are necessary as healthcare leads require figures per district as opposed to figures per postcode, and we require postcodes to send letters to referrals.

Thank you for getting in touch so swiftly!

 

[robertmcallister]

might be easier to have a table to cross reference post code to district e.g.

e.g.
tblPostCodeDistrict
PK autonumber
PostcodePart (e.g. D21 5)
DistrictFK long - link to tblDistricts

then in the future if districts change the area they cover it is easy to maintain.

Note, you need to clarify your requirement - you are saying users have put the city name in the district field, but you want to retain the city name? so does this get moved to a city field? or does district become 'cityname district one'

That's a great idea, however sometimes referrals come through which are outside all districts' boundaries, and then we have to go by the doctor's practice's postcode (whom may be from the other side of the city, therefore be in a different district), but we still need the referral's postcode to send letters to them. Does that make sense?

My apologies if the information given was unclear. Yes currently the district is just set as the city, but we would like the city to be split into three districts as it should have been years ago into District One, Two, and Three.

 

[CJ_London]

so you are saying you have two fields, city and district and both are populated with the name of the city?

 

[robertmcallister]

so you are saying you have two fields, city and district and both are populated with the name of the city?

No, I have two fields, 'District' and 'Postcode' (no 'City' field). Currently we are entering the city into the 'District' field, when in reality we should be entering the region of the city into this field instead (i.e., District One, District Two, District Three). I wish to update all district fields by using postcodes as criteria (e.g., "D21 5" would be District One, but "D21 8" would be district two).

To put it into perspective, the last two characters are always letters, meaning there can be up to 325 letter combinations for each postcode e.g., D21 8**, D21 7** and so on, and with thousands of referrals, it would take far too long!

 

[CJ_London]

OK - I was just trying to make sense of your comment

Edit: Also, the original district (i.e., the city) remains

 

[robertmcallister]

OK - I was just trying to make sense of your comment

Ah my apologies, I can see how that is confusing! I meant I didn't know how to create new districts (i.e., break down the city into three districts) - but I think I managed that.

 

[robertmcallister]

UPDATE: I have managed to use wildcards to update the tables (e.g., 'like "D21 8**"') but then I just get one column with 1384 rows (the number of entries I replaced) saying 'District One' - how do I update information in one column based on information from another column?

 

[CJ_London]

hard to say without knowing what you are actually doing - suggest provide some example data, the result trequired from that example data and the code (sql or vba) you are using

 

[robertmcallister]

Hey guys, I managed! Thank you for your input! Could you delete this thread please? I’m worried as I stupidly used my name as a username