Designing key tracking DB

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
6/18/2007 5:00:41 AM
comp.databases.ms-access 42527 articles. 11 followers. Post Follow

5 Replies
228 Views

Similar Articles

[PageSpeed] 40
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
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
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
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
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
bobalston9 (396)
6/18/2007 6:13:42 PM
Reply:
Similar Artilces:

US-TX-Austin: Sr. SoC Design Eng., ASIC design expert, Deep Sub-micron design; P (45322114408)
US-TX-Austin: Sr. SoC Design Eng., ASIC design expert, Deep Sub-micron design; P (45322114408) ============================================================================================== Position: Sr. SoC Design Eng. Reference: SMC01500 Location: Austin TX Duration: Perm Skills: MS in EE or MS in Computer Engineering. 5 to 10 years experience. Must have proven expertise in ASIC design and a firm grasp of all aspects of Deep Sub-micron design flow, including physical design. Scope: ASIC des...

How to Design a web application
Hi,I am new to this group and I have been working on Java .Could anybody plz tell me how to aproch to design a web applicationwith proper architecture andwhat is design patterns.How to use them .Thank you,Sudheendra. sudheendra wrote: > what is design patterns. "Design patterns" is a literal phrase, referring to structures of code, process or analysis that recur in many situations. These patterns are useful in system design. These patterns go by conventional names, though perhaps you have seen them yourself and had a different term for the same idea. One well-known (and of...

US-TX-Austin: Applications Eng., Power Supply design, MCU HW/firmware design; C- (45316257602)
US-TX-Austin: Applications Eng., Power Supply design, MCU HW/firmware design; C- (45316257602) ============================================================================================== Position: Applications Eng. Reference: SMC01628 Location: Austin TX Duration: C-P Skills: Bachelors in Electrical Engineering required, Masters preferred. Hands-on experience in complex power supply design with a strong understanding of supply topologies (e.g. full bridge, half-bridge), their control architectures, lo...

US-TX-Austin: Design Eng., Analog integrated Circuit design, Switched capacitor (45312614409)
US-TX-Austin: Design Eng., Analog integrated Circuit design, Switched capacitor (45312614409) ============================================================================================= Position: Design Eng. Reference: SMC01551 Location: Austin TX Duration: Perm Skills: Requires a MS/PhD preferred in Electrical Engineering and general knowledge of analog integrated circuit design with specific emphasis on high speed amplifiers and D/A or A/D converters. Skills in switched capacitor circuit design at bl...

Alt key calls paintImmediately() on all my JLabels
I'm trying to figure out how to tell the system that I don't want a particular JLabel to paint when someone presses the alt key in my app. I think it has to do with mnemonics, but I've set my jlabel to have no mnemonic (setDisplayedMnemonicIndex(-1) which was already the default). I've also tried setting: setLabelFor(null); setEnabled(false); setIgnoreRepaint(true); setFocusable(false); setToolTipText(null); setTransferHandler(null); But I still get a call to paintImmediately() whenver the user presses the alt key anywhere in the app. setVisible(false) is the...

US-TX-Austin: Senior RF IC Design Engin, high speed circuit design transistor (45357270240)
US-TX-Austin: Senior RF IC Design Engin, high speed circuit design transistor (45357270240) =========================================================================================== Position: Senior RF IC Design Engin Reference: ZYD00147 Location: Austin TX Duration: Skills: Experience in state-of-the-art high speed circuit design transistor level circuit. Hands on measurement experience solving difficult die-level design problems in products that shipped in high volume. MSEE + 5 years (or equivalent) RF I...

US-TX-Austin: AutoCAD Design Tech, 3-5yrs exp drafting, AutoCAD2000 or ver.14; c (45341457608)
US-TX-Austin: AutoCAD Design Tech, 3-5yrs exp drafting, AutoCAD2000 or ver.14; c (45341457608) ============================================================================================== Position: AutoCAD Design Tech Reference: SMC01967 Location: Austin TX Duration: contract Skills: Associates degree in Design, AutoCAD or equilevant 3-5yrs exp in drafting, using document management systems, AutoCAD and computer-aided design software. AutoCAD 2000 or version 14 direct exp. Manual Drafting ...

right track? hulls mockup.
mostly I am wondering if I am on the right track, or if something important is still missing. sorry, I often can't really follow the literature so well, and a lot doesn't seem to be that close of a fit with my situation, ... so, pardon if I fail to realize some things (imo, in general there seems to be a lack of sites going into this kind of thing in an easily understandable manner...). ok, for testing reasons, I beat together a mini mock-up of my physics engine, things don't need to be exact, just good enough for me to determine if stuff seems ok. the mock up was also ma...

Trouble designating a default printer
I have an HP4650 and an Epson CX4800. I am using Windows Xp Home. When I try to change the default designation from the HP to the Epson the computer upon closing the control panel reverts to the HP. I can get the Epson to print, but this is aggravating. Any advice would be appreciated. -- Mike "Mike" <mwos@optonline.net> wrote in message news:DrtGf.797$gA6.65@fe10.lga... >I have an HP4650 and an Epson CX4800. I am using Windows Xp Home. When I >try to change the default designation from the HP to the Epson the computer >upon closing the contr...

US-TX-Austin: VLSI Circuit Designer, 5+yrs exp CMOS design, DSP design; DH (45341514401)
US-TX-Austin: VLSI Circuit Designer, 5+yrs exp CMOS design, DSP design; DH (45341514401) ======================================================================================== Position: VLSI Circuit Designer Reference: SMC01756 Location: Austin TX Duration: DH Skills: BSEE (MSEE preferred). 5+yrs exp in CMOS custom circuit design. Microprocessor or DSP design experience. Core skills must include high performance custom circuit design and should include experience in one or more of the fo...

US-TX-Austin: Sr. SoC Design Eng., ASIC design expert, Deep Sub-micron design; P (45317132410)
US-TX-Austin: Sr. SoC Design Eng., ASIC design expert, Deep Sub-micron design; P (45317132410) ============================================================================================== Position: Sr. SoC Design Eng. Reference: SMC01500 Location: Austin TX Duration: Perm Skills: MS in EE or MS in Computer Engineering. 5 to 10 years experience. Must have proven expertise in ASIC design and a firm grasp of all aspects of Deep Sub-micron design flow, including physical design. Scope: ASIC des...

Delete key does crazy things
Hi all In a browse that has no update procedure , if the delete key is pressed the system opens the same browse endlessly until windows displays low resource message and i have to terminate the application. The delete button is hidden and i have the delete keycode listed as alerted in window and browse. What can be causing this ? Warm regards Elli CW5peB,TPS,Legacy , win98 ...

Database design conundrum
I currently have a database table that is getting out of control, it is designed to hold information regarding a paddock and when it was initially setup the information needed for each paddock was identical in nature. Original design of Paddock table: -PaddockID -PaddockName -RegionID -CropID **RegionID and CropID are linked to the Region and Crop tables respectively** But as the system has grown, the number of crops has grown and with this growth the number of variables in the paddock table has grown, as some of the new crops have crop specific variables. Current design of Pad...

US-TX-Austin: RF Mixed Signal Design Eng, Analog design exp., wireless design; C (45315314404)
US-TX-Austin: RF Mixed Signal Design Eng, Analog design exp., wireless design; C (45315314404) ============================================================================================== Position: RF Mixed Signal Design Eng. Reference: SMC01602 Location: Austin TX Duration: C-P Skills: MSEE or MSCE or BSEE plus 5 years of experience. Exp designing mixed-signal integrated circuit design at the transistor level. Strong emphasis on analog design required. Wireless design experience at GHz operating frequ...

US-TX-Austin: RF Mixed Signal Design Eng, Analog design exp., wireless design; C (45319957621)
US-TX-Austin: RF Mixed Signal Design Eng, Analog design exp., wireless design; C (45319957621) ============================================================================================== Position: RF Mixed Signal Design Eng. Reference: SMC01602 Location: Austin TX Duration: C-P Skills: MSEE or MSCE or BSEE plus 5 years of experience. Exp designing mixed-signal integrated circuit design at the transistor level. Strong emphasis on analog design required. Wireless design experience at GHz operating frequencies ...

US-TX-Austin: Network Eng./DB Admin, PC/MAC HW/SW/OS knowledge, SQL Server (45311014409)
US-TX-Austin: Network Eng./DB Admin, PC/MAC HW/SW/OS knowledge, SQL Server (45311014409) ======================================================================================== Position: Network Eng./DB Admin Reference: SMC01544 Location: Austin TX Duration: Skills: 3-5 years relevant experience. General knowledge of PC/MAC hardware, software and OS. Good verbal and written communication skills. May be required to be on-call. Eighteen months of college level courses/AA degree required. BS/BA ...

US-TX-Austin: Solutions Architect/Sales Eng.Design/config/implement SW apps; C-P (45328757605)
US-TX-Austin: Solutions Architect/Sales Eng.Design/config/implement SW apps; C-P (45328757605) ============================================================================================== Position: Solutions Architect/Sales Reference: SMC01698 Location: Austin TX Duration: C-P Skills: BS Degree in Computer Science, MIS, or equivalent combination of education and experience. Exp designing, configuring and implementing custom and packaged enterprise software applications (Java and .NET preferred). ...

TextWrangler (by design) ignores font settings in old files.
Hi. I would like to change all of my TextWrangler files (.txt mostly) to use the font setting I choose in Preferences. TextWrangler saves state, so the font setting is saved when you save a file. Newly created files have my new font setting, but old files I saved previously do not. I open old .txt files and they have my old font setting, ignoring my current Preferences setting. I hate this! How can I globally change the font setting for all .txt files on my disk? (No fear of the Terminal; I am a unix-geek.) Thanks. Roger Carlson In article <1139792580.391761.187630@o13g2000cwo.goog...

Game Design FAQs updated #17
Last posted on newsgroup November 1, 2009 Most recent updates to the FAQs (since the November 1, 2009 posting): 1. Links page - Added some links. 2. FAQ 3 - Added a link and a crosslink. 3. FAQ 58 - Fixed broken links, added information about what to do if you find a broken link. 4. FAQ 65 - Added a link. 5. FAQ 56 - Added important links. 6. FAQ 47 - Added yet more wise sayings by Dr. Laura, and a wonderful bit of "reverse wisdom" by "MadApples." Added an Army Engineers quote, a Derek Bok quote, a Katie Uhlaender quote, a Dr. John quote, a Sai Baba quote, a ...

US-TX-Austin: Sr Analog Design Eng, �IC design, A/D & D, DSP, ADC design (45356532411)
US-TX-Austin: Sr Analog Design Eng, �IC design, A/D & D, DSP, ADC design (45356532411) ======================================================================================= Position: Sr Analog Design Eng Reference: MLS00011 Location: Austin TX Duration: Skills: � Design and develop high-speed mixed-signal ICs for highly integrated products and chip system level solutions. � Analog IC designers will contribute to all aspects of product development including analog architecture determination, algorithm...

Port classes design
Does anyone have any suggestions regarding the design of a series of classes to provide an access framework for serial and parallel ports? After looking fairly extensively at the available class libraries and alternatives I came to the conclusion that the javax.comm hierarchy appeared to have many things going for it. class java.lang.Object o interface javax.comm.CommDriver o class javax.comm.CommPort + class javax.comm.ParallelPort + class javax.comm.SerialPort o class javax.comm.CommPortIdentifier o interface javax.comm.CommPortOwnershipListen...

Separating design from code - help a Perl monger
Hi there, I've recently been dabbling in JSP and servlets after years of hacking Perl *shucks*. What I would like to do it to seperate the design of my webpages i.e. the HTML as much as possible from the code i.e. the java. Now I stand to be corrected but JSP don't seem to be the solution as you end up (well I do) with loadsa code in a page which is then compiled into a servlet anyway - yuk, yuk. Servlets don't seem quite right, though I'd rather use servlets than JSP, as I seem to end up with servlets full of HTML - yuk. In Perl I'd use something like HTML::Template whi...

Design Problem with GUI sub vi
Hello community, in general, I would like to separate the GUI from the data, but I get a problem with this design approach in LabView: I often have sub VIs that are supposed to control something. Therefore, these VIs consist of a control loop. A GUI window should show the regulation process (showing how the tracking error is hopefully approaching zero etc.). As I said, this GUI should be a separate VI (separate from the control loop VI). Since the GUI has to be updated on each sample, it is clear that the GUI VI should be inside the control loop, and therefore, it should be a sub VI of the c...

US-TX-Austin: VLSI Circuit Designer, 5+yrs exp CMOS design, DSP design; DH (45344357604)
US-TX-Austin: VLSI Circuit Designer, 5+yrs exp CMOS design, DSP design; DH (45344357604) ======================================================================================== Position: VLSI Circuit Designer Reference: SMC01756 Location: Austin TX Duration: DH Skills: BSEE (MSEE preferred). 5+yrs exp in CMOS custom circuit design. Microprocessor or DSP design experience. Core skills must include high performance custom circuit design and should include experience in one or more of the fo...

books, font design size, psutils
Greetings! I'm experimenting with converting a plain text Project Gutenberg book into a real printed book using *plain TeX*, some OTF fonts from Adobe and the psutils suite. I'm typsetting the pages full size, but with fonts scaled up by 1.5 times, and then using psresize, pstops, psbook and psnup to produce 2-up signature pages. My question -- in my original, full size pages, do I use the smaller font design sizes and scale them up so that when they're reduced during the production of the pages they're normal size, or do I use the larger font design sizes, which then get sh...