Is it possible to obtain [Sum] data in a single select query from a related table please?

[Kevin_Spencer]

Hi everybody



This my first post and I would be grateful for any help as I have spent ages trying to sort this on my own.

Is it possible to obtain [Sum] data in a single select query from a related table please?

My CLIENTS table is related one-to-many to my INVOICE table with each client having up to about 100 invoices.

I am trying to create a select query using both CLIENT & INVOICE tables to return a single record for each client with a calculated field showing the total invoice amounts for each client taken from the Invoice table?

Any help would be gratefully appreciated.

Thank you - Kevin Spencer

[ranman256]

select tClients.ClientID, Sum(tInvoices.Amt) as Total from tClients,tInvoices where tClients.clientID = tInvoices.ClientID

[Kevin_Spencer]

Thank you very much for your reply ranman256

I've typed:

select tClients.ID, Sum(tInvoices.Gross) as Total from tClients,tInvoices where tClients.ID = tInvoices.Invoice_ID

straight into a new field in my query (I'm using Create>Query Design in Access 2016) and I am receiving the following error:

The syntax of the subquery in this expression is incorrect - check the subquerys syntax and enclose the subquery in parentheses.

[aytee111]

Did you enclose it in parentheses as stated? See here for further information on subqueries: http://allenbrowne.com/subquery-01.html

[John_G]

select tClients.ID, Sum(tInvoices.Gross) as Total from tClients,tInvoices where tClients.ID = tInvoices.Invoice_ID

That looks like it should be the SQL for the whole query, not just one field. Youl also need a group by on TClients.ID.

Here is the SQL for a query that does almost the exact same thing, except that it has two sums, from my own database:

SELECT COLLECT.ALBUM, Sum(ITEMS.NUMINSET) AS SumOfNUMINSET, Sum(ITEMS.CATVAL) AS SumOfCATVAL
FROM ITEMS RIGHT JOIN COLLECT ON ITEMS.COUNTRYNUM = COLLECT.CTRYNUM
GROUP BY COLLECT.ALBUM
ORDER BY COLLECT.ALBUM;