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.
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.