Creating a count column 1

[barrykellett]

I am making a simple guestbook using an access database and have two tables, one with the thread title and one with all the thread replies.
I use this SQL to create a table of thread titles:

SELECT thread.threadid, thread.Title, thread.threaddate, guestbook.username, guestbook.ID
FROM guestbook INNER JOIN thread ON guestbook.threadid = thread.threadid
WHERE (((guestbook.Original)<>True))
ORDER BY thread.threadid DESC;

I need to ammend this sql so that the table this SQL outputs creates a count column every time it runs. (This will be to Count all replies with the same thread id as the title thread key)

Thanks.
BK

 

[scriverb]

Give this a try:

SELECT t.threadid, t.Title, t.threaddate, Count(gb.username) as CountAllReplies, gb.username, gb.ID
FROM guestbook AS gb INNER JOIN thread AS t ON gb.threadid = t.threadid
WHERE (((gb.Original)<>True))
GROUP BY t.threadid, t.Title, t.threaddate, gb.username, gb.ID 
ORDER BY t.threadid DESC;

Let me know if this works for you.


[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[barrykellett]

Thanks scriverb, I have tried your SQL and it creates a column but counts one reply for each thread.

I am trying to change it to get it to count correctly but not having much luck. If you like i could send you the .mdb file?

Thanks again for your help on this
BK

 

[scriverb]

I hesitate to use DCount within a query without knowing how many records we are dealing with but it should work. If the database is very large then we could use a additional query to do the counting and link to it in the original query to pull in the counts. But, give this a try. This is based on your threadid being a number field. If it is a text field we will have to add quotes to the expression in the DCount function.

SELECT t.threadid, t.Title, t.threaddate, DCount([username], "guestbook", "[threadid] = " & gb.[threadid]) as CountAllReplies, gb.username, gb.ID
FROM guestbook AS gb INNER JOIN thread AS t ON gb.threadid = t.threadid
WHERE (((gb.Original)<>True))
GROUP BY t.threadid, t.Title, t.threaddate, gb.username, gb.ID 
ORDER BY t.threadid DESC;

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[PHV]

Do a keyword search in this forum for rank.

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

 

[barrykellett]

Hi Scrivb, when i run the DCount query i get:
You tried to execute a query that does not include the specified expression 'DCount(gb.username, "guestbook","[threadid]= " &gb.threadid)' as part of an aggregate function

I have emailed you the database to the address on your profile to see if you can help more.

Thanks
BK

 

[scriverb]

I received you database and worked on this a little. I came up with the following but am not sure it includes all of the data you need:

SELECT t.Title, DCount("[username]","[guestbook]","[threadid] = " & [gb].[threadid]) AS CountAllReplies, gb.username
FROM guestbook AS gb INNER JOIN thread AS t ON gb.threadid = t.threadid
WHERE (((gb.Original)=False))
GROUP BY t.Title, DCount("[username]","[guestbook]","[threadid] = " & [gb].[threadid]), gb.username;

Do you need the date, threaid, and id fields in this final recordset? Let me know exactly what fields are necessary.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

Barry: Sorry about that. I copied and pasted the wrong SQL:

SELECT t.Title, DCount("[username]","[guestbook]","[threadid] = " & [gb].[threadid] & " and [Original] = False and [ID] > 0") AS CountAllReplies, gb.username
FROM guestbook AS gb INNER JOIN thread AS t ON gb.threadid = t.threadid
WHERE (((gb.Original)=False) AND ((gb.ID)>0))
GROUP BY t.Title, DCount("[username]","[guestbook]","[threadid] = " & [gb].[threadid] & " and [Original] = False and [ID] > 0"), gb.username
ORDER BY t.Title;

Let me know.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[barrykellett]

Hi Scrivb, the first portion of SQL code you posted there works!
Super!!!

Thanks a million
BK