Update / append problem

  • Thread starter Thread starter forcefield via AccessMonster.com
  • Start date Start date
F

forcefield via AccessMonster.com

Hi everyone,
I would like to have a “distinct’ query of all items in table A and sum its
quantity. This part is quite easy, Updating or appending the query result
to a new table B is my problem. If number of table A items increases I would
still like table B to be updated for the same item with a new sum and
appended in table B for a different item with a new sum. I think I probably
need a SQL or code to do it, but I am stucked.
Can some experts help? Thanks

Eg in table A
Item qty
Spanner 5
Screwdriver 6
Spanner 7
Wrench 2
Screwdriver 4

The final result in table B
Item Sum
Screwdriver 10
Spanner 12
Wrench 2
 
Hi forcefield,
on a form add a button and paste this code so everytime you click this
button table B will be updated.

docmd.setwarnings false
docmd.runsql("SELECT item, Sum(qty) AS tot_item _
INTO tmp FROM [table A] GROUP BY item")
set rec=currentdb.openrecordset("select * from tmp",dbopendynaset)
do while not rec.eof
if isnull(dlookup("item","Table B","item=""" & rec![item] & """")) then
docmd.runsql("insert into [table B] (item,qty) select _
tmp.item,tmp.tot_item from tmp")
else
docmd.runsql("UPDATE [Table B] SET [Table B].qty _
= " & rec![tot_item] & " WHERE [Table B].item=""" & rec![item] & """")
endif
rec.movenext
loop
rec.close
docmd.setwarning true

I didn't test the code but I think it should work
HTH Paolo
 
Hi Paolo,
You're a genius. It works with very little tweaking.

If IsNull(DLookup("item", "Table B", "item=""" & rec![Item] & """")) Then
DoCmd.RunSQL ("insert into [table B] (item,Sum) select tmp.item,tmp.
tot_item from tmp")
Else
DoCmd.RunSQL ("UPDATE [Table B] SET [Table B].Sum = " & rec![tot_item]
& " WHERE [Table B].item=""" & rec![Item] & """")
End If

Many thanks.


Hi forcefield,
on a form add a button and paste this code so everytime you click this
button table B will be updated.

docmd.setwarnings false
docmd.runsql("SELECT item, Sum(qty) AS tot_item _
INTO tmp FROM [table A] GROUP BY item")
set rec=currentdb.openrecordset("select * from tmp",dbopendynaset)
do while not rec.eof
if isnull(dlookup("item","Table B","item=""" & rec![item] & """")) then
docmd.runsql("insert into [table B] (item,qty) select _
tmp.item,tmp.tot_item from tmp")
else
docmd.runsql("UPDATE [Table B] SET [Table B].qty _
= " & rec![tot_item] & " WHERE [Table B].item=""" & rec![item] & """")
endif
rec.movenext
loop
rec.close
docmd.setwarning true

I didn't test the code but I think it should work
HTH Paolo
Hi everyone,
I would like to have a “distinct’ query of all items in table A and sum its
Click to expand...
[quoted text clipped - 18 lines]
Spanner 12
Wrench 2
Click to expand...
Click to expand...
 
Back
Top