Performing an UPDATE query based on an aggregate result

[Scott Millar]

I am trying to perform an UPDATE query in MS Access 2000,
but I keep getting an error that the "Operation must be an
updateable query".

Here is a sample of my table (called sample)

SAMPLE
record_id totals totalpercent year
1 85.42 2003
2 45.24 2003
3 75.3 2002

What I want to do is calculate the percentage for each
record, of the totals for each year, and store it in the
totalpercent field. I'm trying to do this in one query.

Here is the query I am trying to use, and the result that
I should get.

UPDATE sample
SET totalpercent = (totals/(SELECT SUM(totals)
FROM sample sum_sample
WHERE sum_sample.year = sample.year))*100

SAMPLE
record_id totals totalpercent year
1 85.42 65.38 2003
2 45.24 34.62 2003
3 75.3 100 2002

I can perform this query in MS SQL Server, just not in
Access, so I'm assuming that it has an issue with using a
SELECT query in an UPDATE query.

Any suggestions??

Thanks,

- Scott

 

[Dale Fye]

Unfortunately, you cannot do this by joining your table to a subquery
which aggregates the Totals for each year, or using the SELECT as a
subquery. However, you should be able to use DSUM() to get your
aggregates for a given year.

Update yourTable
SET TotalPercent = [Totals]/nz(DSUM("Totals", "yourTable", "[Year] =
T1.[Year]"), .000001)
FROM yourTable T1

On the off chance that the sum of the [Totals] column would be zero, I
used the NZ function to force access to divide by a very small number.
You could then easily identify those where the Totals column sum is
zero.
--
HTH

Dale Fye


I am trying to perform an UPDATE query in MS Access 2000,
but I keep getting an error that the "Operation must be an
updateable query".

Here is a sample of my table (called sample)

SAMPLE
record_id totals totalpercent year
1 85.42 2003
2 45.24 2003
3 75.3 2002

What I want to do is calculate the percentage for each
record, of the totals for each year, and store it in the
totalpercent field. I'm trying to do this in one query.

Here is the query I am trying to use, and the result that
I should get.

UPDATE sample
SET totalpercent = (totals/(SELECT SUM(totals)
FROM sample sum_sample
WHERE sum_sample.year = sample.year))*100

SAMPLE
record_id totals totalpercent year
1 85.42 65.38 2003
2 45.24 34.62 2003
3 75.3 100 2002

I can perform this query in MS SQL Server, just not in
Access, so I'm assuming that it has an issue with using a
SELECT query in an UPDATE query.

Any suggestions??

Thanks,

- Scott

 

[Graham Mandeno]

Hi Scott

Yes, this is a pain!

Try using DSum instead:

UPDATE sample
SET totalpercent = (totals/DSum("totals","sample",
"year=" & sample.year))*100

 

[Scott Millar]

Thanks to Dale and Graham for their help. Dale, check out
Graham's code below, as it allows you to add variables
outside the domain, which you can't using "[year]=t1.
[year]". I had actually just changed my code to reflect
this, when Graham added his response.

Definitely frustrating, though, coming from the SQL Server
world.

Thanks again,

- Scott

UPDATE sample
SET totalpercent = (totals/DSum("totals","sample",
"year=" & sample.year))*100