keeping records of previously entered data

[paulbrog]

I have an inventory list, and a list of employees, an employee writes his name next to the tool he checks out, when he is done someone else puts their name in the field. Is there a way to look and see any and all who have ever checked out a given tool, while only displaying the current employee? This is Access 2003.

[NeoPa]

Yes, but you'll have to give more information before we can point you reliably in the right direction.

What is your table structure?

Welcome to Bytes!

[JustJim]

I have an inventory list, and a list of employees, an employee writes his name next to the tool he checks out, when he is done someone else puts their name in the field. Is there a way to look and see any and all who have ever checked out a given tool, while only displaying the current employee? This is Access 2003.

Certainly. The important thing to realise here is that an employee can check out many inventory items (perhaps several at the same time), and an inventory item can be checked out by many employees (although not at the same time). This is a particular type of relationship called a many to many relationship between the inventory and employees tables.

To model a many to many relationship in Access requires another table (usually called a union table), with each of the main tables having a one to many relationship with the union table.

See the Relationships section in Mary's normalisation article for further details.

We can help you set this up, but you'll have to have an understanding of these concepts first. Let us know if you need more help.

Jim

[paulbrog]

Certainly. The important thing to realise here is that an employee can check out many inventory items (perhaps several at the same time), and an inventory item can be checked out by many employees (although not at the same time). This is a particular type of relationship called a many to many relationship between the inventory and employees tables.

To model a many to many relationship in Access requires another table (usually called a union table), with each of the main tables having a one to many relationship with the union table.

See the Relationships section in Mary's normalisation article for further details.

We can help you set this up, but you'll have to have an understanding of these concepts first. Let us know if you need more help.

Jim

It's basically two tables, one with inventory, tools are the primary key, then various identifiers of the tools follow serial numbers, manufacturers etc, the final field is the employee field, which relates to contact information of that employee. I can have many people check out many tools, what i don't have is the ability to see who checked out the tool prior to the current borrower.

Thanks again!

[FishVal]

It's basically two tables, one with inventory, tools are the primary key, then various identifiers of the tools follow serial numbers, manufacturers etc, the final field is the employee field, which relates to contact information of that employee. I can have many people check out many tools, what i don't have is the ability to see who checked out the tool prior to the current borrower.

Thanks again!

Well.

You should create an additional table storing borrow history.
It should link [Inventory] and [Employees] table in many-to-many relationship.

Regards,
Fish

[NeoPa]

A Many-to-Many relationship is usually managed by creating a special table which has FK links to both of the tables you need to link between. It normally consists simply of those two key fields (which together can make up the PK of the table).

This gives a One-to-Many link between each of the two main tables and this Cross-reference table.

[OldBirdman]

I would think that the link table would also contain a dteCheckOut and a dteCheckIn field. This would allow for finding all tools checked out - no checkin date - and to create a log of tool use by tool or by employee.

[NeoPa]

Good thinking OB. I agree, that would help the project, and would logically fit well in this Many-to-Many linking table.