Keeping track of room keys

[nitinrao]

Hello everyone,

I'm new to the forums and database management. I'm trying to make a database for a department to keep track of key assignments. This is what I'm trying to incorporate:

- To keep track of the room keys an employee has
- To be able to look up an employee and see which (and how many) keys he/she has
- To be able to look up a key and see which (and how many) employees have this particular key

Could you please refer me to a template that would allow me to do this? Or could you give me a brief tutorial on how to do this?

please help!

nitinrao

 

[pbaldy]

Can't think of a template offhand, but what you describe is a many-to-many relationship. A person can have many keys, and a key can be had by many people. I see 3 tables. A "Persons" table with info about the people, a "Keys" table with info about the keys, and a many-to-many junction table, where each person/key combination would be a record. Querying that table would get you the answers you mentioned.

 

[HiTechCoach]

Could you please refer me to a template that would allow me to do this?

I do not think you will find anything close to this for free. What you need is a Serialized inventory control/Asset tracking system.

I have created several systems to track room keys, pages, cell phones, radios, tools, and etc.

Or could you give me a brief tutorial on how to do this?

If you understand inventory control very well then I hope this is not to:

Inventory Control: Quantity on Hand

You might be thinking that the looks to complex for what you want. Actually your needs are more complex than the example in the link above.

 

[nitinrao]

Can't think of a template offhand, but what you describe is a many-to-many relationship. A person can have many keys, and a key can be had by many people. I see 3 tables. A "Persons" table with info about the people, a "Keys" table with info about the keys, and a many-to-many junction table, where each person/key combination would be a record. Querying that table would get you the answers you mentioned.

Could you please elaborate on this "many-to-many relationship junction"? How can I make this junction? Would it be a relationship or a form?

 

[pbaldy]

Many-to-many describes the relationship between the tables, which is implemented by a junction table:

http://en.wikipedia.org/wiki/Many-to-many_(data_model)