how to count

[nkuk]

I am trying to build my own forum application for a web site.

But i can not get this simple thing right.

I have 4 different table in my database;

discussion
forum
message
reply

I want to know the number of responses, for example, for a particular discussion topic.

So I have to use 'COUNT' to do this for me.

This is to be done on a Access database 2000 and the field to be used is 'discussion_ID'

I have tried this one that I made within Access;

SELECT DISTINCT discussion.discussion_ID, discussion.discussionLabel, discussion.discussionUser, discussion.discussionTime, Count(discussion.discussion_ID) AS CountOfDiscussion_ID
FROM discussion INNER JOIN (forum INNER JOIN (message INNER JOIN reply ON message.discussion_ID = reply.discussion_ID) ON forum.discussion_ID = message.discussion_ID) ON discussion.discussion_ID = forum.discussion_ID
GROUP BY discussion.discussion_ID, discussion.discussionLabel, discussion.discussionUser, discussion.discussionTime;


If you know to solve this puzzle or about a good source of information (I do need to learn it!). I will very much appreciate your help.

Thanks

Nicolas

 

[blom0344]

Hello Nicholas,

Looks a mixed up doesn't it?
If you are selecting solely objects and an aggregate from the discussion table without a where clause that affects the other tables why do you join every other table in your SQL?

Why not: (Possibly DISTINCT not needed either?)

SELECT DISTINCT discussion.discussion_ID, discussion.discussionLabel, discussion.discussionUser, discussion.discussionTime, Count(discussion.discussion_ID) AS CountOfDiscussion_ID
FROM discussion
GROUP BY discussion.discussion_ID, discussion.discussionLabel, discussion.discussionUser, discussion.discussionTime;



T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[nkuk]

Thanks for your help.

Although I must say that I have not made myself clear enough for to understand my query.

I am trying to count the number of responses for a particular discussion (discussion_id) has. Therefore I want to count how many forums, messages and reply belong to a particular discussion.

I want to present the user with information like this:

discussion on:

Marketing there are 5 responses
Physics there are 13 responses
Design there are 2 responses

and so on...

So my question is: how do I do this ???

Many thanks for your cooperation

Nicolas

 

[blom0344]

Hello Nicolas,

It is hard to get a picture of your database-design, so I am trying another approach from this assumption:

every discussion_id (department?) is joined with 1 or more forums, which each itself are connected to 0 or more messages with 0 or more replies.

Now it all depends on what you mean with responses. I assume a forum is not a response, but a message is , as is a response to a message. I would expect the joins between your tables to be something like this:

discussion.discussion_id = forum.discussion_id

forum.forum_id = message.forum_id

message.message_id = reply.message_id

The way you join your tables , using the primary key of discussion makes it impossible to tell which reply belongs to which message to which forum. Okay, perhaps for your counting purposes this does not matter to you.

In that case I would do an append query to an empty table , counting the replies per discussion, followed by an append query counting the messages per discussion.

CREATE TABLE RESPONSES (DEPARTMENT TEXT, RESP NUMBER)

INSERT INTO RESPONSES (DEPARTMENT , RESP)

SELECT DISCUSSION.DISCUSSION_ID , COUNT(REPLY.DISCUSSION_ID)
FROM ........................... (joins)
GROUP BY DISCUSSION.DISCUSSION_ID


repeat the inserts for the messages.

The resulting tables will give you the number of messages /replies and you can add these up for total
T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[CodeHammer]

If you have constructed the query in the manner in which you want it to work, simply open it in a recordset, using a server side client. Then you can use the recordset count property to get the number you need.

 

[nkuk]

Again many thanks for your help.

As I understand you do not have a clear view of my database (neither do I some times!)

But this is the database as follows;

Tables design


discussion

discussion_ID #
discussion_title
discussion_body
discussion_user


forum

discussion_ID
forum_ID #
forum_title
forum_body
forum_user

message

discussion_ID
forum_ID
message_ID #
message_title
message_body
message_user


reply

discussion_ID
forum_ID
message_ID
reply_ID #
reply_body
reply_user

# is the sign for auto-number in the database (I am using it)


So you say that I have to count each table for a particular discussion_ID and then sum then up?

Would you be so kind to clarify it once more?

thanks

Nicolas

 

[KyleS]

Nicolas, I think part of the problem Blom is having in helping you is we don't know how to define what you considder a "Topic" Is it per discussion, per forum, or replies to a message.

Once we get this, combined with the table layout you have given, we should be able to help you out. Kyle