Update a table from summary query

[Fredrated]

Is it possible to update a table from a summary query? I always get a
'operation must use an updatable query'.

For example, query 'qSumTracts2000' is the summary query
SELECT PDAsSubareasTracts.CT, Sum(PDAsSubareasTracts.[2000]) AS SumOf2000
FROM PDAsSubareasTracts
GROUP BY PDAsSubareasTracts.CT;

And the query I want to update from is
UPDATE CT_Totals INNER JOIN qSumTracts2000 ON CT_Totals.CT=qSumTracts2000.CT
SET CT_Totals.CT2000 = qSumTracts2000.SumOf2000;

but this generates, as it seems with all of my attempts to update from
summary queries, the above error.

Any way to do this?

Thanks in advance for any help.

P.S. I know that stored summaries can become outdated; in this case I am
working with census 2000 data, a static database, and the projections model I
am working on is made a lot easier if I can store intermediate calculations
into fields for subsequent use.

 

[John Spencer]

You can use the DLookup function to get the value.

UPDATE CT_TOTALS
SET CT_Totals.CT2000 =
DLOOKUP("SumOf2000","qSumTracts2000","CT=" & CT_Totals.CT)

IF the field CT is a text field then you will need to add quotes around
the value of CT_Totals.CT

UPDATE CT_TOTALS
SET CT_Totals.CT2000 =
DLOOKUP("SumOf2000","qSumTracts2000","CT=""" & CT_Totals.CT & """")

OR if you have a lot of records, it may be worthwhile to build an
intermediate (temporary) table using qSumTracts2000 as the source for
therecords

and use that Table to update your CT_Totals table.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Fredrated]

Wow, that worked right out of the box, just cut-and-pasted into a query and
it ran!

I will use this a lot to update summary records, thanks!

Fred

You can use the DLookup function to get the value.

UPDATE CT_TOTALS
SET CT_Totals.CT2000 =
DLOOKUP("SumOf2000","qSumTracts2000","CT=" & CT_Totals.CT)

IF the field CT is a text field then you will need to add quotes around
the value of CT_Totals.CT

UPDATE CT_TOTALS
SET CT_Totals.CT2000 =
DLOOKUP("SumOf2000","qSumTracts2000","CT=""" & CT_Totals.CT & """")

OR if you have a lot of records, it may be worthwhile to build an
intermediate (temporary) table using qSumTracts2000 as the source for
therecords

and use that Table to update your CT_Totals table.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Is it possible to update a table from a summary query? I always get a
'operation must use an updatable query'.

For example, query 'qSumTracts2000' is the summary query
SELECT PDAsSubareasTracts.CT, Sum(PDAsSubareasTracts.[2000]) AS SumOf2000
FROM PDAsSubareasTracts
GROUP BY PDAsSubareasTracts.CT;

And the query I want to update from is
UPDATE CT_Totals INNER JOIN qSumTracts2000 ON CT_Totals.CT=qSumTracts2000.CT
SET CT_Totals.CT2000 = qSumTracts2000.SumOf2000;

but this generates, as it seems with all of my attempts to update from
summary queries, the above error.

Any way to do this?

Thanks in advance for any help.

P.S. I know that stored summaries can become outdated; in this case I am
working with census 2000 data, a static database, and the projections model I
am working on is made a lot easier if I can store intermediate calculations
into fields for subsequent use.