Assigning postcodes to regions

[lsmcal1984]

Hello all,


This is my first post on this forum and I apologise if it is in the wrong place.
I am trying to create a report of postcodes by region, by use of two (unrelated) tables.


TABLE1 contains a customer’s details, including their full postcode.
TABLE2 contains a list of the first parts of postcodes and their corresponding regions – i.e. AL1 = Hertfordshire or B49 is Warwickshire.


I want to create a query that can match the first part of the postcode in Table 1 to the corresponding field in TABLE2, which then allows me to display the Region field, like this:


AL1 XXX in TABLE1 is matched to AL1 (Hertfordshire) in TABLE2.


I then want to count the responses by region and create a report, like:


Hertfordshire: 6
Warwickshire: 5



As the data in TABLE1 is imported from an Excel spreadsheet, it is not feasible for me to add columns to it.


Any suggestions are appreciated.

 

[plog]

In general, you want to create a query that will produce the date you want to match between the 2 tables. Then use that query in another query to produce the results you want.

I can't speak in specifics without more data. Could you post some sample data of both tables (include field names) and then what you want the result to be based on the sample data you provide?

 

[lsmcal1984]

Sure,

I don't need dates - just a list of regions and a count of the customers in each. I want the postcode field in Table1 to match with the part-postcodes in Table2. Then I will count how many matches there are (as there are many postcodes in a region) and summarise.

TABLE1:
CustomerID: Autonumber
FirstName: Ann
LastName: Other
Address1: 12 A Lane
Address2: Any town
Address3: Any county
Postcode: AL10 5LX

TABLE2:
PostcodeID: Autonumber
ShortPostcode: AL10
Region: Hertfordshire

The report will display how many customers are in each region:

Hertfordshire: 2
London: 8
Warwickshire: 4

I hope that makes sense.

 

[plog]

I meant data not date for the sub-query.

Also, I wanted sample results based on your sample data. You provided 1 record in each table and then had totals that didn't tie back to that data. Could you provide sample rows from each table and then show me what the results will be based on that sample data?

Last, is the ShortPostcode always 4 characters long?

 

[sparks80]

Hi,

One possibility is to use a VBA function to return the first part of the post code based on table 1.

Here is a suggestion:

1: Press ALT+F11 to go to the visual basic editor
2: Go to the tools menu and select references
3: Scroll down and tick "Microsoft VBScript Regular Expressions 5.5"
4: Go to Insert > Module
5: Enter the following code:

Public Function GetFirstPartOfPostCode(strPostCode as String) as String
Dim PostCodeRegExp as New RegExp
Dim mc as MatchCollection
Dim m as Match

' This pattern will match one or two letters, followed by one or two numbers
PostCodeRegExp.Pattern = "\w{1,2}\d{1,2}"

If PostCodeRegExp.Test(strPostCode) Then
    Set mc = PostCodeRegExp.Execute(strPostCode)
    set m = mc.Item(0)
    GetFirstPartOfPostCode = m.Value
Else
    GetFirstPartOfPostCode = vbNullString
End If

Set mc = Nothing
Set m = Nothing
Set PostCodeRegExp = Nothing

End Function

Now in your query to get the first part of your postcode

FirstPart: GetFirstPartOfPostCode([Table1].[PostCode])

 

[Simon_MT]

You can get into a real bind with UK postcodes and unless you a restricted number of postcodes it can become very cumbersome.

Just looking at your scenario the county or town - either could be applicable.

Simon

 

[raskew]

Hi -
If Postcodes are similar to your example, and you are seeking to extract the portion to the left of the space, try playing with this:

x = "AL10 5LX"
? left(x, instr(X, " ")-1)
AL10

HTH - Bob

 

[gemma-the-husky]

the problem you will get is to analyse the given postcode into its leftpart, and rightpart

some leftparts have 3 chars, and others 4, and there may or may not be a space within the postcode.

you cannot get a list of valid postcodes (copyright PO), but you can certainly get a list of the 121/124 valid geo designators - you ptrobably only need the letters, not the numbers anyway.

http://en.wikipedia.org/wiki/List_of_postcode_areas_in_the_United_Kingdom

just put these in a separate lookup table

 

[soupsatchel]

Old thread but postcodes and their local authorities can be downloaded from Ordnance Survey Code-Point® Open...google as it won't let me link yet. Be aware there are 1.7m postcodes.