Count query

[Guest]

I need some help in writting this query>

I have a table with several fields. The important fields are CaseID and
Activity. (tblPriority).
Each Activity field has the word "Priority" in it.
I need to run a query which list all the CaseIDs and the count of the number
of priority records for each CaseID.
I may want to sort on the count field.


Thanks

Sheldon

 

[Jeff Boyce]

Sheldon

If "each Activity field has the word "Priority" in it", couldn't you just
count the CaseID fields? (by the way, if every record in this table has the
same value -- "Priority" -- in a field, why have the field?)

Have you tried a Totals query, GroupBy CaseID and Count CaseID?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[John Spencer]

Assumption:
The word "Priority" can appear anyplace in the Activity field.

Try pasting this into the SQL view

SELECT CaseID, Count(Activity) as CountThem
FROM tblPriority
WHERE Activity Like "*Priority*"
GROUP BY CaseID
ORDER BY Count(Activity)

In the query grid, add the CaseID once and Activity twice to fields
Select View: Totals from the menu
-- Change Group By to Count under the first activity
-- Change Group by to WHERE under the second activity field
-- Set the criteria under the second activity to LIKE "*Priority*"
-- Set sorting by either CaseID or the Activity with Count in the totals
line