Help With Categorising Postcodes! :(

[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

 

[neileg]

Could be surprisingly complex!

Sooner or later you are going to need a table that defines your areas in terms of postcodes. I would expect this to comprise a record for each postcode (or partial code) with the area as a field in this record. Then when you need to extract customers by area, you join this table to your customer table via the post code and select on the area.

The potential complexity lies in the depth of post code analysis. If you are lucky, the areas will be defined by the just the first block of the postcode. Even then you will need a big table to hold all the valid codes for the country. If you get into the second block of the code, it will be nightmareish (is that a word?)

The advantage of holding a table with postcodes and areas is that when area boundaries change (and they always do!) you just have to amend this table and not each customer.

It could be easier to assign the area to each customer as the customer record is created. It wouldn't be considered good design to do it this way, but it may be more pragmatic. It depends on the number and turnover of customers, and the complexity of the postcode analysis and the likelyhood of areas changing.

 

[main-man]

So could "lookup" solve this? How would i do this?

 

[neileg]

Mmm...

As I said

Then when you need to extract customers by area, you join this table to your customer table via the post code and select on the area.

But this will only work if you can match the customer postcode to the table of postcodes and areas. What are your views on the complexity issues I raised?

 

[ColinEssex]

If it helps, I know that the Royal Mail have CD's of postcodes and addresses, we have one in our Finance dept.

May be worth investigating???

Col

 

[main-man]

Ok i have the lists of postcodes at hand per region, these will not change. They are about 80 per region. Are there any simpler methods?

All i need to do is pull out the postcodes...categorise them and make a simple report. Its just the categorising im having trouble with, not sure on the best approach

 

[neileg]

Simpler than what?

If you want Access to 'categorise' the postcodes you need a table with all the post codes and the areas they relate to. Without that, you're going no where.

The next step is to match the postcode in the customer table to the postcodes in your area table. I repeat myself here, what about the issue of partial postcodes?

For example: Suppose the postcode area LS1 is in area 1 and postcode LS18 is in area 2, you can't use LIKE to do the comparison becasue a customer postcode LS1 will match either. What you would have to do is identify the first block of the postcode by locating the space in the middle (assuming your formatting is spot on) and extracting the postcode up to the space and look for an exact match. That's not hard. However, if you go down one level further, your example was BD4 0. Now that is virtually impossible to deal with at the same time as dealing with LS18. The variable structure of UK postcodes is the gotcha here.

In my years of experience in all sorts of business, I can guarantee that area definitions do change. They may not have changed for the last 18 years, but when Fred retires and his area is split between Joe and Billy, you have to have that flexibility in your structure.

 

[main-man]

Neileg do you have msn messenger? is it possible i could chat 2 u on msn later this evening about this postcode dilema im very stuck at the moment and my job depend on it. would appriciate it if possible.

 

[main-man]

Can anyone else help me?

 

[neileg]

Sorry, I'm happy to help as much as I can via this forum, but that's my limit.

What is it that is causing you a problem? You don't seem to want to comment on the questions I have raised.

 

[main-man]

Hi Neileg

Im not sure on a few things

You said "Sooner or later you are going to need a table that defines your areas in terms of postcodes. I would expect this to comprise a record for each postcode (or partial code) with the area as a field in this record. Then when you need to extract customers by area, you join this table to your customer table via the post code and select on the area"

So for a start if i make a table say called

table: POSTCODES REGION ONE
field nameOSTCODES (list all the full and partial postcodes)

Then another table

Table: POSTCODES REGION TWO
field name: POSTCODES (list alll the full and partial postcodes)


And do this for each of the four regions? Accorrding to your expertise is this a start?
Please explain in simple terms as its quite new to me!

Thanks appriciate your help

 

[main-man]

P.S About your question about partial postcodes.. some regions do have partial postcodes. The regions do not cover the whole of the UK just certain areas.

The most complex a postcode can be according to my lists is

e.g HD1 5, LS9 7
The majority are HD1, LS7 etc

 

[neileg]

So for a start if i make a table say called

table: POSTCODES REGION ONE
field nameOSTCODES (list all the full and partial postcodes)

Then another table

Table: POSTCODES REGION TWO
field name: POSTCODES (list alll the full and partial postcodes)

No, one table with two fields, Region and Postcode.

P.S About your question about partial postcodes.. some regions do have partial postcodes. The regions do not cover the whole of the UK just certain areas.

The most complex a postcode can be according to my lists is

e.g HD1 5, LS9 7
The majority are HD1, LS7 etc

Now that is a problem. You can use the space in the middle of a postcode as a marker to extract just the first part. You can then match this first block with your table that contains postcodes and regions.

However, if HD1 5 and HD1 6 are in different regions, you can't rely on the first block to identify the region, and to include the first number of the second block will dramatically increase the number of codes you need to store (approximately a factor of 10).

What you might find acceptable is that when you enter the customer details, you get a list of regions that have a similar postcode to the one you have entered, and you pick the appropriate one. However, that will require you to embed the region in the customer record which negates my point about areas changing.