Writing a query to count records with partial match

[Pennington]

? 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?

 

[Allen Browne]

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]

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

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

Reply to group, rather than allenbrowne at mvps dot org.

? 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?

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

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

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

? 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?

 

[Pennington]

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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

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

? 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?