Book orders logging system

[smithy]

Hi, first off, im new to this forum, im a first year university student, in need of some help!

My criteria is:

Children’s story books are requested by teachers in a junior school. Books can be purchased from any one of a list of suppliers. The school needs to keep track of who requested the book, their contact details, the age group that the book is suitable for, the book details and the supplier’s details. They also need to be able to track the order; i.e. record the order date, when it was received, and whether or not the teacher has been informed. It would be useful if the system e-mailed the teacher once the order has been placed. In addition the school would like to make reports at the end of the year to see how much each teacher has spent and how much has been spent on each age group.
Additional information, the age range for a junior school is 7 – 10 years old

I currently have 5 tables;

Author
AuthorID
AuthorName

Book
BookID
Title
Price
AuthorID
Age
SupplierID

Teacher
TeacherID
TeacherName
Email
TotalSpent

Supplier
SupplierID
CompanyName
Address1
Address2
Town
County
Postcode
TelNo
Fax
Email

Order
OrderID
TeacherID
OrderDate
DateReceived
TeacherInformed (Yes/No)
OrderValue

Relationships are as below:
http://i63.photobucket.com/albums/h1...Untitled-5.jpg

Ive made the report to track an order, with a query for the user to enter the name of the teacher and only display their orders.

After this, im pretty baffled by it, just not sure where to start, etc.

Im hoping someone could give me some guidance on what forms I should be making to cover the criteria.

Thanks a lot, Dan.

[Rawb]

First off, it looks like you might have some formal database training. Everything is internally consistent, there are no special characters or spaces in table or field names, and the database it normalized! Frankly, that's a rarity for people asking questions on these forums!

But on to your issue: Do you have any Forms set up to allow Teachers to order books? And if not, how does that information get placed into the system? Or is that your problem?

[TheShabz]

Hi. There's 2 things that are pretty glaring to me.

1. The 1-1 relationship between Book and Order puzzles me. Joining on Price means that a teacher can only buy one book at a given price. If two of your books cost 9.99, the teacher cant get both. I would add a foreign key for BookID to your Order table, and make it a 1-M relationship from Book to Order, essentially creating a M-M between Book and Teacher (which is what an Order table usually is). Also look up "sales order many to many relationships." Your Order table shouldnt be a summary of the order, rather a table of each line item on that order. Your summary (invoice) would be a report (or whatever) based off of query that Order table.

2. Really look into Normalization. I see way too many price fields around. This probably goes back to the above though, as I can see how each of them can be different (individual book cost, total order value, total each teacher has ever spent).

As far as the interface goes, it would be form based as described above. The part about emailing the teacher puzzles me though. This sounds like a intro-db course and emailing through Access is a pretty robust VBA procedure.