Postcode Letters count query

[Addy Smith]

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

[Rabbit]

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?

[missinglinq]

And are there always spaces between letters and numbers?

[Rabbit]

Well I finally decided to just look it up so...

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));
 

[Addy Smith]

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

[Stwange]

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

[Rabbit]

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?

[Addy Smith]

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

[Addy Smith]

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

[Addy Smith]

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

[Rabbit]

Not a problem, good luck.