Designing key tracking DB

  • Permalink
  • submit to reddit
  • Email
  • Follow


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

0
Reply promotions.marketing (15) 6/18/2007 5:00:41 AM

See related articles to this posting


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 <promotions.market...@comcast.net> wrote:
> 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


0
Reply aaron.kempf (199) 6/18/2007 5:07:22 PM

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" <promotions.marketing@comcast.net> wrote in message 
news:1182142841.246868.144390@p77g2000hsh.googlegroups.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
> 


0
Reply phil (1005) 6/18/2007 5:12:44 PM

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


"dee" <promotions.marketing@comcast.net> wrote in message 
news:1182142841.246868.144390@p77g2000hsh.googlegroups.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
> 


0
Reply A 6/18/2007 5:31:29 PM

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


"dee" <promotions.marketing@comcast.net> wrote in message 
news:1182142841.246868.144390@p77g2000hsh.googlegroups.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
> 


0
Reply A 6/18/2007 5:38:46 PM

aaron.kempf@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 <promotions.market...@comcast.net> wrote:
>> 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
> 
> 
0
Reply bobalston9 (396) 6/18/2007 6:13:42 PM
comp.databases.ms-access 42280 articles. 10 followers. Post

5 Replies
122 Views

Similar Articles

[PageSpeed] 50


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

DB design opinions
Intro: Hi all, my name is Vams, and I am fairly new to postgresql and totally new to mailing lists, so please bare with me. I have used hypersonic sql and mysql, and now I am trying out postgresql... and so far, very impressive. GJ dev team. Problem: For the location table, should I use two columns (ID PK, name) or just one column (name PK)? Should I FK the id column from the location table or should I FK the name column? Does postgresql keep a reference when using a FK (like pointers in C) or does it actually make a copy and store it (like when C passes a copy in...

class design vs. db design
Hi, if I want to reflect a 1:1 relationship for user:account, the design model may be: for class: A. User ------------- int user_id; int account_id; B. User ------------ int user_id; Account account; for db: C. User (user_id int); Account(account_id int, user_id int) D. User (user_id int, account_id int); Account (account_id int) Questions: 1. How to compare A vs. B in terms of OO design, JDO (java data object) 2. How to compare A vs. B in terms of db schema (with foreign key constraint) 3. C vs. D, which is better? should create user first or account first? ...

DB Design using Berkeley DB
This is my first posting to this group! :) Can you guys point me to some sample design documents (for a simple project) using Berkeley DB? I'm working on a simple "Address Book" kinda project, which has to do basic addition/deletion/updation of a contact, mailing list and search. Also I would like the search to be as fast as possible. Any suggestions on how to design this DB schema? TIA! software007@excite.com (Scott) wrote in message news:<b46e592d.0311211102.4d2d2a17@posting.google.com>... > This is my first posting to this group! :) > > Can yo...

DB Design: Extending the DB schema over time
Dear all, I'm about to design a MySQL data base. I have some basic knowledge about relational databases but am no expert. In particular, it's my first time as designer. The data, the DB is about to hold, is hierarchically organized. In the end, it's a bunch of XML files. Once in a while the XML structure will be extendend incrementally, i.e., new elements and parameters might be present. My concept is that the DB resembles the structure of the XML files. Now my questions: 1) Is it esaily possible to extend DB tables with new attributes (columns) in case the XML structure is ext...

Design is Key
"It has heavily contributed to my subsequent opinion that creating confidence in the correctness of his design was the most important but hardest aspect of the programmer's task. In a world obsessed with speed, this was not a universally popular notion." EW Dijkstra "What led to 'Notes on Structured Programming'" Elliott -- Despite the tests being based upon analysis, if at each point design is not mainly resting upon as Djkastra's says functional decomposition, the interrelationship of abstractions, what is riskiest etc. there is a much greater chance o...

