Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Database design for Lending Library

Posted on 2009-04-08
6
Medium Priority
?
1,878 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
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 1500 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

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 a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

971 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