Question Count unique entries to table (1 Viewer)

[Sarre]

Hi all,

I have a forum on my page, only registered users.
I would like to count how many entries each user have made to the forum.

For instance: John has 112 posts
Eve has 97 posts

For this forum I have 1 tbl_users and 1 tbl_posts.
When logging in to the page each user is assigned a session username.
The username is then stored in tbl_posts when they post.

I'm a newbie with this (SQL), so please be gentle..

 

[plog]

I'll be gentle on you for not knowing how to write a query, I won't be gentle on you for not even attempting to accomplish this on your own. So, have you even tried?

If so, where are you stuck and tell us what you've tried and how it hasn't worked and we will help you along the way. Post any SQL you have.

 

[Sarre]

Are you for real?
What makes you think I haven't tried to find a solution on my own?

Dreamweaver won't accept my SQL because it is not right, that's where
I'm stuck right now.

 

[plog]

Yes very real. What makes me think you haven't tried is that in your initial posted you didn't identify any specific issues or error messages you were receiving. Now through 2 postings you've yet to post any SQL nor have you explained the methodology you've employed in trying to accomplish this yourself.

So again, post any SQL you have and the error messages or unexpected results you are getting and we will be happy to try an resolve it with you.

 

[Sarre]

This is what I've got so far:

SELECT Count (Name)
FROM tbl_posts group by Name

I get a number for each user, but I don't know if it is right.
I have 16 users, but only 15 lines of numbers from the list when testing.
Maybe one user have no posts. No names appear in the list, only numbered lines.
There are 640 posts total.

The idea is to show each user how many posts they have, like on this forum.

HTML from the recordset:

<%
Dim RS_CountFM
Dim RS_CountFM_numRows

Set RS_CountFM = Server.CreateObject("ADODB.Recordset")
RS_CountFM.ActiveConnection = MM_Connection_STRING
RS_CountFM.Source = "SELECT Count (navn) FROM fm GROUP BY navn"
RS_CountFM.CursorType = 0
RS_CountFM.CursorLocation = 2
RS_CountFM.LockType = 1
RS_CountFM.Open()

RS_CountFM_numRows = 0
%>

I'm not sure, but is it possible to show a count based on session MM_User?
Would it be easier?

 

[Pat Hartman]

I'm going to assume you are posting here because your data is in a Jet or ACE database. The best solution is to open Access to create the query and test it. Once it is working, switch the query to SQL view, copy it and paste it in your code.
Looking at your query I would say that it is quite unusual to count and group by the same column. Try changing the count to Count(*) as a quick attempt to fix the problem.

A query that counts the number of posts for an individual might look like:
Select PersonID, Count(*) As PostCount
From tblPosts
Group By PersonID;

 

[Sarre]

Thank you for replying, Pat!

It's much easier to understand the query when you start off with one that is working, like the one you presented. Tried out your suggestions and it works
well, just the way I was hoping for.

Thank you for helping me out, much appreciated

Sarre

 

[Sarre]

Just as a curiousity, is it possile to write back the value to the users table from the query? Thinking about storing the value along with all the other user information.

 

[Pat Hartman]

I would strongly suggest not storing calculated values. They are valid only in the instant they are calculated. When you read the record, you have no idea if the count is accurate. So, forget that thought. Chalk it up to some bad sushi

 

[Sarre]

ok, good to know, thanks