Allocating multiple items to one master key

[gordonab]

I am relatively new to using Access and I have been asked to put together a database for allocating keys at our school. Every member of staff has certain keys allocated to them based on their role. There are also some keys which open multiple rooms. So for example a teacher will need a classroom key and a key for the storeroom and a key for the staffroom. The classroom key and the storeroom key are the same but the staffroom key is different. (This is a simple example).
I have created a table DOOR KEYS with the following fields:
KeyID: Door Key: Key Number: RoleID
A table called ROLES with fields
RoleID: Role
A Table called STAFF with fields
StaffID: LastName: FirstName: DepartmentID: LocationID: DateRequested: KeysIssued: Date Returned: RoleID
A table called LOCATION with
LocationID Location
A table called DEPARTMENT with
DepartmentID; Department

all of the first fields are key fields
I need to be able to link these to allow me to create a form for issuing keys based on the Role. I also need to be able to create a report based on the person's name and the keys they have been issued with.

I am really stuck on how I create the relationships between these tables and how I can get a report something like as follows

Name Key Key Number Date Issued
John Smith Classroom 1 1/1/11
John Smith Storeroom 1 1/1/11
John Smith Staffroom 2 2/1/11

Any help with this would be greatly appreciated

Gordon

 

[jackd]

There is a free data model on Access control here that may be useful to you.
http://www.databaseanswers.org/data_models/access_control/index.htm

I think in general terms: you would

- identify all the privileges/access rights within the scope of your study
- identify roles that relate to these privileges/rights
- assign users to roles

- need a process to assign a role
- need a process to change a role

 

[gordonab]

jackd, thanks for the quick response. I obviously didn't make myself clear enough in the problem descriptor. The keys I am talking about are physical keys that unlock doors, not access control keys. So I am still looking to understand how I can make this db work. Thanks anyway

Gordon

 

[jackd]

Perhaps, but I still think you need to clearly define what the roles are.

Role 1--- needs access to
classroom
storeroom
staffroom

Role 2---????

Role 3 --????

You identified Role in your description. What values did you envision? How would you assign a person to a Role? How do keys relate to Role?

I still think the questions need answers whether physical keys or logical passwords or whatever.

If you want to deal specifically with physical keys and not what they "permit access to" or "privileges" they represent, you could treat the problem conceptually as a "text book loan".

John Smith loaned Book12345 on Jun 21/2010. Any reporting/querying would show John Smith still has Book12345 since there is no Returned record for that Book by That Person.

Good luck.