Stumped on book db structure

[Tanya]

Hi
I am stumped on the next stage of my database. Within a school faculty there will be many book titles and copies of each. I am attempted to streamline the process of recording the books on the shelf and those borrowed to students. At present this is recorded by hand which leads to incomplete records. Since textbooks are very expensive it makes sense to me to include this module in my db.

I have attached a copy of what I have come up with so far, my main question is have I got the relationships right? I've been playing around with the relationships for days and can't get my form to work either. Perhaps I have overcomplicated the project by including faculty and subject?

Any suggestions greatly appreciated.

cheers
Tanya

 

[RainLover]

Your Tables seem to be correct.

Your SQL Statement for SubFrmBookLoans is incorrect.

Try this

SELECT tblStudentsClasses.ClassID, tblBooks.ISBN, tblBooks.FacultyID, tblBooks.Title, tblBooks.Description, tblBookLoans.BookLoanID, tblBookLoans.StudentClassID, tblBookLoans.DateIssued, tblBookLoans.DateDue, tblBookLoans.DateReturned
FROM tblStudentsClasses INNER JOIN (tblBooks INNER JOIN (tblBookCopies INNER JOIN tblBookLoans ON tblBookCopies.CopyID = tblBookLoans.CopyID) ON tblBooks.BookID = tblBookCopies.BookID) ON tblStudentsClasses.StudentClassID = tblBookLoans.StudentClassID;

 

[Tanya]

Thank for your reply. I tried what you suggested and it didn't work.

I have since attempted to reorganise the tables a bit and playing with the form, but now I am getting an error message relating to a table and requiring a value?

I realise I will need to set up cascading combo's for the form, but still wondering if the problem is with the relationship schema.

cheers
Tanya

 

[RainLover]

I opened all of your form and they all worked.

Where exactly is your problem.

Please name the from and the control if necessary.

 

[Tanya]

Main form is frmclasses and the problem is with subfrmLoanBooks

I found that if you enter one record, that is fine, however when you click on a new row you get the following message:

You cannot add or change a record because a related record is required in table tbltextcopies

Otherwise the form appears to work.

 

[RainLover]

It is late

Will have a look first thing in the morning.

Hope that is OK

But I think your problem is with the Child Master thingy

 

[Tanya]

That is fine, I appreciate your patience and time. It was late here also when you posted your last response and morning now in Sydney.

regards
Tanya

 

[RainLover]

Last night had had a quick look and things seemed fine. However this morning I am having a closer look and there are problems with your Relationships.
In the Relationship window you need to add the missing two Tables, namely tblStudents and tblClases. Then you need to relate these to the other tables.
Now to look closer at your design. tblBooks hasa Primary key of BookID which is AutoNumber. This is good and is my preference to have Autonumber as the PK in all Tables. Your Foregin Keys are FacultyID, AuthorID and PublisherID. The latter two are correct except for their default value. They should not default to zero. Leave that blank. FacultyID does nothing. Perhaps you have other tables so I won't worry about that.
tblBookCopies. Why do you have the FK of CopyID. again this does nothing.
I added a relationship between tblStudenClasses and tblClasses by joining ClassID. That is fine but again you have 3 more FKs that do nothing.
Now I tried to join tblStudents to tblStudentClasses via StudentID but was not allowed to as you have data in tblStudentClasses that does not match tblStudents, so I was not able to enforce referential integrity.
So I looked at tblStudents and you have gone away from your own convention of using Autonumber as your PK. So I changed it and changed some data in the tables so I was then able to create the proper relationship.
tblStudents has not been Normalised. Suburb, State, PostCode and Language could all be held in lookup tables.
This SubFrmBookLoans seems all wrong. You are using tblBookLoans as the recordsource, then you have comboBoxes that lookup a Totally Unrelated Table.
I think the whole Relationship structure needs to be redone. Most of the tables are correct it is just the way they are related to each other that is incorrect.
Now I am not a Libriarian nor do I even know how to spell the word, and I don't know what your thinking is, so it is difficult to help.
I suggest that you print out the relationship page and have a look at it in conjunction with your Form relationships. If they do not match then you need to fix that.
I have said a lot but I don't know if I have helped much. But please post back and if I can I will try to help further.

 

