Hi All,

Hopefully you can help me with this one, i've read lots of articles about building forums using access, and i'm aware that there are lots of plugins available. however as my user table is already on the server i would like gto try and build my own forum.

The area that i'm struggling with is, showing either the number of topics in the forum or the number of responces to each topic on a forum / topic overview page.

I have three tables on my server -

Forums.dbo which includes ForumID(PK), ClientID, ForumTitle etc
Topics.dbo which includes TopicID(PK), ForumID, ClientID, TopicTitle etc
Responce.dbo which includes ResponceID(PK), TopicID, ForumID, ClientID, ResponceTitle etc

So far I've managed to build a page that displays a list of forums, using a recordset and a repeat region, this is basically just a repeated region of Forum Titles. Now what i'm trying to do is include a column that highlights the number of topics in each form, so that rather than just being displayed the forum title, our users can see how many topics have been posted before clicking.

This unfortunatly is where i seem to be hitting a brick wall, so would be grateful for advice on how to do this -

Thanks in advance -

Assuming that you are using sql server.

Current you must be using something like following to get the list of forums.

SELECT * FROM Forums

For getting number of topic within each forum, use following

SELECT f.ForumID, f.ClientID, f.ForumTitle, COUNT(t.TopicID) AS Topic_Count
FROM Forums f INNER JOIN Topics t ON f.ForumID = t.ForumID
GROUP BY f.ForumID, f.ClientID, f.ForumTitle

On the similar line, you can get the number of responses for each topic.

SELECT t.TopicID, t.ClientID, t.TopicTitle, COUNT(r.ResponceID) AS Response_Count
FROM Topics t INNER JOIN Responce r ON t.TopicID = r.TopicID
WHERE t.ForumID = 999
GROUP BY t.TopicID, t.ClientID, t.TopicTitle

The same result can be achieved by some different sqls depending upon the situation.