Linking Multiple Tables To A Single Table

[Wobert]

Ey up all,

I am developing a database that will hopefully track students enrolled onto various IT courses. Each student who is enrolled onto an IT course is require to have a review once a month. The review consists of different questions depending on what course they are enrolled onto and tracking of what modules they have completed etc. This means that every course is different! This is where my problem lays!

On my database I have various tables.

1. Student ID & Contact Details
2. Course Details (Course code, length of course, enrolment date etc).
3. Course tracking (different tables for different course i.e. MOUS Tracking Table, ECDL Tracking Table, CLAIT + Tracking Table etc. 7 Tracking Tables in all!)
4. End of course review (Again depending on the course different tables)

What I want to do (and so far have been unable to do) is link the various tracking tables to the course details table.

i.e. if in table [Course Details] there was a field named course, and I wrote in MOUS, this record would then relate to the MOUS Tracking Table. And then if I entered another learner’s details apart from this learner was enrolled onto the course ECDL, it would automatically relate itself to the ECDL Tracking table. etc.

Is this possible? Or is there easier ways to do this? Or best of all, how do I do this?

Thanks a lot, even if all you do is read this! Please help if you can!

Regards,

Robert Canavan

 

[JeremyNYC]

Robert,

A couple of broad points, and then some detail.
1) I think your table scheme needs to be reworked.
2) You should be setting up your database so that all data entry and modification happens through forms, not tables.

On the first point: I would suggest you do a bit of reading about "normalization". I've got a copy of a great article on my website "Fundamentals of Relational Database Design". It's written by a guru of the subject, and will help a lot.

Basically, you don't want to have one table per course. I assume these tables have more or less identical fields. You could just put all of this data into one table, with an additional field to tell you which course this data relates to.

If you post back with the fields in each of your tables, you'll get some more feedback about what you can do to your tables specifically to get them more normalized. But I woudl frist read that article and then make an attempt at changing things yourself.

Next point: use forms to edit data. The way a relational database works is to store data in multiple tables, which can be related to each other (though "relational" comes from a weird name for a table, but that's neither here nor there). By putting data in multiple tables you run into problems, like the one you're struggling with, of ensuring that records in one table are related to the correct recordds in another table, or multiple tables. The way this is dealt with is through forms that draw data from multiple table queries, and through forms that employe linked subforms. All of this gets taken care of for you automatically, in most circumstances.

First, get your tables in order, as otherwise it's like building a house when you know you're going to have to go back and alter the foundation--it might be possible to do the alterations without tearing down the house, but it's going to be a pain in some body part to do it. Then, read up on subforms. They will do what you need.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[PJoy]

Your post, Robert, is very good timing for me -- I have the very same problem. I completely understand the concept of normalization and I'm trying to do just that. However, I have one table that, like yours, needs to be linked to multiple tables depending on the value of a particular field.

I have a "Sales" table with several fields, one of which is "SaleType" - currently there are 2 choices, "DirectSale" and "Auction." I keep very, very different detail information for each of these sale types. For that reason, it makes no sense for me to keep auction details in the same table as direct sale details - or does it? If I do keep both in the same table, every record will have many unused fields. I would rather keep separate "Auction" and "DirectSale" tables, each with a field that would be a foreign key to the primary key of the "SaleType" table. Depending on the value in the PK of the main table, I would like to be able to see a different subdatasheet.

Oh, about using forms, yes, I plan to use forms for data entry. However, I want to be sure I lay everything out in theh proper tables before I get that far.

Can someone out there help us both? Thanks all! Joy