Counting records in related table; can't get GROUP BY clause right 1

[Sashanan2]

I am trying to run a query on two tables with a one-to-many relationship which gives me all columns from the first table and one calculated field that counts the number of related records in the second table. Query so far is as follows ("Col1" is the primary key in table 1 and the foreign key in table 2; "Col2" is the primary key in table 2):

SELECT Table1.Col1, COUNT(Table2.Col2) AS Total
FROM Table1, Table2
WHERE Table1.Col1 = Table2.Col1
GROUP BY Table1.Col1

This works fine with just the one column (Col1); but I run into trouble when I start including other columns from Table1 as well. Unless I include all Table1 columns that I select in my GROUP BY clause as well, I raise the error:

"You tried to execute a query that does not include the specified expression '[columnname]' as part of an aggregate function."

for whichever column I omitted. I wouldn't mind including all the columns in the GROUP BY clause except that I'm limited to a maximum of ten (and I need more), plus one of the columns I want in is of the OLE Object data type which, when used in a GROUP BY clause, raises the error:

"Cannot group on Memo, OLE or Hyperlink Object ([columnname])."

I have little experience with GROUP BY queries and I suspect I'm just misunderstanding the way the GROUP BY clause has to be done, but I can't seem to figure out what I'm doing wrong. Perhaps what I want needs a different kind of query entirely, but I wouldn't know what. I've been looking up examples and explanations on the net for the past few hours but couldn't find anything that explains this particular situation. Can anybody help out (and, ideally, explain where I'm going wrong)?

For reference, the above is done in an Access 2003 MDB.


"Any fool can defend his or her mistakes; and most fools do." -- Dale Carnegie

 

[dRahme]

Hi, you know what you might do would be to first build a query based on the second table and do your counts there. Then build a second query using the first table and bring in the first query as well. Link on Col1. Bring in the desired fields from table one and the counts from the first query. That should work.

Hope that helps

 

[PHV]

You may consider an inline view:
SELECT A.*, G.Total
FROM Table1 A INNER JOIN (
SELECT Table1.Col1, COUNT(Table2.Col2) AS Total FROM Table1, Table2
WHERE Table1.Col1 = Table2.Col1 GROUP BY Table1.Col1
) G ON A.Col1 = G.Col1

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

 

[Sashanan2]

Thanks both! PHV's solution - though it makes my head spin and I'll need to look at it some more to grasp exactly how it works - did the trick.

"Any fool can defend his or her mistakes; and most fools do." -- Dale Carnegie