Table update

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an items table and a receipts table. In Items, I
have..
1 100
2 200

In Items
2 20
3 300

I would like to update items to
1 100
2 220
3 300 How do I go about doing it?
Thanks
 
Hi,


The easiest solution is to append the second table to the first. This
way, you know what have been "included" or not, already. If you just have:
item=1010, quantity=2020, can you tell if you really added the + 10 items
1010 received this morning? no, you can't, you just have what the sum should
be, and it very prone to error (duplicated entries, missing entries, wrong
entries). If you keep all the elements, even with their dates, you can
answer much more questions, and it is a more robust system. TO get the total
at any point, ... just ask about it:

SELECT Item, SUM(Qty)
FROM myTableWithHistory
GROUP BY Item


and use that query where you used the original table before.



The other solution, unsafe, is to use something like:

UPDATE myTable SET qty=qty+DSum("qty", "receipts", "item=" & item)



and that is assuming item is a numerical entity.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top