Hi, everyone! I'm new here, and signed up in order to ask some of the more experiences members for their help in designing a relational database system that is capable of doing numerous things.

It has to store information relating to clients, courses, tutors, resources, bookings, invoices, statements, and ledger accounts.

The idea is that it will be built in Microsoft access, and contain a 'Booking Form', which will allow the user to enter the details of the booking, while at the same time updating the resources table, invoices table, statements table, and ledger accounts table. There will then be multiple queries written in order to take this information from the database, and use it to perform a mail merge.

This is what I have so far,

----------
Table: Courses
Course I.D - Primary Key
Description
Price
Tutor I.D - Foreign Key from Tutors Table
Tutor Name - Foreign Key from Tutors Table.
----------
Table: Tutors
Tutor I.D - Primary Key
Tutor Name
Tutor Address Line One
Tutor Town
Tutor Postcode
Specialism 1 - Foreign Key - Course I.D from Courses Table
Specialism 2 - Foreign Key - Course I.D from Courses Table
Date Employed
----------
Table: Clients
Client I.D - Primary Key
Client Name
Client Address Line One
Client Town
Client Postcode
Client Telephone
----------
Table: Resources
Item I.D - Primary Key
Item Name
Purchase Date
Cost
Supplier
Status
History
----------
Table: Bookings
Booking I.D - Primary Key
Client I.D - Foreign Key from Clients Table
Client Name - Foreign Key from Clients Table
Course I.D - Foreign Key from Courses Table
Course Name - Foreign Key from Courses Table
Course Cost - Foreign Key from Courses Table
Start Date
No of Attendees
Invoice I.D - Foreign Key from Invoices
----------
Table: Invoices
Invoice I.D - Primary Key
Booking I.D - Foreign Key from Booking Table
Client I.D - Foreign Key From Clients Table
Client Name - Foreign Key from Clients Table
Client Address Line One - Foreign Key from Clients Table
Client Town - Foreign Key from Clients Table
Client Postcode - Foreign Key from Clients Table
Client Telephone - Foreign Key from Clients Table
Course I.D - Foreign Key from Courses
Course Name - Foreign Key from Courses
Course Cost - Foreign Key from Courses
Invoice Total - Foreign Key from Bookings and Courses - No of Attendees X Course Cost
----------
Table: Statements
Statement I.D - Primary Key
Date
Client I.D - Foreign Key from Clients Table
Client Name - Foreign Key from Clients Table
Clients Address Line One - Foreign Key from Clients Table
Clients Town - Foreign Key from Clients Table
Clients Postcode - Foreign Key from Clients Table
Clients Telephone - Foreign Key from Clients Table
Invoice I.D - Foreign Key from Invoices Table
Invoice Date - Foreign Key from Invoices Table
Invoice Total - Foreign Key from Invoices Table
Amount Due - Foreign Key from Ledger Table - Invoice Total - Payments Received
----------
Table: Ledgers
Client I.D - Foreign Key from Clients Table as Primary Key
Client Name - Foreign Key from Clients Table
Client Address Line One - Foreign Key from Clients Table
Client Town - Foreign Key from Clients Table
Client Postcode - Foreign Key from Clients Table
Client Telephone Number - Foreign Key from Clients Table
Invoice I.D - Foreign Key from Invoices Table
Invoice Date - Foreign Key from Invoices Table
Invoice Total - Foreign Key from Invoices Table
Payment Due - Foreign Key from Bookings Table - Start Date + 3 Months
Payment Received
Outstanding Balance - Invoice Total - Payments Received
----------

Ideally I would like to have a booking form which updates the tables that are required, and I would like to have it display a calendar which can show the user which resources are available on which day, so they can determine when they want to book the course start date for.

I am sorry if this is hard to understand, if it is impossible I will try again, but I do appreciate any help that any of you can give me.

Thanks,

Eddie.