[Tanya]

Thank you for this break down as you see it, I will take it on board and see what I come up with then get back to you.

You did mention a few FK which did nothing, the reason being that this library is a small part of a much greater project I am working on. [I have attached a copy for your interest]

As a teacher, I have seen many different methods of maintaining student grades, attendance and records relating to texbooks, but nothing that really does it all for me. I have been developing an Excel workbook to do most of this and have been using it successfully for 18 months or more, in fact my colleagues also use it. As a professional development I have decided to create an Access database to do the same, only with the added benefit of being able to create reports and use it across a whole faculty, even whole school further down the road.

Another project I worked on in 2008 was for facilitating the mentoring of students to assist them achieve their personal best both academicly and personally.

I have found teaching databases at high school level is quite different to developing a database for a real world problem specific to the needs of the user.

I am very grateful to all the support I have and continue to recieve through this forum. Again Thank you.

Tanya

 

[RainLover]

Tanya

Here is my thinking.

You have 3 Tables, tblTextBooks, tblBookLoans and tblBooks. I think tblBookLoans is the key table here. It should have a Many to Many Relationship with the Books to be leant out and to the Student who is borrowing them.

So you would have

TblBook
BookID as Autonumber
Description as Text
etc

TblStudent
StudentID as Autonumber
FName as Text
LName as Text
etc

Now you need to join these two tables together using a join table which will give you a Many to Many Relationship.

TblBookLoans
BookLoansID as Autonumber
BookID as Foregin Key to TblBook
StudentID as Foregin Key to TblStudents
DateIssued
DateDue
DateReturned
Etc

Just in case you do not understand the concept of Many to Many.

A Student can borrow many different books (or even the same book) at different times.
A Book can be lent to many different Students.


I do not understand why you have tblBookLoans related to tblStudentClasses and not to the Student.

Hope this helps

 

[Tanya]

Hi
StudentID is not an autonumber because it is taken from the Departments own Oasis Database and I am trying to relate the borrowing of a book to a class and faculty.

For instance, one student is enrolled in many classes hence studentclassID and within each faculty there may be more than class the student is enrolled in i.e. IPT, SDD & VET IT or Ancient History and Modern History, now when I want to see which books the student has borrowed, I don't want to see the books for all classes, only the class I am managing myself as a teacher.

On the topic of autonumbers for PK's I am not sure your reasoning for having all PK's as autonumbers and happy to hear your view on this.

cheers
Tanya

 

[RainLover]

I believe I am catching on to what you are doing. (Sorry for taking so long)

You have a Form subfrmBookLoans.

I think your recordsource should simple be the Table tblBookLoans. Not the three tables you are currently using.

If this solves your problem then we could say that your Tables are infact correct.

With regard to Autonumber. You don't have to use them, but I do. The choice is yours.

 

[Tanya]

Thats ok, perhaps I didn't explain myself so well.
I am going to leave this problem for a couple of hours and then get back to it. I will keep you informed.
Cheers
Tanya

 

[Tanya]

I had another look at the tables and took your suggestion about querie for from related only to tblbookloans however, it doesn't allow me to create another record for another student, then return to previous to see the history. This is very frustrating given our aircon is on the blink and it must be 40 degrees inside. I've been working down stairs without internet connection and its almost impossible to concentrate in this heat.

Will keep you posted.

 

[Tanya]

Hi
I have finally solved this problem. You were on the right track! I've simplified the whole idea.
Thank you.
Regards
Tanya

 

[RainLover]

Glad to hear.

Suggestions

Do not use anything in your naming conventions other than Alph Numeric plus underscore.

Also if you have a Form named FrmMain

Then its sub should be

FrmMainSub not

SubFrmMain

I think you can work out the advantages of sorting by this convention.

 

[Tanya]

Taken on board, thanks.

 

[RainLover]

Hope I have helped.

Bye.