Solved

Database design for Lending Library

Posted on 2009-04-08
6
1,761 Views
Last Modified: 2012-05-06
I am creating a lending libary database - much like in a real library, or movie store.  I am having some issues in constructing the design.  
Subject:   Tools in a company are being checked-out.
Tables
  Tools:
      TID
      TName
      TActive = yes/no
  Supervisor
      SID
      Fname
      Lname

Checked out
       C.O. ID
       SID
       TID
       Date Out
       Date In
I need to create a form that will allow a user to Fill in the following:
Supervisor Name,  Date Checked out,  Date Returned,Tool ID,

The problem is how can i place multiple Tool Ids with one Checkout ID?
Since a person can checkout multiple tools.  
0
Comment
Question by:ansonindustries
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:GarthSnyder
ID: 24102791
I'm not sure that I understand your task perfectly, since it sounds like the form you're designing only records the checkout after the fact -- after the tool has circulated and come back, rather than recording the event once at the time of circulation and once at the time of return.

You have the right tables here, but the COID is essentially an artificial primary key and is not in fact necessary. The (ToolID, Date Out) pair is a more natural primary key and will automatically be unique if Date Out includes the time or if tools cannot circulate more than once per day for some reason.

I don't think your question is really about the table schema, though. You are asking how to handle one-to-many relationships in a form, and the answer is that you use a subform. Put the "one" part in the main form (e.g. supervisor) and link those constant fields to a subform that shows the checkout table in datasheet mode. Then you'll be able to enter as many or as few tools as you need for the transaction.
0
 
LVL 24

Expert Comment

by:Eirman
ID: 24102792
You can't beat looking examining a working database similar to your requirements

There is an Access Librarary DB here (Scroll down the page)

http://databases.about.com/od/accessdatabasetemplates/Microsoft_Access_Database_Templates.htm
0
 

Author Comment

by:ansonindustries
ID: 24108184
I have already downloaded the lending library from Access but the problem is that they have 1 to 1 relationships - you can only check out 1 item at a item which can be various tedious.  I might be over thinking this and a subform might be the best option.  just getting stuck on how many columns to allocate for each record on the checkout table.  

I want to create a form that the user will fill out a Date out, Supervisor Name (drop-down) and a date return.  There will be a checkbox for each item that will be taken out.

the form should link to a table resembling the following form.

COID---Supervisor----DateOut----DateIn-----Item1-----item2..........  
Auto#---johndoe-------2/2/09-----2/10/09-----drill------saw-----pump----paintstick.........

I guess how can i keep the flexebility of expansion and how should i track each transaction on a table as multiple items will be taken out by one person in one day and another person in the same day.  keep in mind we will have to add more tools so that means some way of updating the checkbox form automatically.

I hope that gives you guys a better pic.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:ansonindustries
ID: 24110998
ok i think im getting closer in coming up with a more solid solution so if you guys can tell me how i can do the following:

form1
  Contains
  CheckoutID - autogen
  Supervisor name: dropdown
  Date Out
  Date In

Form 2 - Subform
  has a listing of all tools that one can take i guess i will have to manually type them each in with a checkbox next to them.

when a user checks the box how can i place the  corresponding value into the table?

[ ]  drill
[/]  hammer
[ ] blade
[/] saw

Table checkout
 ID tool
1   hammer
1   saw
0
 
LVL 3

Accepted Solution

by:
GarthSnyder earned 500 total points
ID: 24111913
I would like to help, but I'm afraid your question is enlarging into something along the lines of "How do I design a database? How do I use Access?" :-) I can only give you a general direction to go in: your initial schema is the right idea. Any time you get into adding table columns for new items or manually updating a form when new tools are added, you know you're barking up the wrong tree.

Pull the list of tools from the tool table and make it a drop-down list just like for supervisors.

I referred to this as a one-to-many relationship, but that's a simplification from the perspective of a particular transaction. From a schema perspective, it's a many-to-many relationship since multiple supervisors can check out multiple tools over time. You MUST use a linking table (the one that pairs supervisor IDs and tool IDs) and subforms for this application; that's the only way it will do what you want and be maintainable.
0
 

Author Closing Comment

by:ansonindustries
ID: 31568301
thanx
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question