Inventory Database

[kram941]

Hi everyone,
I am fairly new to Microsoft Access so I have done some of the tutorials to try and learn how to use it. What I am looking for is someone to either help me out with the parts I can not figure out myself or point me towards a resource that will help me solve my problems.
I am currently in charge of creating a database to track our Tool Inventory and usage. What I need it to do is be able to inventory all the tools we have in house and make a record of who checks out what tool and when.

Here is my plan for the database:
Three tables:
1. Employees
2. Inventory


3. Transactions

I am going to have trouble with the form that will be used to check tools in and out. This is how I would like the form to look/work:
Two texts fields, one for the Employee ID Number, and another for Tool ID Number. Both of these are being inputed with a barcode scanner system that we currently use. (to prevent any user input error) Below that is a "Check Out" button. When this is clicked I would like a new record to be created in my transactions table that has the Transaction ID, Employee ID, Tool ID, check out date, and a empty check in date. Also it would need to minus 1 from the quantity on my inventory table for that tool. I don't have any idea how to go about making this check out button work in this way. Oh an I would like it to also verify that the Employee ID number and Tool ID number are both existing records in their respective tables.

I would like a second button for them to scan check tools back in. Or maybe just a second tab on the form. What I was thinking is when they click this button/tab they are presented with a text field to scan in their Employee ID number. A list then shows the tools they currently have checked out and they select correct tool and click check in. And it would no longer come up on the list of tools when they do a search of checked out tools. A more ideal solution woul be they scan their employee ID number and then the tool ID number and it is automatically checked back in.

Let me know if you think this plan would work and if you have any input or can show me where I should be looking for answers.

Thanks for your time,
Mark

[dcrake]

Your idea is quite feasable and I don't see any immediate flaws.

Couple of suggestions

In you employee table have a field that indicates that the employee is active. You don't want to be selecting employees that left 2 years ago.

In you inventory table adopt the same as you don't want to be selecting obsolete parts.

Have a seperate table that records the ins and outs. Again have a quantity field to record how many out and how many in. Also ask yourself is the item being taken out a non returnable stock item, say a pair of safety gloves.

Use append and update queries to mange your stock levels also don't store calculated values in fields when the sum of two fields will give the same answer.

You will also need additional modules to mange the employees coming and goings. Also one for stock items and setting stock levels. This will keep you going for a bit. Come back when you need any further assistance.

David

[kram941]

Your idea is quite feasable and I don't see any immediate flaws.

Couple of suggestions

In you employee table have a field that indicates that the employee is active. You don't want to be selecting employees that left 2 years ago.

In you inventory table adopt the same as you don't want to be selecting obsolete parts.

Have a seperate table that records the ins and outs. Again have a quantity field to record how many out and how many in. Also ask yourself is the item being taken out a non returnable stock item, say a pair of safety gloves.

Use append and update queries to mange your stock levels also don't store calculated values in fields when the sum of two fields will give the same answer.

You will also need additional modules to mange the employees coming and goings. Also one for stock items and setting stock levels. This will keep you going for a bit. Come back when you need any further assistance.

David

Thanks for your reply. How would I go about doing this part:

Use append and update queries to mange your stock levels also don't store calculated values in fields when the sum of two fields will give the same answer.

Also after reading your suggestions I decided I would just have two button that would both create the same record except the check in button would put +1 in the quantity field for the transaction and the check out button would put -1 in the check out field. How would I program a button to do this along with creating the transaction record?
Thank you for any help you can give.