Postcode count query

dxp

Registered User.
Local time
Today, 16:31
Joined
Jun 5, 2006
Messages
62
Hi All,
Can anyone help me count postcode instances. I have a query that looks at my customers table (tbl_Customer_Details) post code field (PostCode), at the moment I have got it to strip out the right side of the postcode leaving me the left district side eg. HG12 8EN becomes HG12. I would then like to count how many times each postcode instance occurs so I can create a report on the result so I can track which district the customers are coming from. I hope this explains the problem. Any help would be mich appreciated.

SELECT tbl_Customer_Details.PostCode, Left([Postcode],4) AS Code
FROM tbl_Customer_Details
GROUP BY tbl_Customer_Details.PostCode;
 
Looks like a good time to use a crosstab report
 
Thanks for the reply Sprocket. Never done anything with Crosstab before any help or ideas would be appreciated
 
Crosstab report/query

OK there is a very easy way to get this done. You already have a query that has got your postcode.
you need two other fields in the query one that is a unique field (say customer ID) and one that wont return many values - something like gender where you only have 2 options.
Then you need to create a crosstab query using your existing query as its data source.
To do this go to the query pane and then select INSERT from the main menu select QUERY and choose Crosstab Query Wizard

For your data source - select your postcde query
For row heading - select your postcode field
For your column heading - select the low count field - gender
For your aggregate function - select COUNT

save and run the query - the only values you are interested in are in the first column which will return total values based on the customerID

Same principle if you want a Crosstab Report

Cheers... Sprocket
 
Thanks for the reply Sprocket and your help. KeithG supplied the following SQL which works and is simple.

SELECT Left([Postcode],4) AS Code, Count([FieldName]) As CountPostCodes
FROM tbl_Customer_Details
GROUP BY Left(tbl_Customer_Details.PostCode,4);

[FieldName]= Name of the field the uniquely identifies your records

as a matter of interest would there be any advantage using you rmethod?
 

Users who are viewing this thread

Back
Top Bottom