Helpp!!: Count & Group By with in a Query

[RainX]

Hi all,

I hope someone can help, i'v searched but i still couldnt figure out how to do this. =(

The screenshot i've attached is what i have so far.
What i want to do is add a column within that query that counts the number of times the ComplaintShort is the same within the Query and displays it.
I'd also like to make the ComplaintShort Field Distinct so it would only show it once and show the number of times it has really appeared as a duplicate entry.

Heres the Query I currently Have to work with.

SELECT Cities.P_Names, Complaints.precinct_id, Complaints.ComplaintShort
FROM Complaints INNER JOIN Cities ON Complaints.prec_id=Cities.P_prec
WHERE Cities.P_Names=Forms!Form1!comType
ORDER BY Complaints.ComplaintShort;

Please Help

Thanks in Advance

 

[EMP]

Try this query;-

SELECT Cities.P_Names, Complaints.ComplaintShort, Count(*) As Num
FROM Complaints INNER JOIN Cities ON Complaints.prec_id=Cities.P_prec
Group By Cities.P_Names, Complaints.ComplaintShort
Having Cities.P_Names=Forms!Form1!comType

^

 

[RainX]

Thank you soo much!
I will try it right now.

 

[RainX]

It works!!!!!!!! Once again thanks alot!!

I still dont understand the Count(*) as NUM Part.
Do you put it after the column you want to count and itll count it?
An explanation would be awesome so i can practice with it for later use.

Take care

 

[EMP]

Count(*) returns the total number of records within a group.

Count([aFieldName]) returns the number of records within a group, excluding any null values in the named field.

Since there is not likely to be any null values in the complaint field of a complaint table, I just used the more efficient one. The AS NUM part just gives a field name for the field.

^