Count of PostCodes

[jcbhydro]

Good Morning,

I have a query to count the number of society members in a particular UK Postcode area. It works well for a single Postcode reference but adding further references to the Query Design View generates an 'or' or 'and' statement, rather than the additional counts that I need.

The SQL code I use for a single reference is :
SELECT Count([Mail List].[PostCode]) AS CountofPostCodeH
FROM [Mail List]
WHERE ((([Mail List].PostCode) Like "BN5 ???"));

How do I amend this code to produce counts for multiple references in one query.

I would be grateful for any assistance.

jcbhydro

 

[Brianwarnock]

You have to use a Sum(IIF(. Technique

Count1: Sum(iif(postcode1 like "BN5 ???"),1,0)
Count2: etc

No Where criteria needed

Brian

 

[pr2-eugin]

I think your Query needs to change a bit, the Count should be GROUPED BY Post Code.. Air CODED.

SELECT MailList.PostCode, Count(MailList.PostCode) AS CountofPostCode
FROM MailList
GROUP BY MailList.PostCode;

 

[Brianwarnock]

Paul is correct if you want to count breakdown of all postcodes but I think that you don't, and further that you want to count based on the first part of the code only, in fact to count a group eg all BN5

Brian

 

[jcbhydro]

Yes Brian,

I do NOT want to Group all post codes.
You will know that the first part of a UK Post Code indicates the locality and the 2nd part a selection of residences, which can be anywhere between 1 & 30 residences.
I am wanting to determine the total number of members in particular localities. Hence my use of the 'like' expression.
I can see the logic of your SUM(IIF coding but I have been unsuccessful in slotting it into my code, even when I dump the 'WHERE' sequence.
I assume that the first 2 lines of my coding should be retained.
Your further assistance would be very welcome.

jcbhydro

 

[pr2-eugin]

How about?

SELECT [COLOR=Blue][B]Left(MailList.PostCode, InStr(MailList.PostCode, " "))[/B][/COLOR] As GroupCode, Count(MailList.PostCode) AS CountofPostCode
FROM MailList
GROUP BY [B][COLOR=Black]Left(MailList.PostCode, InStr(MailList.PostCode, " "))[/COLOR][/B];

I based my calculation for postcodes like..
BS16 3DD
BS1 3XX
BH1 1LG

But if you want exactly the first three digits then you can simply use Left(poscode, 3) but remember that will count BS16 into BS1.. The Area grouping might not be exact..

 

[MSAccessRookie]

Good Morning,

I have a query to count the number of society members in a particular UK Postcode area. It works well for a single Postcode reference but adding further references to the Query Design View generates an 'or' or 'and' statement, rather than the additional counts that I need.

The SQL code I use for a single reference is :
SELECT Count([Mail List].[PostCode]) AS CountofPostCodeH
FROM [Mail List]
WHERE ((([Mail List].PostCode) Like "BN5 ???"));

How do I amend this code to produce counts for multiple references in one query.

I would be grateful for any assistance.

jcbhydro

From what I see, you want Post Codes with the first three Characters the same grouped together, but you do not want all of them, only a selected list? If this is so, then replacing the Like() with In() could work for you. The difficult part will be determining what to include in the In().

WHERE ((([Mail List].PostCode) Like "BN5 ???"));

becomes something like

WHERE ((Left(([Mail List].PostCode), 3) In ("BN5", "BN6", "BN7"));

Of course, determining how to define the list will still be an issue.

-- Rookie

Note:

I was interrupted while I was making my response and it looks like Paul has given you the same advice, only he took into account varying lengths of PostCodes. This was something that I was unaware of, since Post Codes in the US are 5 Numbers followed by a 4 Number extension.

 

[Brianwarnock]

Select Sum(iif(postcode1 like "BN5 ???"),1,0) as countbns, sum(iif(postcode like etc
From [mail list]

Is the code required if you are only selecting a few groups
This gives a horizontal list, if you wanted a vertical list or the list becomes numerous a combination of Paul's code and accessrookies where clause modified would probably work.

Code:
SELECT Left(MailList.PostCode, InStr(MailList.PostCode, " ")) As GroupCode, Count(MailList.PostCode) AS CountofPostCode
FROM MailList
Where Left(MailList.PostCode, InStr(MailList.PostCode, " ")) in ("BN5","B1" ...)
GROUP BY Left(MailList.PostCode, InStr(MailList.PostCode, " "));


Brian

 

[jcbhydro]

Thank you Brian,

Your last piece of coding works beautifully. I can now sum 3 or 4 alphanumeric post code localities as required.

Regards,

jcbhydro

 

[Brianwarnock]

Glad it worked, I had never done that and am grateful to the other guys for pointing the way. I just stitched the plagiarisation together.

Brian