Update Query with Totals doesn't update!

G

Guest

Hi, I'm trying to figure out how to make a sinlge query that will allow me to
update a sum to a table for inventory. I have the following tables:<p>

ztblInvEst: ingrID(txt), begInv, purchIngr, usedIngr(number), adjIngr.<br>
tblBatch: id(number & Key), batchDate<br>
tblBatchSub: parentID(number & frgn Key to tblBatch), ingrID(txt), ingrQty<p>

I set up ztblInvEst to be used as a temporary table for my inventory
calculations. First, I add all ingredients that exist to ztbl using an append
query. Next I have been attempting to create a sum query to calculate
usedIngr from tblBatchSub.ingrQty, and restricting that sum to the dates in
tblBatch.batchDate that I want that period to cover. So far so good.<p>

The problem is now converting this sum query into an update query! Access
keeps telling me this is not an updatable query. I have tried saving the sum
query as qryPurchIngrSum, and using it in an update query where I tie
qryPurchIngrSum.sumQty to ztblInvEst.ingrID. Of course this hasn't worked. I
have tried a gazillion variations and I just can't get this thing to work. I
even attempted to set up an update query using the following SQL:<p>

UPDATE ztblInvEst AS z SET z.minusInv =
DSum("[ingrQty]","tblBatchSub","[ingrID]=" & "'" & [z].[ingrID] & "'")<p>

This partially works, as it will add ingrQty per ingrID, but it will add ALL
of them. I can't restrict this sum between two dates. Seems to me that the
only way around this is to create a second ztbl where I dump the results from
the sum query and then update those results to ztblInvEst. Any ideas out
there to make this with a single update query using a totals?<p>

Will greatly be appreciated. If this was my only problem I'd just create a
second temp table, but there are about 20 instances where I have to run a
similar scenario, and I don't want to clutter access with a bunch of temp
tables and queries.
 
J

John Spencer

You can have multiple criteria in a DSum. Basically the third argument is a
where clause.

DSUM("ingrQty", "tblBatchSub", "ingrID =""" & z.IngrID & """ AND BatchDate = #2006-12-21#")

If BatchDate is being compared to a variable

DSUM("ingrQty", "tblBatchSub", "ingrID =""" & z.IngrID & """ AND BatchDate = " & Format(DateVariable,"\#YYYY\/mm\/dd\#")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update query issue 0
Update Field from Query Result 1
Query Question 3
Update Query Help Please 1
Query Question / 4
Update query 2
Another Query Modification Question 6
SUM in a UNION query 2

Top