Missing Something Using One of the Aggregate Functions (SUM)

[GraeagleBill]

Doing a little debugging trying to understand what I'm missing here: (Just wanting to sum up the "Shares" field for a subset (InvstID=2) of table Ledgers)


Is there simply a syntax error in the way I coded the SUM function?

[JoeM]

In order to use an Aggregate Function like "Sum", it needs to be an Aggregate Query. In an Aggregate Query, every field being returned must either be "Grouped" or have an Aggregate Function applied to it. Your InvstID field has neither.

Here is how you want to do this Query:
1. Add the InvstID and Shares to a query
2. Click on the "Totals" button, which looks like a Sigma or Summation sign.
3. This will add a "Totals" row under each field. Leave InvstID as "Group By", but change Shares to "Sum"
4. Add your Criteria to the InvstID field (=2)
5. View your results.

[GraeagleBill]

Thanks, I was way off base now seeing what the SQL looks like:

SELECT Ledgers.InvstID, Sum(Ledgers.Shares) AS SumOfShares
FROM Ledgers
GROUP BY Ledgers.InvstID
HAVING (((Ledgers.InvstID)=2));

It's easier for me to use design mode like you've shown me with this post to get the SQL expression that I can then use as the ControlSource of a text box. (Actually, I have to create the query in code where I can change the value of InvstID and then set the ControlSource accordingly.)

Thanks,
Bill

[pbaldy]

Hi Bill, sounds like you might get a white Christmas?

Very minor point. That query would be slightly more efficient as:

SELECT Ledgers.InvstID, Sum(Ledgers.Shares) AS SumOfShares
FROM Ledgers
WHERE Ledgers.InvstID=2
GROUP BY Ledgers.InvstID

in other words, using a WHERE clause instead of a HAVING clause. I know, minor point, mainly I just wanted to say hi.

[GraeagleBill]

Hey Paul, and a Merry Christmas to you!

What really needs to happen is WHERE Ledgers.Invst = Portfolio.Invst

See my new post where I'm attempting to use the query as the ControlSource of an unbound text box. Maybe not the right approach?