SUM function in JOIN query

[PATRICIA]

Just using SQL (i.e., no user-defined functions), is it
possible to get the results of a SUM joined with other
tables?

e.g., I want find personal information and TOTAL
DEPOSITS, but deposits are in a different table from name
and address. How would I integrate these two queries to
return DEPOSIT_AMT also?

SELECT CLIENT.PERSON_ID, CLIENT.PERSON_NAME, ADDRESS...
FROM CLIENT, ADDRESS
WHERE CLIENT.PERSON_ID = ADDRESS.PERSON_ID

SELECT ACCOUNT.PERSON_ID, SUM(ACCOUNT.DEPOSIT_AMT)
FROM ACCOUNT
GROUP BY ACCOUNT.PERSON_ID

 

[Michel Walsh]

Hi,


Using DSum( ) counts as a user defined function?


UPDATE table1 SET f1=DSUM("g1", "Table2", "g2=" & f2) WHERE ...

Otherwise, use a temp table, to store the SUM, then use that temp table..

Alternatively, set the field to receive the sum to 0, then

UPDATE table1 INNER JOIN table2
ON table1.f2=table2.g2
SET table1.f1=table1.f1+table2.g1



Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer (MVP)]

If you don't need the result to be updatable, then

SELECT CLIENT.PERSON_ID, CLIENT.PERSON_NAME, ADDRESS...,
SUM(ACCOUNT.DEPOSIT_AMT) as TotalDeposit
FROM (CLIENT Inner Join ADDRESS
ON CLIENT.PERSON_ID = ADDRESS.PERSON_ID)
INNER JOIN ACCOUNT ON
Client.PersonID = Account.PersonId
GROUP BY CLIENT.PERSON_ID, CLIENT.PERSON_NAME, ADDRESS...

This of course won't return any rows that don't have a record in all three tables.