Writing a query to count records with partial match

? have a table of names and addresses including a 7 digit postcode E.g BA16
7TY in a list of other BA* together with TA*, GL* etc. When I create a query
with Like "BA*" in the criteria it counts individual postcodes and as few
people have exactly the same postcode I get a list of "1s" and it does not
sum them for the group. How do I write a query that counts the number of
people with the same two digits of the postcode?

If you want a count of the number of clients in each grouping by the first 2
characters of the postcode, something like this should do it:

SELECT Left([Postcode],2) AS PostcodePrefix,
Count(ClientID) AS NumberOfClients
FROM tblClient
GROUP BY Left([Postcode],2);

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Pennington" wrote in message
...
? have a table of names and addresses including a 7 digit postcode E.g
BA16
7TY in a list of other BA* together with TA*, GL* etc. When I create a
query
with Like "BA*" in the criteria it counts individual postcodes and as few
people have exactly the same postcode I get a list of "1s" and it does not
sum them for the group. How do I write a query that counts the number of
people with the same two digits of the postcode?

Thanks, I would try this if I knew how as I am not fluent in VB. I have the
Query open in Design View, where do I put these instructions? I have 2
columns
Column 1
Field: Postcode
Table: Clients
Total: Group By
Sort: Blank
Show: Checked
Criteria: Blank
or: Blank

Column 2
Field: Count Of Clients: Count(*)
Table: Blank
Total: Expression
Sort: Blank
Show: Checked
Criteria:Blank
or:Blank



"Allen Browne" wrote:

If you want a count of the number of clients in each grouping by the first 2
characters of the postcode, something like this should do it:

SELECT Left([Postcode],2) AS PostcodePrefix,
Count(ClientID) AS NumberOfClients
FROM tblClient
GROUP BY Left([Postcode],2);

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Pennington" wrote in message
...
? have a table of names and addresses including a 7 digit postcode E.g
BA16
7TY in a list of other BA* together with TA*, GL* etc. When I create a
query
with Like "BA*" in the criteria it counts individual postcodes and as few
people have exactly the same postcode I get a list of "1s" and it does not
sum them for the group. How do I write a query that counts the number of
people with the same two digits of the postcode?

1. Open your query in design view.
Then switch it to SQL View (view menu.)

2. Paste in the SQL statement
Use your table name in place of tblClient
Use your Postcode field name in place of Postcode.
Use your primary key field in place of ClientID.

3. You can then switch back to design view.

Alternatively, just type:
Left([Postcode],2)
into the Field row in query design.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Pennington" wrote in message
news
Thanks, I would try this if I knew how as I am not fluent in VB. I have
the
Query open in Design View, where do I put these instructions? I have 2
columns
Column 1
Field: Postcode
Table: Clients
Total: Group By
Sort: Blank
Show: Checked
Criteria: Blank
or: Blank

Column 2
Field: Count Of Clients: Count(*)
Table: Blank
Total: Expression
Sort: Blank
Show: Checked
Criteria:Blank
or:Blank



"Allen Browne" wrote:

If you want a count of the number of clients in each grouping by the
first 2
characters of the postcode, something like this should do it:

SELECT Left([Postcode],2) AS PostcodePrefix,
Count(ClientID) AS NumberOfClients
FROM tblClient
GROUP BY Left([Postcode],2);

"Pennington" wrote in message
...
? have a table of names and addresses including a 7 digit postcode E.g
BA16
7TY in a list of other BA* together with TA*, GL* etc. When I create a
query
with Like "BA*" in the criteria it counts individual postcodes and as
few
people have exactly the same postcode I get a list of "1s" and it does
not
sum them for the group. How do I write a query that counts the number
of
people with the same two digits of the postcode?

Thanks very much, it worked a treat and I have learned a bit more about using
Access.

"Allen Browne" wrote:

1. Open your query in design view.
Then switch it to SQL View (view menu.)

2. Paste in the SQL statement
Use your table name in place of tblClient
Use your Postcode field name in place of Postcode.
Use your primary key field in place of ClientID.

3. You can then switch back to design view.

Alternatively, just type:
Left([Postcode],2)
into the Field row in query design.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Pennington" wrote in message
news
Thanks, I would try this if I knew how as I am not fluent in VB. I have
the
Query open in Design View, where do I put these instructions? I have 2
columns
Column 1
Field: Postcode
Table: Clients
Total: Group By
Sort: Blank
Show: Checked
Criteria: Blank
or: Blank

Column 2
Field: Count Of Clients: Count(*)
Table: Blank
Total: Expression
Sort: Blank
Show: Checked
Criteria:Blank
or:Blank



"Allen Browne" wrote:

If you want a count of the number of clients in each grouping by the
first 2
characters of the postcode, something like this should do it:

SELECT Left([Postcode],2) AS PostcodePrefix,
Count(ClientID) AS NumberOfClients
FROM tblClient
GROUP BY Left([Postcode],2);

"Pennington" wrote in message
...
? have a table of names and addresses including a 7 digit postcode E.g
BA16
7TY in a list of other BA* together with TA*, GL* etc. When I create a
query
with Like "BA*" in the criteria it counts individual postcodes and as
few
people have exactly the same postcode I get a list of "1s" and it does
not
sum them for the group. How do I write a query that counts the number
of
people with the same two digits of the postcode?