SQL Update Query - hard one (i think)

[Jimbob]

I need to update one table with the sum of values in another table, eg

set PaymentSummary.TotalCash to the sum of PaymentLine.Cash where both tables contain the same PayentRef column

I think I need to do this with a JOIN of some type, but I'm not sure which

anyone got any ideas....

[gnosys]

Try this:

UPDATE PaymentSummary SET TotalCash = (SELECT
SUM(Cash) FROM PaymentLine WHERE PaymentSummary.PaymentRef
= PaymentLine.PaymentRef AND PaymentSummary.PaymentRef
= [whatever the PaymentRef of the record you want to
update is])

That is how you would do it in Oracle because Oracle
doesn't use the INNER JOIN syntax that Access does.
I think it would still work in Access, but I'm not sure
whether Access supports subqueries like Oracle does.
Hope this helps.

G.

[HunterMcCray]

Originally posted by Jimbob
I need to update one table with the sum of values in another table, eg

set PaymentSummary.TotalCash to the sum of PaymentLine.Cash where both tables contain the same PayentRef column

I think I need to do this with a JOIN of some type, but I'm not sure which

anyone got any ideas....

I have found that it is often better to execute this type of transaction using code rather than SQL statements. I assume that you are trying to either roll-up monthly totals or create period reports in a summary table? In either case you gain a lot of control by writing a routine that opens a recordset containing the records that you want to tally and then using a do loop to tally them. Following this do loop you can either append the information to the second table or you can update the second table depending on your requirements. For instance:

Dim rs as recordset
Dim Sql as String
Dim curAccountTotal as Currency
Sql="Select TableName.* From TableName Where TableName.CustId = " & CustNum & " AND TableName.InvoicePaid = False ORDER BY TableName.InvoiceDate;"

Set rs=db.openrecordset(Sql,dbopendyanset)
rs.movelast
if rs.recordcount<1 then
set rs=nothing
exit sub
end if

rs.movefirst
curAccountTotal=0
do
if rs!AmtDue <> null then
curAccountTotal=curAccountTotal + rs!AmtDue
end if
rs.movenext
loop loop until rs.EOF
UPDATE Constants SET Constants.lintg = 8
WHERE (((Constants.Indx)=7));

Sql="UPDATE Table2Name SET Table2Name.AcctBalance = " & curAccountTotal & " WHERE ((Table2Name.CustId) = " & CustNum
& ");"
db.execute sql

(This assumes that you have opened the database with a global variable db of the type database) The advantage to doing this is greater control and error trapping. The downside is that it requires slightly longer to process and it requires a bit more code writing. I use this sort of code in many places in an accounting routine. I initially used complex SQL statements, but found that I had far better control of what was happening by doing it this way, and when something went wrong it was much easier to trace the error. In my Application I use several tables as temporary total holders. This greatly simplifies creating user generated parameters for reports....

Hope this is helpful,

Hunter

[Jimbob]

thanks, both of you

I'll have a look at both methods and see what's going to be best