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

[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

On Jun 17, 9:00 pm, dee <[e-mail]:

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

[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
"dee" <pr******************@comcast.netwrote in message
news:11**********************@p77g2000hsh.googlegr oups.com...

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

[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
"dee" <pr******************@comcast.netwrote in message
news:11**********************@p77g2000hsh.googlegr oups.com...

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

[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
"dee" <pr******************@comcast.netwrote in message
news:11**********************@p77g2000hsh.googlegr oups.com...

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

[Bob Alston]

aa*********@gmail.com wrote:

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

bulls***

>
MS Access is only a front end to SQL Server.

bulls***

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

On Jun 17, 9:00 pm, dee <[e-mail]:

>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