access 2003 database problem..........Help !!

  • Thread starter Thread starter cab626
  • Start date Start date
C

cab626

Hello All….



I’ve been pulling my hair out trying to figure this problem that I hav
with my database. Please excuse me if I don’t have all my terminolog
correct. I want to be able to keep track of our spare parts inventory
and allow user to “check out” parts from stock. I have made a tabl
that I called “Spare Parts inventory.” In that table I have severa
fields but most importantly are the field “In Stock, and I also have
field in that table labeled “remove from stock.” There’s my problem …
want to be able to subtract “remove from stock” from “stock” then hav
the new valve be updated to the “stock” field. Example, 10 apples “i
stock”. I “remove from stock” 4 apples. Now I have 6 apples in stock.
can’t seem to figure it out………. Please Help !!!!

i was told that all i would need to do is retrieve the current value o
"stock" from the database, either by opening a recordset or by doing
DLOOKUP, then subtract the "remove from stock" value from "stock", an
finally update the "stock" field, again by either using a recordset o
via an SQL statement???? How is this done ???


Many Thanks

E
 
EM,

In my humble opinion, what you were told is not really a good idea. In
my opinion, you shouldn't have a "Stock" field at all. This adds
unnecessary complication to your database without contributing any
benefit. It would be a much better idea to simply have a field for
Quantity, and a field for TransactionType (i.e. to record removed from
stock of added to stock), and then whenever you need the current stock
levels it is immediately derivable via a very simple query calculation.
 
Hello Steve

Thanks for the pointer. Would it be too much to ask you to walk me thr
this "Transaction Type (i.e. to record removed from
stock of added to stock)"

Thanks for pointing me in the right direction...

Enriqu
 
Enrique,

To oversimplify, let's say you have a Transactions table with fields
like this:
TransactionID
TransactionDate
Item
Quantity
TransactionType

So, suppose then this table has records like this:

1 6-Jun-04 apples 10 Added
2 6-Jun-04 pears 20 Added
3 7-Jun-04 apples 5 Removed
4 8-Jun-04 apples 2 Removed
5 8-Jun-04 pears 17 Removed

Then, whenever you need a "read-out" of the stock levels, you just opne
a form or print a report that is based on a query from this Transactions
table. The SQL of such a query would look like this:
SELECT Item, Sum([Quantity]*IIf([TransactionType]="Added",1,-1)) AS Stock
FROM Transactions
GROUP BY Item

You can further simplify this by making the TtransactionType a Number
data type, and use an Option Group on your form to enter the data, and
set the Option Value property of the "in" option button to 1, and the
Option Value property of the "out" option button to -1. Then your query
will be like this...
SELECT Item, Sum([Quantity]*[TransactionType]) AS Stock
FROM Transactions
GROUP BY Item
 
Back
Top