Categorising Postcodes? Can Someone Help?

Mar 13, 2006

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

View Replies


ADVERTISEMENT

Help With Categorising Postcodes! :(

Mar 7, 2006

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

View 12 Replies View Related

Selecting Multiple Postcodes From Table

Aug 15, 2006

This must be the easiest of things, but I haven't used access in a while in now in my new job I need to run the following query.

I have to select a number of postcodes from a single table to see how many are in there. For example select postcodes beginning with B, CV, DY, HR, ST, TF, WR, WS, WW. Then I need to write down how many records found with the above postcodes.

Also I may have to omit some too like omit CV12

Thank you in advance

View 1 Replies View Related

UK Postcodes In The Query Design Grid.

Oct 13, 2006

Hi everyone;

I am using Access 2003 with Windows XP Pro. I have a problem with using a query and the dreaded UK Postcodes!

I have a Client table that contains a field “Client Postcode 1”, in which is entered the first part of the UK postcode i.e. AB11, WC1A, E1, EC2V, etc.

In a Candidate table here are many text fields where we enter which postcode a Candidate wants to work in, i.e. TN, CV4, EC, W, etc.

We have a separate Candidate Search form where we select a particular client (Combo box from the Client table) which then displays the Client’s Postcode 1 data i.e. the first part of the UK postcode before the space.

We have a select query that then tries to match the Client Postcode 1 with the postcode that the Candidate wants to work in from the Candidate’s table.

In this query design grid I have successfully created criteria that will match the Client Postcode 1 field to the Candidate’s postcode field – very straightforward. This, for example, will match a Client with a TN39 postcode with a Candidate who wants to work in the TN39 postcode.

In the same query design grid I have also been able to successfully match the Client Postcode 1 field to a Candidate’s Postcode field using just the first 2 letters (using Left). This matches a Candidate who wants to work anywhere in the TN postcode area (TN1, TN2 etc.) with a Client whose postcode starts with TN

However, what is stumping me is where a Client’s Post code has only 1 letter followed by numbers (E1, B2 etc) in the first part of their postcode without returning EX, BL etc. I would like the accumulated wisdom on this forum to point me in the right direction to design criteria to input into the query design grid that will match ONLY the first letter of the postcode IF the second character is a number.

Regards to all - and what an excellent site!

View 1 Replies View Related

Queries :: Postcodes - Return Only TEXT Before Numbers

Jun 17, 2015

I have a number of short postcodes ie

S43
GU10
ME8

etc etc

I want a run query to return only first characters before the numbers ie..

S
GU
ME

View 4 Replies View Related

Queries :: Match Postcodes Ad Return Part That Matches?

Sep 7, 2014

I have been given a list of UK postcodes, with the following format L15TG or TS14TGU.

I need to be able to match these postcodes to a list of postcodes I have stored in the database, however, my list are only UK outcodes, so L15TG is just L1 and TS14TGU is just TS14.

So I need to match the records and return the part of the string that matches i.e take L1 from L15TG.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved