Counting rows

[emozley]

Hi,

Is it possible to count the rows in a recordset based on a column in another table?

I have one table called Users which has UserID and Department (both are numbers) and I have another table called Messages that has UserID (number) and Message (memo).

Is it possible to execute a query whereby you could count the number of rows where the poster was in a particular deparmtent.

When I display the posts I use

SELECT Messages.UserID, Messages.Message, Users.Department FROM Messages LEFT JOIN Users ON Messages.UserID=Users.UserID

cheers

 

[PHV]

something like this ?
SELECT U.Department, COUNT(*) AS CountOfMsg
FROM Messages AS M INNER JOIN Users AS U ON M.UserID = U.UserID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

plus:

SELECT U.Department, COUNT(*) AS CountOfMsg
FROM Messages AS M INNER JOIN Users AS U ON M.UserID = U.UserID
GROUP BY U.Department

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

Good catch Leslie, i've submitted too fast ...

 

[lespaul]

not very often I get to correct your postings!! Still too early in the morning to be perfect!

Have a great day!
les

 

[emozley]

Fantastic thanks very much