Can I use Access for library-type system?

[Newbie456]

I would like to use Access to keep track of equipment as it is checked in and out of a lab. This would be similar to tracking books checked out in a library - in that I would like to display if equipment is "in" or "out"

The equipment wouldn't have a specific name, so that is where the difficulty lies. Essentially, I would like to create a database that stores the number of items in my lab inventory (ie, 30 brooms, 10 shovels, etc.) and then I could click a button to "check out" items (such as 3 brooms) to a specific employee. The total number of items in the lab would then be reduced to show what is in house.

I'm thinking I can design it so that there is a table for "lab" and each employee.

My question, then, is can I shuffle items between tables as they are moved around? So that it always knows I have 10 shovels, even if only three are in the lab and 7 are out with various employees? Any ideas?

 

[Remou]

I think you need to read up a bit:

http://r937.com/relational.html

http://www.alphabetcitydataworks.com/fundamentals.htm

(same as above, different format)
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

Also, it is best to post in Microsoft: Access Tables and Relationships Forum: forum700

You need to set up something like:
[tt]Items Table
ItemID -> Primary Key
ItemDescription
Maybe: CurrentStatus In/Out

History Table
HistoryDateTime -> Primary Key
ItemID-> Foreign Key
CheckOutDate
CheckInDate
EmployeeID -> Foreign Key

Employee Table
EmployeeID -> Primary Key[/tt]

This is just a very rough thought.

 

[nq]

May I suggest that you take the time to identify EVERY item in your inventory with a number or a code.

Suppose you have 10 shovels and lend 3 to person A, 4 to person B and 2 to person C.

At the end of the day, if only 8 shovels are returned, who has the remaining shovel?

If every item had a number or code, then it would be easy to identify who had not returned it. And your database design would be much easier.

 

[Newbie456]

I suppose I was trying to avoid the extra work of labelling every piece of field equipment - with shovels it is easy, but when it comes to things like buckets (100s can go out at a time)it can be unreasonable.

 

[nq]

Use barcodes or RFID. Stick labels on and use a reader to enter items into the database.