Update Query Problem

J

Jeff Garrison

All -

I've been racking my brain with no avail on something that should be an easy
task...

I have two temporary tables that I'm using to come up with a single set of
variables. The first one (tmptblItemsOKToBill) is the result of a query.
The second (tmptblInvoiceTable) is a table that summarizes the data from the
first as well as pull some data from another table. The problem I'm having
is that there are multiple items in the first table that need to be
calculated and placed in the second table.

First Table (tmptblItemsOKToBill)

Project ActualUnits ActualUnitCost
7398 3.0 25.00
7398 1.0 49.00
8491 1.0 375.00

What I need is to update is...

Second Table (tmptblInvoiceTable)

Project Amount
7398 124.00
8491 375.00

The only thing I could find is to use DSum, but I've tried every variation
and the closest it comes is...

Project Amount
7398 499.00
8491 499.00

Any help would be greatly appreciated.

Thanks.

Jeff G
 
P

Pieter Wijnen

Select Project, Sum(ActualUnits*ActualUnitCost) As ProjTotal
From MyTable
Group By Project

HtH

Pieter
 
J

Jeff Garrison

That would work for a SELECT query, but I'm trying to do an UPDATE query.

Jeff


"Pieter Wijnen"
 
J

John Spencer

Best thing is to calculate the sum when you want it. It is almost always a
mistake to store calculated data since it can easily get out of synch if you
change one of the values the calculation is based on and forget to redo the
calculation.

As for your problem, it looks as if you aren't applying criteria in the part
three of the DSUM. AGAIN, I think this is a BAD idea, but if you really
feel the need to do it, then try the following:
UPDATE tmptblInvoiceTable
SET tmptblInvoiceTable.Amount =
DSUM("ActualUnits*ActualUnitCost","tmptblItemsOKToBill","Project=""" &
tmptblInvoiceTable.Project&"""")



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

Jeff Garrison

It's only being used as a temporary table for integration into Dynamics. I
agree with your point of view regarding storing calculated data. But in
this case, it's only a temporary table.
 
J

Jeff Garrison

Let me ask a couple of questions...

1. If I'm doing this via query, don't the both tables (tmptblInvoiceTable
and tmptblItemsOKToBill) have to be joined?
2. What if the Project is numeric?

If I make the modifications and run the query, it says it wants to update 3
rows, but in the tmpInvoiceTable there are only 2. There are 3 records in
the tmptblItemsOKToBill table. It comes back and says that it can't update
the fields due a conversion error. The Amount field in the
tbltmpInvoiceTable is set as Currency and the Unit Cost in the
tbltmpItemsOKToBill is set as numeric and the Unit Cost is set to Currency.
 
P

Pieter Wijnen

Still don't see why you need a temp table
you can export the sum query as easily
but if you insist <g>

UPDATE tmptblInvoiceTable
SET tmptblInvoiceTable.Amount =
DSUM("ActualUnits*ActualUnitCost","tmptblItemsOKToBill","Project='" &
tmptblInvoiceTable.Project&"'")
WHERE Exists (SELECT 'X' From tmptblItemsOKToBill B
Where B.Project = tmptblInvoiceTable.Project)

HtH

Pieter
 
J

John Spencer

DSUM is a VBA function. So you don't automatically need to have both tables
in the query's FROM clause. DSum("X","SomeTable") will sum all the X in
Sometable in a VBA module or in a query that doesn't have SomeTable in it.

If Project is numeric then we drop the extra quote marks.

If you want advice on why your query doesn't work as expected it is a good
idea to post the SQL of the query (MENU View: SQL)

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