By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,136 Members | 1,760 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
10 Replies


Rabbit
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

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

Rabbit
Expert Mod 10K+
P: 12,392
Well I finally decided to just look it up so...

Expand|Select|Wrap|Line Numbers
  1. SELECT iif(IsNumeric(Mid(Postcode,2,1)), Left(Postcode,1), Left(Postcode,2)) AS Area, Count(Postcode) AS CountOfArea
  2. FROM tbl_Postcodes
  3. GROUP BY iif(IsNumeric(Mid(Postcode,2,1)), Left(Postcode,1), Left(Postcode,2));
  4.  
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

Rabbit
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

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

Post your reply

Sign in to post your reply or Sign up for a free account.