Hi Mike,
I'm using 'ID' as a sort of suffix to indicate Primary Keys, incidentally
That's fine, but it looks like you are using the same suffix in
tblCoursesRun for four other fields as well: CourseID, TrainerID, AssessorID,
and TraineeID. Most likely, these are foreign key fields:
CourseID relates to CourseID (PK) in tblCourses
TrainerID relates to ??
AssessorID relates to ??
TraineeID relates to CandidateID in tblCandidateDetails (I think, based on
your description)
It looks like you slipped CourseTitle into the tblCoursesRun table, but this
field is already present in tblCourses. That represents storing the same data
in more than one table, which is generally a "no-no", unless you need to
maintain a historical record. You should probably delete this field from your
junction table: tblCoursesRun.
Examples of maintaining historical records include recording the ship to
address in an Orders table, even though the same address is present in a
customers table. If the customer later moves, the information your database
produces will still be accurate, because you'll be able to tell exactly which
address a given order was shipped to. Another example is seen in the
Northwind sample database: UnitPrice is stored in the Products table and in
the Order Details table. This allows Northwind Traders to later change
prices, without affecting calculated sales for orders that have already been
filled.
I suggest renaming TrainerID to fkInstructorID. To me, TrainerID and
TraineeID just sound too similar, and it would be easy to get them mixed up.
Of course, fkInstructorID needs to be related to pkInstructorID in
tblInstructors, but I haven't seen any mention of such a table yet. I'm not
sure what AssessorID refers to; this was the first time you mentioned this
field.
Oh - I should mention I want be able to extract the following data,....
Everything you listed should be very easy with the proper design that you
are close to having now!
By the way, there is a separate Tables Design newsgroup that you might want
to post design related questions to. There's also separate groups for
queries, forms, reports, etc.
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Hi Tom.
Again, many thanks. Your reply came through just as I was finishing a more
detailed version of the question (based on your previous help). What you've
said here looks like it will be very helpful - I'm going to try it now (I'm
using 'ID' as a sort of suffix to indicate Primary Keys, incidentally). In
the interim, though, I've appended the more detailed question below - do you
think that what you have suggested will fit this scenario - I'm thinking of
the data I'm trying to extract.
Here's the question:
Many thanks to Joseph Meehan and Tom Wickerath for getting me this far - but
I need a little more help. Thanks to Tom, I now have a normalised table
called 'tblCoursesRun' with fields, CoursesRunID (PK field); CourseID;
CourseTitle; CourseStart; CourseEnd; TrainerID; AssessorID; TraineeID. A
table called 'tblCourses' with fields, CourseID (PK); CourseTitle. A third
table called tblCandidateDetails with fields, CandidateID (PK); Surname;
FirstInitial; DoB; NationalInsuranceNumber.
I have managed to set up a query,
called qryCoursesRun, which combines CoursesRun with Courses, but can't find
a way to combine CandidateDetails with it. I think I need a 'many-to-many'
relationship because I have many candidates doing many courses, and many
courses each with many candidates. Can anyone help with this - I'm a beginner
and just cannot work it out?
Oh - I should mention I want be able to extract the following data, 'All
Courses completed by This Trainee', 'All Trainees on this Course', and 'All
Trainees doing this Training' (ie all trainees who have completed this same
course at different times). Many thanks in anticipation.
I realise I'm taking up a lot of your time - I do appreciate it.
Regards,
Mike
__________________________________________
:
Hello -
I've now got separate, normalised tables for Trainees (TraineeID; Surname; FirstInitial;
DoB; NationalInsuranceNo) for Courses (CourseID; CourseTitle), and for CoursesRun
(CourseRunID; CourseNo (taken from CourseID); StartDate; EndDate). What do I do
next? (I want to be able to say that certain trainees took certain courses - some trainees
will have taken several courses, all courses are taken by several trainees).
Have you set the TraineeID, CourseID and CourseRunID as primary keys in
their respective tables? Also, what data type(s) did you use for these three
fields? The CoursesRun table can serve as the join (aka. junction, linking,
or intersection) table. You need to add TraineeID as a foreign key field to
the CoursesRun table. I recommend naming it fkTraineeID (the "fk" reinforces
the idea that this is a foreign key). Likewise, I usually always prefix my
primary key fields with a lowercase "pk", as in pkTraineeID, but that's just
me. Each person has their own style.
When you add the TraineeID (or fkTraineeID) field to the CoursesRun (or
tblCoursesRun) table, it must be the same data type as the corresponding
TraineeID (or pkTraineeID) field in the Trainees (or tblTrainees) table. If
you used an autonumber primary key for TraineeID in the Trainees table, then
you must select Number, with the default Long Integer field size, as the data
type for the foreign key. The reason is that an autonumber field is a long
integer data type.
You then create two one-to-many (1:M) relationships, as follows:
(1) pkTraineeID ---> (many) fkTraineeID and
(1) pkCourseID ---> (many) fkCourseID
Thus, your CoursesRun table should have the many side of two 1:M
relationships. You have now defined what is known as a many-to-many (M:N)
relationship between Trainees and Courses. M:N relationships are modeled by
using two 1:M relationships with a third table.
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Tom
I've read, absorbed and inwardly digested the info from the links you sent
me - so can now see the errors in my previous reply (please ignore it)! I've
now got separate, normalised tables for Trainees (TraineeID; Surname;
FirstInitial; DoB; NationalInsuranceNo) for Courses (CourseID; CourseTitle),
and for CoursesRun (CourseRunID; CourseNo (taken from CourseID); StartDate;
EndDate). What do I do next? (I want to be able to say that certain trainees
took certain courses - some trainees will have taken several courses, all
courses are taken by several trainees). Hope you don't mind helping. Thanks
in anticipation.
__________________________________________
:
As Joesph indicated, you should spend some time gaining an understanding of
database design and normalization before attempting to build something in
Access (or any RDBMS software for that matter). Here are some links to get
you started. Don't underestimate the importance of gaining a good
understanding of database design. Brew a good pot of tea or coffee and enjoy
reading!
http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf
http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")
http://support.microsoft.com/?id=234208
http://support.microsoft.com/?id=289533
Also recommended: Find the copy of Northwind.mdb that is probably already
installed on your hard drive. Study the relationships between the various
tables (Tools > Relationships...)
Here is a link to Access MVP Jeff Conrad's site for database design papers.
You'll find tons of links of Jeff's site:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
Also, since you are new to Access, I encourage you to start your experience
by using good naming conventions and avoiding the use of reserved words:
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763
Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp
Using a Naming Convention
http://msdn.microsoft.com/library/d...us/odeopg/html/deconusingnamingconvention.asp
Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335
List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Thanks Joseph - I was hoping no-one would say this - it's taken me days to
get to where I am and I don't relish starting all over again. However, you're
absolutely right, I need to get the basic structure right - oh well, back to
the drawing board.
