Designing key tracking DB

[dee]

My wife is school secretary who inherited the job of tracking about
100 keys to about 150 school employees for thee next school year.
Their current system, comprised of 2 non connected spreadsheets, is a
totally useless inaccurate mess.

I thought I might be able to help out by making a simple access
database, but think I bit off just a little more than I can handle.

My idea was to create 2 tables:

TbleKeyInfo
KeyMarking Text field (Primary key) ' contains
markings found on keys
OnHand Number field Quantity on hand
Out Number field Quantity lent out

TblEmployees
FullName Text field (Primary key) 'contains
full name, including 1 digit suffix when required
KeyMarking Text
field ' contains
markings found on keys

The problem is that some employees can have as many as 25 different
keys and some keys can be lent out to almost 100 employees. This sort
of makes it many to many relationship. Maybe I need 3 tables?

I'm hoping to create a select query, which can be used to create a
form with a subform on which I can edit the data and also create new
records as required.

I know that this is a lot of help to ask for, but maybe someone can
help?

Thanks
Dee

 

[Guest]

Dee,

Try creating a third table called something like TblKeysLoans, with the
fields:

PrimaryKey
StaffID
KeyID

Then you can create a one-to-many relationship between the primary key in
your TblEmployees and StaffID, and similarly for TblKeyInfo and KeyID. Then
for data entry you can create a form bound to TBblLoans, with two list boxes
bound to TblEmployees and TblkeyInfo.

Yarra

 

[Guest]

You can find out how many keys are out to employees simply by doing a Totals
query using "group by".

If you have a lookup table with the total number of keys then you can
connect them and work out how many are left.

That is how I would do it anyway

 

[aaron.kempf]

if you're building a database in the year 2007; u should be using SQL
Server and not MS Access.

MS Access is only a front end to SQL Server.

if you don'[t know how to write SQL Server then ask one of your 4th
graders; because SQL Server is easier to use than MDB

 

[Phil Stanton]

Something I need to do myself for Club Keys.

OK A few basic principals. I am assuming that every key has an
identification number - possibly 1 to 100 if there are a hundred keys

I would suggest 3 tables

TblLocks
LockID Auto Primary
LockName Text Indexed(NoDuplicates) e.g. Broom Cupboard, Safe

TblEmployees
EmployeeID Auto Primary
EmployeeSurName Text Indexed
EmployeeFirstName Text

TblKeys
KeyID Auto Primary
LockID Number Long Refers to the lock it will open
EmployeeID Number Long Refers to who has the key
KeyMarking Text

Set up the relationships and enforce referential integrity.

Create a form to add your employees. You will need a dummy Employee for
unallocated keys.

Create a form to add your locks

Create a form based on the keys with combobox for the Lock and a combobox
for the employee.

With sort of structure you can have a subform on your employee form to show
all the keys they have and what locks they fit. Equally on the Lock Form,
you can have a subform showing the keys and the employee who holds them.

Have fun

HTH

Phil

 

[A a r o n K e m p f]

you need A c c e s s D a t a P r o j e c t s

 

[A a r o n K e m p f]

you need A c c e s s D a t a P r o j e c t s

 

[Tom Wickerqueer]

scubadiver is a racist pig, I reccomend avoiding everything he says