In Access, I've created the following query using design view:
Code:
SELECT tbltempPositions.client, Sum(tbltempPositions.amount) AS SumOfamount
FROM tbltempPositions INNER JOIN tblClients ON tbltempPositions.client = tblClients.client
GROUP BY tbltempPositions.client, tblClients.Manager, tbltempPositions.security
HAVING (((tblClients.Manager)="ll") AND ((tbltempPositions.security)="abc" Or (tbltempPositions.security)="def" Or (tbltempPositions.security)="ghi"));
I'm trying to sum each client's total value of all 3 of the securities - abc, def, ghi.
The current query shows me each client that has these 3 securities and their individual amounts, so if the client has all 3 securities it shows me 3 records for that client. I want to sum the amount of all 3 of the securities and have 1 record for each client showing that total summed value only. I know I can make a query based on this query to do this but I was wondering if there was a way to avoid that.