Update tools stock with query or form

[Guest]

Hi all,
This is my first time here - we have tools in a bin and I have them listed
in a simple access table.
How do I make a query or Form that I can enter the tools taken out and then
it updates the stock automatically.
(i imagine tools in would be a negative of this)
I do not care about invoice etc as all tools stay in the factory - just
stock level

Thanks in advance for any help
Maja W

 

[Guest]

I would recommend four tables --
Employee-
EmpID - Autonumber - integer - primary key
Name - Text
Active - Text

ToolList -
ListID - Autonumber - integer - primary key
Description - Text
Remarks - memo

TOOL -
ToolID - Autonumber - integer - primary key
ListID - number - integer - foreign key
Cost - Currency
AcqDate - Datetime
Remarks - memo
Salavage - Text

Issue -
IssueID - Autonumber - integer - primary key
ToolID - number - integer - foreign key
IssueDate - Datetime
EmpID - number - integer - foreign key
ReturnDate - Datetime
Remarks - Text

You would have a listing of type tools in the inventory. The inventory will
have a separate ID for each tool. All 6-inch Cresent wrenches will have a
separate ID. This facilitates traceability to who it was issued to later.
The Issue table will keep a running record of issue/turn-in.

Your 'balance' query would substract total of item minus count of issues not
returned.

 

[Guest]

Hi Karl,

Thanks for the reply but I do not care who takes it or cost - and do not
want to get involved in 4 tables
Just a field (possible in a second table but same table is ok) that will add
to the stock or take away from the stock (giving me one number the stock in
the bin)

thanks Maja

 

[Guest]

Ok, so omit the employee table and related fields. Omit the cost and other
fields you do not need.