Need Help: Memo Field and Aggregate Functions

[Yahweh]

I know SQL well enough, but I cant seem to get my query to work. Basically, I am trying to display a series of posts, with the number of comments on the side.

I have two tables, Entries and Comments, which look like this:

Entries
---------
ID	Title	Content (memo field)
1	Cats	Something about cats here
2	Dogs	Something else about dogs here
3	Rodents	My pet rat runs in a wheel
etc.


Comments
--------
ID	PostID	Comments
1	1	My cat's name is mittens
2	1	I taught my cat how to throw a curve ball
3	3	Rats like squeak toys
4	2	Nobody likes dogs
5	3	Bunnies make good pets
6	1	Cats are witches in disguise

Comments.PostID corresponds to Entries.ID on a many-to-one relationship.

I want to run a query that will join the number of comments (which is a Count of each row in the Comments table where Comments.PostID = Entries.ID) to the existing table, so that it will look like this:

Entries (with #ofcomments joined)
----------
ID	Title	Content (memo field)		#ofcomments
1	Cats	Something about cats here	3
2	Dogs	Something else about dogs here	1
3	Rodents	My pet rat runs in a wheel	2
etc.

Usually, this would be a really simple SQL statement, but as I am using a Memo field, I am getting a "cannot use Memo field in aggregate function" error.

I'm not sure what to do, any help would be appreciated

 

[Brianwarnock]

As you can Groupby and count memo fields , the 2 functions you require, I cannot understand your problem from the info posted.

Brian

 

[Yahweh]

As you can Groupby and count memo fields , the 2 functions you require, I cannot understand your problem from the info posted.

Brian

Then I'm not sure what I'm doing wrong. My SQL code is this:

SELECT q.ID, q.Quote, Count(a.PostID)
FROM Quotes Q
LEFT JOIN Comments a ON q.ID = a.PostID
GROUP BY q.ID, q.Quote;

But, when I try to run the query, I get the following error (I've changed Entries.Content to q.Quote):

If I modify the last line so that it says "GROUP BY q.ID;", I get this error:

 

[Brianwarnock]

Not sure I fully understand your SQL ,knocked up a quick test and this worked

SELECT [entries].title, [entries].content, Count([comments].postid) AS CountOfpostid
FROM entries INNER JOIN comments ON [entries].id=[comments].postid
GROUP BY [entries].title, [entries].content;

I used your original table and field names, I think.

Brian

Sorry forgot to groupby entries.ID but no doubt you spotted that

 

[Yahweh]

I found an SQL command that works:
SELECT q.ID,
q.Title,
q.Quote,
(select count(ID) from comments as c where c.postid = q.id) as NumberOfComments
FROM Quotes AS q;

But, thanks anyway for your help Brian, I appreciate it