How to filter queries

[COII]

Dear all,

I have a Table called "Staff" and I would like to create a query for this table. The condition is :

[Last Name] field must contain "Jackson" word and count the number of these data.

just like as below:

Last Name Count
Jackson 18
William 20

Can I use simple method to display it?

I know that I can use cross-query to do it. But one condition limited to set only. For example, I only find the number of [Jackson], but cannot also find the number of [William] in one query.


Thank You.

 

[DanJR]

I'm not exactly sure I understand your question. If you want count only the number of records that include "Jackson" then try something like:

[tt]
SELECT Count(*)
FROM Staff
WHERE [Last Name] Like "*Jackson*"
[/tt]
If you want to count the occurance of each last name where the last name contains the text "Jackson" then try:
[tt]
SELECT [Last Name], Count([Last Name]) AS [Count]
FROM tblContacts
GROUP BY [Last Name]
HAVING [Last Name] Like "*s*"
[/tt]
Cheers,
Dan

 

[DanJR]

Opps, last SQL statement should be

SELECT [Last Name], Count([Last Name]) AS [Count]
FROM Staff
GROUP BY [Last Name]
HAVING [Last Name] Like "*Jackson*"