record count

[Silvinho]

Ive created a messageborard with a MS Access table called Subject and a Table called messages. Ive created a Query that counts the number of messages per subject:

SELECT DISTINCTROW messages.SubjectID, Count(*) AS [Count]
FROM messages
GROUP BY messages.SubjectID;

How do i link this to the subjects page to give the total number of posts per subject. All that happens is the recordset just gives the first number in the list. i need to link the SubjectID somehow.

can anyone help?

 

[moonshadow]

I think you might be in the wrong forum, but it sounds like you need to join your query (lets called it qryMessageCount) back to your subjects table:
select (fields form subject table), [COUNT]
from Subject LEFT JOIN qryMessageCount ON
subject.SubjectID = qryMessageCount.SubjectID

the left join ensures you get a row back for each subject, even if a subject has no messages. Then use this new query as the basis for your subjects page