446,136 Members | 1,760 Online
Need help? Post your question and get tips & solutions from a community of 446,136 IT Pros & Developers. It's quick & easy.

# Postcode Letters count query

 P: 9 I have to to create a query that produces a count by the first letter(s) of the postcode where the count is greater than 100. (In Bitish postcodes) For e.g. First Letter(s) of Post code Count B 300 CM 260 CR 237 CV 288 NOT B1 200 B2 150 CM 260 Any help would be appreciated. Thanks Addy Aug 16 '07 #1
10 Replies

 Expert Mod 10K+ P: 12,392 I don't know British postal codes so you'll have to help me out here. First thing you need to do is seperate the letters from the numbers and then do the count. How many letters can there be in the postal code? Aug 16 '07 #2

 Expert 2.5K+ P: 3,532 And are there always spaces between letters and numbers? Aug 16 '07 #3

 Expert Mod 10K+ P: 12,392 Well I finally decided to just look it up so... Expand|Select|Wrap|Line Numbers SELECT iif(IsNumeric(Mid(Postcode,2,1)), Left(Postcode,1), Left(Postcode,2)) AS Area, Count(Postcode) AS CountOfArea FROM tbl_Postcodes GROUP BY iif(IsNumeric(Mid(Postcode,2,1)), Left(Postcode,1), Left(Postcode,2));   Aug 16 '07 #4

 P: 9 Hi Thanks for the reply. Post code could be of the form: B19 2TT B17 3SW SW19 0XL KT19 0NR BW1 15TS I need to count the occurance of first letter or letters before the space(There is always a separation in the UK postcodes). For example if I have fifty thousand postcodes, I need to count how many were of the form B's, BW's or SW's, KT's or K's. I need to try this in access table where there is only one field called Post Code. I am not very good at queries in Access, that is why I have posted it here. Thanks Addy Aug 17 '07 #5

 Expert 100+ P: 126 Hi Thanks for the reply. Post code could be of the form: B19 2TT B17 3SW SW19 0XL KT19 0NR BW1 15TS I need to count the occurance of first letter or letters before the space(There is always a separation in the UK postcodes). For example if I have fifty thousand postcodes, I need to count how many were of the form B's, BW's or SW's, KT's or K's. I need to try this in access table where there is only one field called Post Code. I am not very good at queries in Access, that is why I have posted it here. Thanks Addy You can also have BB4 4BB (ie just two numbers in between. Pretty much L{L}#{#}_#LL Aug 17 '07 #6

 Expert Mod 10K+ P: 12,392 Hi Thanks for the reply. Post code could be of the form: B19 2TT B17 3SW SW19 0XL KT19 0NR BW1 15TS I need to count the occurance of first letter or letters before the space(There is always a separation in the UK postcodes). For example if I have fifty thousand postcodes, I need to count how many were of the form B's, BW's or SW's, KT's or K's. I need to try this in access table where there is only one field called Post Code. I am not very good at queries in Access, that is why I have posted it here. Thanks Addy ... And that's exactly what I've given you in post #4, is there a problem with it? Aug 17 '07 #7

 P: 9 Thanks for the query, it is understandable however I do not know how to implement it as I am quite new to Access. Please help me understand in what form should I apply this query Thanks again Aug 29 '07 #8

 P: 9 I have tried select query and when I ran it I got the following result for BW Area Count BW 9 which is not correct. I have 2 postcodes starting with BW in my table. Please check the query. Thanks Addy Aug 29 '07 #9

 P: 9 I am sorry but the Postcode field name I selected initially was incorrect(Post_Code) and I have changed it to Postcode. It is working now. Thanks a lot Addy Aug 29 '07 #10

 Expert Mod 10K+ P: 12,392 Not a problem, good luck. Aug 29 '07 #11