Count postal codes that start with K,M,P,L etc

[Carleton1]

I am try to create a query or report that will sum all the Canadian Postal Codes in the field [Home_Postal_Code] that start with a different letter. For example. I need to report that 278 postal codes start with a K. I would like to have all this information in one report. I can run a query using the postal code field with the criteria K* and when I run the query it returns all the postal codes and at the bottom of the query it reports how many records it returned. I cannot however add the same field to the query 10 times and change the criteria to P*, L*, M*. So I need an expression that will capture this information.

[orange]

Tell us about your table.

Sounds like (air code-- untested)

Select Left(postalCode),1) as StartingWith, Count(Left(PostalCode,1))
From YourTable
Group By Left(postalCode),1)

[June7]

The Left function extract not needed in the Count() but do need alias name else Access will assign one like Expr1.

SELECT Left(PostalCode),1) As StartsWith, Count(*) As CountCodes FROM table GROUP BY Left(PostalCode),1);

Or build a report using Grouping & Sorting features with aggregate calc in footer sections. Do a grouping on the expression that extracts the first character and use Count(*) in textbox in group footer. This will allow display of detail info as well as summary calcs.

[Carleton1]

Tell us about your table.

Sounds like (air code-- untested)

Select Left(postalCode),1) as StartingWith, Count(Left(PostalCode,1))
From YourTable
Group By Left(postalCode),1)

I am not that familiar with expressions and other code but have self taught a bit. My table holds biographical information on our applicants. Annually I use this information to produce a recruitment report. I use the postal code field to report how many applicants we get from different areas of Ontario and Canada.

I was able to use DCOUNT to count the total number of postal codes in the table but when I tried to enter a criteria "[Home_Postal_Code] like K*) I get an error.

=DCount("[Home_Postal_Code]","Master for Inquiries & Students","[Home_Postal_Code] like K*)

Any help would be much appreciated.

[orange]

Try creating a report as June7 suggested.
Or create a query using this SQL

SELECT Left(PostalCode),1) As StartsWith
, Count(*) As CountCodes
 FROM table 
GROUP BY Left(PostalCode),1);

[June7]

Text parameters must be delimited with apostrophes: "[Home_Postal_Code] like 'K*')

Domain aggregate functions can run slow on forms and reports.

A report using Grouping & Sorting with aggregate calcs in group footer is the better approach.

[Carleton1]

Thanks for both your responses. I was able to get the DCOUNT function to work once I put in the apostrophes. Would either of you be able to recommend any good sites or other references that could help me learn more about the expression you were speaking of: SELECT Left(PostalCode),1) As StartsWith, Count(*) As CountCodes FROM table GROUP BY Left(PostalCode),1);. I tried to enter this expression in a textbox and received an "enter parameter value" prompt. I think I need a bit more background knowledge before I attempt this. Any resources regarding this would be great. Thanks again June & and Orange.

[June7]

That expression is an SQL statement. Create a query with Access query builder, switch to SQL View. This will expose the SQL that Access built for you. SQL (Structured Query Language) is the code used to manipulate data in queries. Tables and queries are what forms and reports are based on (RecordSource property).

More about SQL http://w3schools.com/sql/default.asp