Update using 2 tables and a sum

[kidfortoday]

Ahhh... I can't figure this out, can someone help?

I'm using Access 2003.

I have 2 tables here:

One is for each person, called tblPartners.

One is for each distribution by a partner, called tblDistributions

In tblPartners there is a field called ClosingBalance.

What I want to do is UPDATE ClosingBalance to equal the sum of
distributions by that partner for a period.

So what I originally did was try

UPDATE tblPartners LEFT JOIN tblDistributions
ON tblPartners.PartnerID = tblDistributions.PartnerID
AND tblPartners.Period= tblDistributions.inPeriod
SET tblPartners.ClosingBalance = SUM(tblDistributions.DistribAmt)

But this gives the error "You tried to execute a query that does not
include the specified expression 'ClosingBalance' as part of an
aggregate function."

I also tried:
UPDATE tblPartners
SET ClosingBalance = TotalDistribs
FROM tblPartners INNER JOIN tblDistributions
(SELECT SUM(DistribAmt) AS TotalDistribs, PartnerID, inPeriod
FROM tblDistributions GROUP BY PartnerID, inPeriod)
ON tblPartners.PartnerID = tblDistributions.ParnterID AND
tblPartners.Period = tblDistributions.inPeriod

But gives a syntax error.

[salad]

ki*********@gmail.com wrote:

Ahhh... I can't figure this out, can someone help?

I'm using Access 2003.

I have 2 tables here:

One is for each person, called tblPartners.

One is for each distribution by a partner, called tblDistributions

In tblPartners there is a field called ClosingBalance.

What I want to do is UPDATE ClosingBalance to equal the sum of
distributions by that partner for a period.

So what I originally did was try

UPDATE tblPartners LEFT JOIN tblDistributions
ON tblPartners.PartnerID = tblDistributions.PartnerID
AND tblPartners.Period= tblDistributions.inPeriod
SET tblPartners.ClosingBalance = SUM(tblDistributions.DistribAmt)

But this gives the error "You tried to execute a query that does not
include the specified expression 'ClosingBalance' as part of an
aggregate function."

I also tried:
UPDATE tblPartners
SET ClosingBalance = TotalDistribs
FROM tblPartners INNER JOIN tblDistributions
(SELECT SUM(DistribAmt) AS TotalDistribs, PartnerID, inPeriod
FROM tblDistributions GROUP BY PartnerID, inPeriod)
ON tblPartners.PartnerID = tblDistributions.ParnterID AND
tblPartners.Period = tblDistributions.inPeriod

But gives a syntax error.

Why don't you create a Totals query on tblDistributions and use that
Totals query to link to tblPartners to get the value to update.

I suppose you could use Dsum() to get the closing balance as well...make
sure you filter it correctly.

[Chris L.]

First create a query that groups partners and sums the distributions:

SELECT tblDistributions.PartnerID , Sum(tblDistributions.DistribAmt)
AS TheSum
FROM tblDistributions
GROUP BY tblDistributions.PartnerID ;

Later create an update query that joins the first query with the
partners table.

But, storing a sum in a parent table maybe isn't such a good idea,
what happens when the child records change? who's in charge of
updating the closing balance to keep it in sync?

Kind regards
C.
On Jul 17, 12:00 pm, [e-mail] wrote:

Ahhh... I can't figure this out, can someone help?

I'm using Access 2003.

I have 2 tables here:

One is for each person, called tblPartners.

One is for each distribution by a partner, called tblDistributions

In tblPartners there is a field called ClosingBalance.

What I want to do is UPDATE ClosingBalance to equal the sum of
distributions by that partner for a period.

So what I originally did was try

UPDATE tblPartners LEFT JOIN tblDistributions
ON tblPartners.PartnerID = tblDistributions.PartnerID
AND tblPartners.Period= tblDistributions.inPeriod
SET tblPartners.ClosingBalance = SUM(tblDistributions.DistribAmt)

But this gives the error "You tried to execute a query that does not
include the specified expression 'ClosingBalance' as part of an
aggregate function."

I also tried:
UPDATE tblPartners
SET ClosingBalance = TotalDistribs
FROM tblPartners INNER JOIN tblDistributions
(SELECT SUM(DistribAmt) AS TotalDistribs, PartnerID, inPeriod
FROM tblDistributions GROUP BY PartnerID, inPeriod)
ON tblPartners.PartnerID = tblDistributions.ParnterID AND
tblPartners.Period = tblDistributions.inPeriod

But gives a syntax error.