I need help with a database problem

[thinboywide]

Hi i need help with a scenario,

I have made the database and the tables but i am having trouble with the forms and bits of the realtionhip.

here is the scenario

Current System
The case study is based on the video issue recording system of the Video Library (VL). VL specialises in obscure videos that are not readily available from other sources. VL lends only to members of the Video Society, part of the exclusive and very expensive Gentleman's Club. Details of finance are outside the scope of the investigation. Any member of the club is automatically a member of the library. Club membership is for one year. A few new members are admitted on 1st January each year; new members are not admitted during the year.

The library is set up along conventional lines and members are not restricted in the number of videos they may borrow at any one time. Videos are requested for return only if required by another member. Most active users of the library regularly change their videos. Two or three copies of popular titles are available. Two codes are used to describe a video. While a video publication can be identified by a standard 7-character alphanumeric video code assigned by the producer of the video, a particular copy of it has a 6-digit serial number, the copy number, given by the library itself.

In order to borrow a video a member selects it, takes it to the issue desk and gives the video and his club membership card to the librarian. The librarian takes the video ID card from the video and writes the membership number on the card at the end of the list of membership numbers of previous borrowers. The librarian places the card into the loans file in video code order and the member leaves with his video.

When a member returns a video, he presents the video to the librarian. The librarian finds the video card in the loans file, lightly crosses out the member's number and places the card in the video sleeve before returning the video to the library shelves. The crossed-out numbers provide a simple history of the loans of each copy.

To reserve a video which is on loan, a member asks the librarian and leaves his membership number. The librarian finds the record of the relevant video in the loans file and adds the member's number to the reservation column on the video card. The librarian also makes a note of the member who has the video on loan and completes a return request card addressed to that member. The address is taken from the library copy of the club membership book. When the reserved video is returned, the librarian puts the video under the counter and completes a reservation ready card addressed to the member who first requested the reservation, again taking the address from the membership book. The date by which the reserved item must be collected is entered on the card. The member requiring the video can then pick it up from the library and have it issued in the usual manner. Videos which are not collected by the collection date are returned to the shelves or offered to the next reserving member, if any.

The selection, ordering and payment for the videos is outside the scope of the investigation.

On receipt of a new video the librarian has to make out a video card for insertion in the video sleeve. The librarians are also responsible for amending the library's membership book with changes of address, and at the end of the year for collecting the new membership book from the Club Secretary.

Proposed System
Functionality
The functionality of the new system is to be the same as in the current system. That is, operations which can be performed in the current system should be capable of being performed in the proposed system. For example, the history of loans of a copy of a video is required. Paper-based documents will be retained except that video cards are abolished.

Is someone can help me with a simple solution, many thanks

 

[neileg]

Are you asking someone to design this system from scatch? If you have the tables already, how about sharing this?

 

[thinboywide]

Hi neilg

Thanks for your help, i have done the tables, but need help with the relationship, as i am new to MS Access, so if you could come up with a solution that would be much appericiated

Anyway here is the database so far

 

[neileg]

Member table - I would split names and addresses into their individual parts, eg FirstName, LastName, Title etc.
Video table - I would not have duplicate records for more than one copy. What is the Copy table for? I would have Genre as a separate table and hold the genre ID in the Video table - you already have a spelling mistake in the Genre field.
Reservation table - Thiws doesn't show who has reserved the video
Loan table - What is the Loan Membership Number field? I don't understand where you are going with the Copy table. You have a return date but not an issue date?

You must have an idea of what the relationships are to have drawn the tables. You need to have the real world relationships clear in your mind before you can model this in Access.

 

[GaryPanic]

I have not looked at what you ahve doen
but i would of done
contact details
id
title
firstnme
surname
add1
add2
etc
tel
fax
email

genre table
id
genre

video
gener link to above table
name
outto link to contact
reserveddate
reserved by link to customerid

out y/n (on click populate todays date to dateout)
dateout
in (yes/no) on click deleted date out and reset out to n

this I would have though would be enough to work with
reservations

if its out then you can run reports on whats avaiable hook on whats reserved on a a screen - you might need a couple of reservation days field but I could not imagine this being more than 4 -5 ???

you need to work out what is the centre of the D/base in this case its the video and everything must run from this
hows has the video
when , and who wants and when

the customer is linked to the video as is the genre

so get this table/set up right and the rest is releative easy

just thinking out loud

 

[thinboywide]

Hi Thanks for your help,

Could someone if possible build me this database and then i may have a better idea of how it should work, based on the scenario. Or go through what would be involved in each step

This was my orginal idea of an ERD and entities in 3rd Normal Form but i was told there was a mistake in the model

Thanks

 

[neileg]

...i was told there was a mistake in the model

Is this a homework assignment?

 

[thinboywide]

Hi Neil

It's an example assignment, thanks could you possible help me as i am really stuck

Thanks

 

[thinboywide]

Hi i have made the database, with the original ERD i had but i keep getting this error message when adding data into my tables

" Cannot add or change record because a related record is required in table MEMBER"

Could some please help me with this problem

Thanks ( I have enclosed the ERD and entites on a original post)

 

[GaryPanic]

thin

you be lucky to get help on this -- home work is home work

try and show what you have tried and one for the guys may point you in the right direction --

g

 

[boblarson]

Maybe this will help - When you get that message it generally means that you either aren't adding data in the correct order, or you have your keys/relationships slightly askew (not Raskew, mind you Sorry, inside forum joke)

 

[thinboywide]

Hi could someone then point me in the right direction as i am lost and new to access? Could you by any chance help me with my relationship's and entities to make them correct.

Thanks

 

[thinboywide]

Could someone please help me a simple solution to my problem i have tried filling in my tables but i keep getting told that another field is required when i enter some value's into my database

Thanks

 

[RuralGuy]

Why not Compact and Repair the db and then Zip it up and post it here. Someone might look at it. As it is they would just be guessing as to what might be wrong.

 

[thinboywide]

Hi i need help filling in the reservations table, can someone help, also how do you do forms and query's

Thanks

Here is my DB