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 Replies
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 9 Replies
View Related
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
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
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
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
