Categorising Postcodes? Can Someone Help? (1 Viewer)

[main-man]

I have a database with 3000 + records all with customers from all over the UK.


The CUSTOMER table contains
>customer ID
>name
>address
>postcode

I have been asked to produce reports from the database.
These reports should group all customers into postcodes regions and then display information accordingly. E.g how many customers have ordered information in region 1.


For each region I have been give a list of postcodes.

Region 1, e.g LS17, LS18
Region 2 e.g BD4 0,
Region 3 ETC
Region 4 ETC

Each region list is extremely long to type in but I have a copy on paper.

Could someone help and guide me into a quick way of grouping the required postcodes into regions 2 enable the reports to be produced.

One way I have done this is by doing a select query and typing in the criteria “like “LS1*” or like LS18*” …and by typing this on and on to the list this is quite long.

Plus I need to be able to use the * function so I select the correct postcodes.

As this is so time consuming ..Bearing this in mind I have been told a far better approach is to put the postcodes in a table and then perform a lookup on that table when trying to determine the region.

I don’t understand what to do…would this be a better approach if so how would I do this as I don’t know how to use lookup? Could someone help me? Perhaps with a small example so I could follow it?

Appreciate any help

Many thanks

 

[Brianwarnock]

When I had to do something similar, the postcodes were taken to the number after the space in some cases eg L1 6 or WA23 4, I created tables for each region and parsed the postcodes on the file then for each region I could run a query joined against the relevent region table.

To avoid using using Like * I included all codes, this is quicker to do by entering in EXCEL using fill series techniques and importing into ACCESS.

There may be better approaches.

Brian

 

[main-man]

Brian could you help me step by step if you have time!

To start with i have a customer table on the database

The CUSTOMER table contains
>customer ID
>name
>address
>postcode

So if i create a new table first

for example called:

table name: REGION 1
POSTCODES - as the field name
(and paste all the postcodes in as records in this table)
eg.
BD18
BD12
LS2 7

Is this right?Do this for each of the four regions? and therefore have 4 region tables with lists of postcodes in each region?

 

[Brianwarnock]

From the example you have given it appears that region 1 contains all of BD18 but only LS2 7, LS2 0-6 and 8-9 are in other Regions, if this is the pattern, it was for me, then I had to include in my table BD18 1 to BD18 9 , else you have to use Like* or different parsing techniques depending on post code, I decided on the simple approach, hence my use of EXCEL for the data entry.
It still takes time but I couldn't come up with a programmable approach.

I am assuming your data table contains FULL postcodes.

Brian

 

[Brianwarnock]

Thinking about your problem if you really are covering the whole of the UK, I was only covering Merseyside plus a bit more, then I would guess that your regions are large and are likely to contain all of BD* for example in one Region. If so a multiple query approach is probably better selecting the large areas first then using the table technque where the postcode group is spread over regions. Combine the outputs either in a temp table or using a Union query.

Brian

 

[main-man]

Thanks brian for helping!

The regions we cover are 4, there are the yorkshire and humber region

i have a 4 regions each with 50 postcodes per region approx

However in my database i have 3000 people so these need categorising.

Could you talk me through the best way step by step?

 

[neileg]

Brian, this is a duplicate post. I have been commenting on the other one.

 

[Brianwarnock]

Thanks Neil, as this is my last day at work I will bow out.

Brian

 

[Brianwarnock]

I've just done a search and found that this is the FOURTH thread on this subject, there is no easy answer, uaually a little wit saves a lot of sweat but sometimes you just have to take the sweat.

Brian

 

[The_Doc_Man]

The question isn't that you are categorizing post-codes, but HOW you are categorizing them.

Like, if all post-codes are their own category, no problem. But if they are grouped in some way, WHOOPS! Big problem.

I'm from the USA so I don't know the ins and outs of UK post codes, but I'll tell you how you might do this using the USA model.

Each ZIP code is a 5-digit number. There is an option called ZIP+4 that adds four more digits to really refine the locale. The problem is that the first three digits don't always help in knowing where anything is located.

So if you are looking to categorize postal codes by, say, distance from point X on the map, you need to somehow build a table that contains all of the codes in one field and another field that records the distance from point X to some arbitrary point like the center of the postal region. Then you can group them according to this distance, like some of our shipping companies do. Zone A (short hop) through Zone E ("overnight" means "air service required"). This is the tedious part - filling in the category codes.

Once you have this lookup table, though, you can use a query that does a JOIN of your raw record to the post-code lookup table. Then you can see the category and sort by category even though category isn't in the "raw" address table. You will learn from this exercise that queries are your friends.

Now, if you have some other criteria for categorization - like the name of the business representative who covers that postal code - the same idea still applies. The work is in building the code-to-category lookup table, which might well be a many-to-one::code-to-category table. And unless it is purely geographical, it is a manual operation. But once it is done, it is no sweat to use it with a simple JOIN query.