I have a basic DB that has the sole purpose of creating two special reports that are basically Course Graduation cards. Therefore, I only have a small number of fields. The problem is that when I try to normalize it, I get all kinds of errors in my forms telling me it can't update certain fields. Therefore, I was hoping someone would help me out.

The things I need stored are
Lname - Last Name of Student
Initials - First and middle
Cdate - Date of course
Instructor - usually one of three people, but can change
Cname - the name of the course
crt_num - the number of the certificate

I have a rudimentary DB that has all these things in one table, with a constantly changing query to generate the certificates. I want to create a more user friendly interface, but this setup is naturally useless. Any ideas on the Proper table layout?

Something like this is a starting point...

tblStudents
- StudentID [PK] (A#)
- FName
- LName
- MI
- Other info related to student...

tblInstrauctors
- InstructorID [PK] (A#)
- I_FName
- I_LName
- Other info related to instructor...

tblCourses
- CourseID [PK] (A#)
- CourseDescription
- CourseDate <-- if one course can have many dates... you will need to create another table for that...
- Other info related to course

tblCompletion
- RecordID [PK] (A#)
- CourseID [FK] <- to tblCourses
- InstructorID [FK] <- to tblInstractors
- StudentID [FK] <- to tblStudents
- CertificateNumber <-- need more info about this value... how do you get it?

Makes sense?

Any given course can be offered mutiple days. Right now, two courses are offered, and they are given almost every week.

Certificate number is a combination of the year and the order of which the name is entered into a db. Ex. 07-001.. It would be a composite of Record ID and the last two digits of the year.


What you gave me is much more detailed then it needs to be, but it will probably be integrated into the larger (and currently broke) Administration Section DB. Thanks for all of this, I appreciate the help.

Doug's structure is correct. Just one point to add. You don't need to store initial in a field. you can get a person's initials by using the Left() function.

Actually what Doug gave you is the minimum detail. Anything less granular runs into normalization issues.

Similar to the initials issue you don't store the Certificate number either. That's concatenated like so:

format([datefield],"yy") & "-" & Format([RecordNo,"000")



Edited by: ScottGem on Mon Jan 29 13:29:05 EST 2007.

Hi Scott,

Good point....I thought the OP meant Middle Initial, not the person's initials...

What I gave you is a properly normalized structure (NF3)...

You actually need another table that will link the courses and the dates there are taught in a M:M relationship...

If the certificate number is calculated, there is no need to store the value in the table.

Thanks Scott, Doug

Due to the way information moves around here, I'm not actually given full names. I'm only provided with Last name, and the first and middle initials of the person. I'm not in direct contact with the people who set up and administer these courses, so i'll probably change Fname in the example to Initial, simply because of this fact.

That does make sense