Designing a db
Hi everyone, Having learnt a little about the basics whilst creating my first db I am now about to start creating a good one! I am about to sit down with a large piece of paper and plan it all properly but I have some questions about logistics I wondered if anyone could help with. 1. Can I put command buttons on a table or query? For example I am going to have a table/query with a list of customers (over 1000). I would like the user to be able to select the customer and then click a command button to do an action relating to the selected record. Further to this - if buttons are possible is...

DB-design
Dear All, Is it possible to have an updatable query where rows are the rows of one table and the columns are the fields of another table? More details: I want this for planning the constuction of houses in time TBL_CALENDAR: has all working days. TBL_WORK_GROUPS: has all workgroups working to build a house: WorkGroup1, WorkGroup2, WorkGroup3, WorkGroup4 TBL_CLIENT: has all cients (construction yard): Client1, Client2, Client3, Client4 WeekNr. Date TblWorkGroup.field1 TblWorkGroup.field2 TblWorkGroup.field3 etc. 22 1/6/2007 Combo2selectClien...

DB design
Hi, How to design a generalized database that is used to store datas from any other database. The thing is that the tables and datas of the source database is not known while designing this target database. The design should be a generalized one to store data from any database. Can dummy tables be used? Should there be a provion to create the actual tables from the source db?? Or is there any better way?? Thanks in advance... Baski. "Baski" <mailtobaski@gmail.com> wrote in message news:1141104899.939084.255070@i39g2000cwa.googlegroups.com... > Hi, > &...

design db
In the early phase of data modeling how do you usually start with these steps: data layout, normalization, key definition, and performance tuning Considering that application needs to design from dcratch and it is 100+ tables involve <soalvajavab1@yahoo.com> wrote in message news:1177542646.546450.219350@n15g2000prd.googlegroups.com... > In the early phase of data modeling how do you usually start with > these steps: > > data layout, normalization, key definition, and performance tuning > > Considering that application needs to design from dcratch a...

Database Design Problem with Hibernate Primary Key and Foreign Key
hi, I am a newbie to Hibernate. It seems to me that Hibernate does not allow you to declare both id and composite-id in .hbm.xml mapping. That was why i have a triple primary key in my ENROLLED table as following: CREATE TABLE ENROLLED ( SID INTEGER NOT NULL, CID INTEGER NOT NULL, ENROLL_SINCE DATETIME NOT NULL, EID INTEGER NOT NULL auto_increment, COURSE_ROLE_ID INTEGER DEFAULT 0, LAST_LESSON_ID INTEGER, COMPLETE_BY_DATE DATETIME, GRADE VARCHAR(10), STATUS_ID MEDIUMINT(9), IS_AVAILABLE CHAR(1) DEFAULT 'Y', CREATION_DATE DATETIME ...

how to design db
I'm using sqlite. I need a DB that contains: - a set of general tables - a variable number of sets of users tables. The first set is accessed by every-one. Each of the users' set 'is identical in structure to the others, but it is specific in content respect to the owner. I can manage authentication through my application. What I don't know how to do in a suitable way is the design of a group of identical tables repeated for each user. Could anyone help? Thanks Ciao, Licia. ilochab wrote: > I'm using sqlite. > > I need a DB that contai...

DB and Designer
Hello, I want to test Oracle Designer ... Then i have sucessfully installed Oracle database and Oracle Designer. The database and designer is on the same machine. But when i try to open Oracle Designer ... 1st doubt: what user can i use with Designer? If i logon with SCOTT user ... I get a message "User SCOTT doesnt have a repository installed" Then i go to Repository Administration Utility ... But with user SCOTT i cant complete sucessfully the process. Could you help me anything? Thanks Leandro Loureiro dos Santos LeLouSan wrote: > Hello, > &...

DB design
Hi, How to design a generalized database that is used to store datas from any other database. The thing is that the tables and datas of the source database is not known while designing this target database. The design should be a generalized one to store data from any database. Can dummy tables be used? Should there be a provion to create the actual tables from the source db?? Or is there any better way?? Thanks in advance... Baski. Baski wrote: > Hi, > > How to design a generalized database that is used to store datas from > any other database. The thing is th...

US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343057616)
US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343057616) ====================================================================================== Position: SQL DBA Reference: SMC01989 Location: Austin TX Duration: 4M Skills: Strong understanding of relational database design and development 5+yrs full-time work experience in advanced design and implementation of Microsoft SQL Server-based databases 3+yrs full-time work experience using Microsoft SQL Server database administr...

