Solved

Database design for Lending Library

Posted on 2009-04-08
6
1,665 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 23

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can you open the FORM2 2 31
Top 1 of each supplier 55 55
Access Update Query 1 20
VBA SQL statement - 2 "OR"s and 1 "And" 4 27
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

785 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