Using Post/Zip Codes as criteria to update another column (1 Viewer)

robertmcallister

New member
Local time
Today, 17:16
Joined
Sep 15, 2020
Messages
7
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?
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 10:16
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Today, 17:16
Joined
Feb 19, 2013
Messages
16,553
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

New member
Local time
Today, 17:16
Joined
Sep 15, 2020
Messages
7
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

New member
Local time
Today, 17:16
Joined
Sep 15, 2020
Messages
7
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.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 17:16
Joined
Feb 19, 2013
Messages
16,553
so you are saying you have two fields, city and district and both are populated with the name of the city?
 

robertmcallister

New member
Local time
Today, 17:16
Joined
Sep 15, 2020
Messages
7
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! :(
 
Last edited:

robertmcallister

New member
Local time
Today, 17:16
Joined
Sep 15, 2020
Messages
7
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

New member
Local time
Today, 17:16
Joined
Sep 15, 2020
Messages
7
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

Super Moderator
Staff member
Local time
Today, 17:16
Joined
Feb 19, 2013
Messages
16,553
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

New member
Local time
Today, 17:16
Joined
Sep 15, 2020
Messages
7
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 🤗
 

Users who are viewing this thread

Top Bottom