update query w/ totals

  • Thread starter Thread starter Jerrym
  • Start date Start date
J

Jerrym

How can I create an update query totaling 2 fields in
one table and update another table with the sum? For
example: Table 1 (detail file) contains a Key, Debit
amount and a Credit Amount. Table 2 (Summary file)
contains the same Key as table 1 and I would like to have
the Total Debits and Credits Amounts from Table 1.

I am having troubles writing the code. Can anyone point
me in the right direction?

Thanks.
 
Jerrym said:
How can I create an update query totaling 2 fields in
one table and update another table with the sum? For
example: Table 1 (detail file) contains a Key, Debit
amount and a Credit Amount. Table 2 (Summary file)
contains the same Key as table 1 and I would like to have
the Total Debits and Credits Amounts from Table 1.

I am having troubles writing the code. Can anyone point
me in the right direction?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Access (aka JET) UPDATE queries can be a real pain sometimes. Usually
Domain Aggregate functions clear up the problem. E.g.:

UPDATE Table2 As T2
SET T2.TotalDebits = DSum("Debit","Table1","Key=" & T2.Key),
T2.TotalCredits = DSum("Credit", "Table1", "Key=" & T2.Key)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIWsZIechKqOuFEgEQIcDgCfSPeucTsmWHrv/nd7IL+08qCLMwgAoLWc
2/s4xyhiCxTs2fay1c9YISpJ
=yE5d
-----END PGP SIGNATURE-----
 
Back
Top