Update Query from table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Samantha - 14 Aug 2006 22:17 GMT

I need to update a summed value from table WO to table Table1.

In table WO, I have fields: PartNumber, Quantity, and WONum. The is the
many table.
In Table1, I have fields: Component and TotalQuantity.
What I would like to do is update Table1's fields with the summed Quantity
from WO table, give that PartNumber = Component.

So far I have:
UPDATE Table1 INNER JOIN WO ON Table1_Component = WO.PartNumber SET
Table1_TotalQuantity = DSum("[Quantity]","WO","Table1.[Component]='" &
[WO].[PartNumber] & "'")
WHERE (((WO.WONum)="6k8-16246"));
It's giving me an error "Reports can't update all records in the update
query."
I've been working on this query all morning and surely would like some
pointers in the right direction! Thanks in advance!

Reply to this Message

Jeff Boyce - 14 Aug 2006 23:37 GMT

Samantha

Why? As in "why would you want to put a calculated value in your 'parent'
table?"

Any time you need the sum, you can run a Totals query to get it.

To do a Totals query, create a new query in design mode, add the table, add
the field you want summed, click on the Totals toolbar button (the greek
"sigma"). This will add a new row and put "GroupBy" under your field.
Change this to "Sum".

Regards

Jeff Boyce
Microsoft Office/Access MVP

>I need to update a summed value from table WO to table Table1.
>
[quoted text clipped - 13 lines]
> I've been working on this query all morning and surely would like some
> pointers in the right direction! Thanks in advance!

Reply to this Message

Samantha - 14 Aug 2006 23:54 GMT

Jeff, this only a partial of the total picture. This is part of some
multiple complex queries, and I am only adding a small piece of information
to gather more information for the end report. Would you know what's wrong
with this query as I have it now? thanks in advance.

> Samantha
>
[quoted text clipped - 30 lines]
> > I've been working on this query all morning and surely would like some
> > pointers in the right direction! Thanks in advance!

Reply to this Message

Jeff Boyce - 15 Aug 2006 00:14 GMT

Samantha

I'm wondering about your using the DSum() function. Consider creating a
query that Sums Quantity GroupedBy PartNumber (this would be a totals
query).

Then join that query to the main table on the part number.

(I still recommend that you NOT store the calculated value, unless you can
describe a business need that calls for this. You need to be aware that as
soon as you've stored a calculated value, the burden is on you to keep the
calculated value and the underlying components "in sync".)

Regards

Jeff Boyce
Microsoft Office/Access MVP

> Jeff, this only a partial of the total picture. This is part of some
> multiple complex queries, and I am only adding a small piece of
[quoted text clipped - 41 lines]
>> > I've been working on this query all morning and surely would like some
>> > pointers in the right direction! Thanks in advance!

Reply to this Message

John Spencer - 15 Aug 2006 12:40 GMT

What you have posted will certainly fail. You should be referring to Table1
dot Component and Table1 dot TotalQuantity. You have underscores where the
periods should be. Try the following.

UPDATE Table1 INNER JOIN WO
ON Table1.Component = WO.PartNumber
SET Table1.TotalQuantity =
DSum("[Quantity]","WO","Table1.[Component]='" & [WO].[PartNumber] & "'")
WHERE (((WO.WONum)="6k8-16246"));

As mentioned elsewhere in this thread, storing calculated values is
**usually** not a good idea. Usually, because for performance reasons in
complex reports it is sometimes a good idea to store calculated data
temporarily in a table. You just have to be careful to keep things in
synch.

Reply to this Message