US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343332403)
US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343332403) ====================================================================================== Position: SQL DBA Reference: SMC01989 Location: Austin TX Duration: 4M Skills: Strong understanding of relational database design and development 5+yrs full-time work experience in advanced design and implementation of Microsoft SQL Server-based databases 3+yrs full-time work experience using Microsoft SQL Server database administr...

US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343357615)
US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343357615) ====================================================================================== Position: SQL DBA Reference: SMC01989 Location: Austin TX Duration: 4M Skills: Strong understanding of relational database design and development 5+yrs full-time work experience in advanced design and implementation of Microsoft SQL Server-based databases 3+yrs full-time work experience using Microsoft SQL Server database administr...

US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343032410)
US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343032410) ====================================================================================== Position: SQL DBA Reference: SMC01989 Location: Austin TX Duration: 4M Skills: Strong understanding of relational database design and development 5+yrs full-time work experience in advanced design and implementation of Microsoft SQL Server-based databases 3+yrs full-time work experience using Microsoft SQL Server datab...

US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343057616)
US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343057616) ====================================================================================== Position: SQL DBA Reference: SMC01989 Location: Austin TX Duration: 4M Skills: Strong understanding of relational database design and development 5+yrs full-time work experience in advanced design and implementation of Microsoft SQL Server-based databases 3+yrs full-time work experience using Microsoft SQL Server datab...

US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343057616)
US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343057616) ====================================================================================== Position: SQL DBA Reference: SMC01989 Location: Austin TX Duration: 4M Skills: Strong understanding of relational database design and development 5+yrs full-time work experience in advanced design and implementation of Microsoft SQL Server-based databases 3+yrs full-time work experience using Microsoft SQL Server datab...

US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343332403)
US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343332403) ====================================================================================== Position: SQL DBA Reference: SMC01989 Location: Austin TX Duration: 4M Skills: Strong understanding of relational database design and development 5+yrs full-time work experience in advanced design and implementation of Microsoft SQL Server-based databases 3+yrs full-time work experience using Microsoft SQL Server datab...

US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343357615)
US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343357615) ====================================================================================== Position: SQL DBA Reference: SMC01989 Location: Austin TX Duration: 4M Skills: Strong understanding of relational database design and development 5+yrs full-time work experience in advanced design and implementation of Microsoft SQL Server-based databases 3+yrs full-time work experience using Microsoft SQL Server datab...

US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343014413)
US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343014413) ====================================================================================== Position: SQL DBA Reference: SMC01989 Location: Austin TX Duration: 4M Skills: Strong understanding of relational database design and development 5+yrs full-time work experience in advanced design and implementation of Microsoft SQL Server-based databases 3+yrs full-time work experience using Microsoft SQL Server datab...

US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343032410)
US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343032410) ====================================================================================== Position: SQL DBA Reference: SMC01989 Location: Austin TX Duration: 4M Skills: Strong understanding of relational database design and development 5+yrs full-time work experience in advanced design and implementation of Microsoft SQL Server-based databases 3+yrs full-time work experience using Microsoft SQL Server datab...

US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343357615)
US-TX-Austin: SQL DBA, Relational DB design, Design/implement, Erwin; 4M (45343357615) ====================================================================================== Position: SQL DBA Reference: SMC01989 Location: Austin TX Duration: 4M Skills: Strong understanding of relational database design and development 5+yrs full-time work experience in advanced design and implementation of Microsoft SQL Server-based databases 3+yrs full-time work experience using Microsoft SQL Server datab...