Trouble deciding tables w/ info

**FishVal** · Sep 25 '07, 03:51 PM

Hi, there.

Something like this. Fields implementing PK/FK relationship are bolded.

Table: tblEmployees
[code=text]
keyEmployeeID AutoNumber(Long ) PK
SSN
Last name
First name
Middle name
[/code]

Table: tblCourses

Code:

keyCourseID        AutoNumber(Long)           PK
[B]keyEmployeeID      Long    FK(tblEmployees)[/B]
TrainingType
CourseTitle
CourseDescription

**Kevin Wilcox** · Sep 25 '07, 04:07 PM

Deanndra

It looks to me like what you’re asking is how do you set up this data so that the tables are relational (and efficient?). I’m not an expert so it might be worth hanging on until you get an experts reply, but for what it’s worth these are my observations;

It seems like you’re duplicating the data from the 3rd table mentioned, storing this again in the first table? SSN, Name data… But you’re on the right track.

Instead, try this:

1. Complete the tblEmployees table (and a tip, use this kind of naming protocol from the outset, it’ll save you loads of hassle and confusion later).
2. If SSN is a unique field with no duplicates, make this the primary key. Otherwise, add in a new field called e.g. EmployeeID, set it’s property to autonumber, and make this the primary key.
3. For the remainder of the fields, I’d just suggest losing the gaps between words, i.e. change Last name to LastName etc. makes life easier later.
4. next, do the same kind of this for the courses table (tblCourses), create a table of unique courses with a field called CourseID, property = autonumber, make this the primary key
5. You could add in the course date fields into this table, but I wouldn’t, as you’ll end up storing the course name etc more times than you need to
6. I’d create a further table called e.g. tblCourseDates – this is effectively the first table you mention
7. Also give this one an autonumber, primary key field called e.g. CourseDateID
8. Then give it a field called CourseID (matching the tblCourses primary key), set the property to number. Whilst in this field in design mode, use the ‘lookup’ tab at the bootom left and change the display control to a combo. Make the rowsourcetype table/query. For the rowsource, use the build button just to the right to create a query of the tblCourses which includes the CourseID and CourseName fields. This makes it easier to understand your data in tblCourseDetail s
9. Also give it a field called EmployeeID, also as a number and do the same lookup thing.
10. Add in your course date fields, supervisor field etc. NB. If you have lots of different supervisors for different courses, consider creating a separate tblSupervisors, joining this to tblCourseDetail s along the same lines as described below.
11. Now, in the relationships window, connect the EmployeeID field from tblEmployees to the corresponding field in tblCourseDates; and do the same for tblCourses to tblCourseDates.
12. Now, populate the tblEmployees with just their bio-details
13. populate the tblCourses with the different course details
14. In edit view in tblCourseDetail s (or in a form) you can now select the course name, select the employee name, and add in the dates and other details, i.e. you avoid replicating the basic course details and employee details.
15.
16. This should give you a nice, sound, relational, normalised base and obviously you can tailor these principles to suit.

**Scott Price** · Sep 25 '07, 04:15 PM

Hello and welcome to the Scripts! (especially the Access forum :-)

The essential thing about a Relational Database is that you should only store one piece of information in one place. It then is 'accessed' according to the relationships that you create between your tables.

Obviously there are times when you have to store something in more than one place, but those situations are handled in such a way as to minimize the redundant data.

The first part of designing a database is deciding what it will be used for. Second is collecting all the information that will be stored and examining it for it's purpose and characteristics .

We'll look at your first table as an example. You are apparently storing student information? Assuming so, we have identified one subject of your database. Belonging to this subject are several characteristics : Name (first, last and middle), SSN, address, phone #, etc. In your table you have added a number of Course fields, but as you will see, these really are not part of the 'Student' subject! I see you have a Course table, which is excellent.

Now, let's analyze the relationship between the Student and Course subjects. One student can attend multiple classes, this tells us that a One to Many relationship exists between Students and Courses. Now, turning it around, can one class be associated with more than one student? Obviously yes... this tells us that a One to Many relationship also exists between Courses and Students, this results in what is called a Many to Many relationship between the Students table and the Courses table.

The best way to make this relationship work is by creating a third table that will break the many to many relationship down into two one to many relationships. Call it tblStudentCours es, and give it three fields: StudentCourseID , StudentID, and CourseID.

Now when you are in the database window, on the tool bar you will see a symbol looking something like the < sign. It symbolizes the relationship diagram. Click it, and it will bring up the window for creating/defining the relationships in your database. Right-click in the empty window and choose Show All tables. Arrange them by clicking and dragging as you wish, and then you can begin creating the relationships. First of all locate the Students table, then click and drag the StudentID (or SSN if you are using it as a primary key) from the Students table to the corresponding ID field in the StudentCourses table. This will bring up the dialog box, just click OK, and the relationship is created. Repeat with the Courses table, and you have now defined the relationship between Students and Courses.

There is a book that I have recommended many times and I will recommend it to you also! Database Design for Mere Mortals, Second Edition, by Mike Hernandez. I've seen it for around $35US on Amazon.com, and it is well worth the nearly $50US I payed for it a few years ago. It explains the process of designing a database in simple language that even I can understand :-)

Also, this tutorial on the Scripts provides some valuable information: Database Normalisation and Table Structures

A final note on Primary keys. They need to be unique, which means that each record in your table will be identified by one and only one number. This is why many (including myself) use the Access autonumber data type to create a primary key field for each table. This primary key field never gets changed and never should be seen by the user of the database, it is only for the database engine reference.

Any other questions, feel free to ask.

Regards,
Scott

**Scott Price** · Sep 25 '07, 04:16 PM

I see two other people are faster typers than I :-) Thanks guys for your input as well!

Regards,
Scott

**deanndra** · Sep 26 '07, 12:47 PM

Wow! I am so shocked, impressed, and grateful! I am going to try these suggestions right now. Thank you all so much for your help! Will let you know what I did, and believe me, I will definitely be checking back in here frequently because there is so much more user-friendly info available on here than on Microsoft's webpage! Thanks again!!!

**deanndra** · Sep 26 '07, 01:02 PM

Originally posted by Scott Price

Hello and welcome to the Scripts! (especially the Access forum :-)

The essential thing about a Relational Database is that you should only store one piece of information in one place. It then is 'accessed' according to the relationships that you create between your tables.

Obviously there are times when you have to store something in more than one place, but those situations are handled in such a way as to minimize the redundant data.

The first part of designing a database is deciding what it will be used for. Second is collecting all the information that will be stored and examining it for it's purpose and characteristics .

We'll look at your first table as an example. You are apparently storing student information? Assuming so, we have identified one subject of your database. Belonging to this subject are several characteristics : Name (first, last and middle), SSN, address, phone #, etc. In your table you have added a number of Course fields, but as you will see, these really are not part of the 'Student' subject! I see you have a Course table, which is excellent.

Now, let's analyze the relationship between the Student and Course subjects. One student can attend multiple classes, this tells us that a One to Many relationship exists between Students and Courses. Now, turning it around, can one class be associated with more than one student? Obviously yes... this tells us that a One to Many relationship also exists between Courses and Students, this results in what is called a Many to Many relationship between the Students table and the Courses table.

The best way to make this relationship work is by creating a third table that will break the many to many relationship down into two one to many relationships. Call it tblStudentCours es, and give it three fields: StudentCourseID , StudentID, and CourseID.

Now when you are in the database window, on the tool bar you will see a symbol looking something like the < sign. It symbolizes the relationship diagram. Click it, and it will bring up the window for creating/defining the relationships in your database. Right-click in the empty window and choose Show All tables. Arrange them by clicking and dragging as you wish, and then you can begin creating the relationships. First of all locate the Students table, then click and drag the StudentID (or SSN if you are using it as a primary key) from the Students table to the corresponding ID field in the StudentCourses table. This will bring up the dialog box, just click OK, and the relationship is created. Repeat with the Courses table, and you have now defined the relationship between Students and Courses.

There is a book that I have recommended many times and I will recommend it to you also! Database Design for Mere Mortals, Second Edition, by Mike Hernandez. I've seen it for around $35US on Amazon.com, and it is well worth the nearly $50US I payed for it a few years ago. It explains the process of designing a database in simple language that even I can understand :-)

Also, this tutorial on the Scripts provides some valuable information: Database Normalisation and Table Structures

A final note on Primary keys. They need to be unique, which means that each record in your table will be identified by one and only one number. This is why many (including myself) use the Access autonumber data type to create a primary key field for each table. This primary key field never gets changed and never should be seen by the user of the database, it is only for the database engine reference.

Any other questions, feel free to ask.

Regards,
Scott

Hmmm, Scott you definitely broke it down for me here! But I didn't know if it would be important to clarify that this is not for students. I work in the military and I am formatting a database to keep track of statewide training in a certain area. I don't need any info on the employees outside of their name and ssn. That is why the course dates/titles/descriptions are important. Like, let's say a particular class was held 2-3 Oct. Well actually a similar class would then also be held on 2 Oct. Perhaps I should put it into a diagram below of some type.

Class Date
1A (all attend) 1 Oct
1B (technician) 2 Oct
1C (supervisor) 2-3 Oct

However, the problem is because 2 different courses (1B & 1C) share a date (2 Oct) although 1C also includes 3 Oct. And this particular course (with the 2 classes) may not be held again until 15 Dec.

See? This is why I get confused with it. Again, mountain out of molehill I'm sure...Thanks!

**deanndra** · Sep 26 '07, 01:41 PM

Ok, this is what I've come up with combining the comments and ideas left here from you all. I hope this will get me where I want to go!!!

tblEmployees
EmployeeID autonumber PK
SSN
LastName
FirstName
MiddleName

tblCourses
CourseID autonumber PK
Type
Title
Description

tblDates
CourseDateID autonumber PK
CourseID FK (tblCourses)
EmployeeID FK (tblEmployees)
CourseDates

I'm still a bit stumped on the dates themselves however. But I think I'm definitely on the right track with the other items. Any suggestions on how to track the dates?

**Scott Price** · Sep 26 '07, 01:53 PM

Originally posted by deanndra

Ok, this is what I've come up with combining the comments and ideas left here from you all. I hope this will get me where I want to go!!!

tblEmployees
EmployeeID autonumber PK
SSN
LastName
FirstName
MiddleName

tblCourses
CourseID autonumber PK
Type
Title
Description

tblDates
CourseDateID autonumber PK
CourseID FK (tblCourses)
EmployeeID FK (tblEmployees)
CourseDates

I'm still a bit stumped on the dates themselves however. But I think I'm definitely on the right track with the other items. Any suggestions on how to track the dates?

First of all, I commend you on doing a great job with your design! I think you've come up with something quite workable.

As for the dates, why not use two date fields? CourseStartDate and CourseEndDate? This would allow for reasonably easy searching from a query, and would satisfy the requirement of not having multipart fields.

Regards,
Scott

**FishVal** · Sep 26 '07, 04:02 PM

Originally posted by deanndra

Ok, this is what I've come up with combining the comments and ideas left here from you all. I hope this will get me where I want to go!!!

tblEmployees
EmployeeID autonumber PK
SSN
LastName
FirstName
MiddleName

tblCourses
CourseID autonumber PK
Type
Title
Description

tblDates
CourseDateID autonumber PK
CourseID FK (tblCourses)
EmployeeID FK (tblEmployees)
CourseDates

I'm still a bit stumped on the dates themselves however. But I think I'm definitely on the right track with the other items. Any suggestions on how to track the dates?

Hi, deanndra.

Clarify please your intension to associate course date with particular employee making particular couse. Does this mean each employee makes a course individually?

**deanndra** · Sep 26 '07, 07:15 PM

Originally posted by FishVal

Hi, deanndra.

Clarify please your intension to associate course date with particular employee making particular couse. Does this mean each employee makes a course individually?

Well, for ONE of the courses, your statement above is true. Let's use Course 1A. It is an online course, and a pre-req to continuing the other courses in the "1" series. All other courses have pre-set dates split in such a way as I stated above, with the "1A, 1B, 1C..." that I put.

**mlcampeau** · Sep 26 '07, 07:54 PM

Originally posted by deanndra

Well, for ONE of the courses, your statement above is true. Let's use Course 1A. It is an online course, and a pre-req to continuing the other courses in the "1" series. All other courses have pre-set dates split in such a way as I stated above, with the "1A, 1B, 1C..." that I put.

To incorporate pre-req's you could add to your table:
tblCourses
CourseID autonumber PK
Type
Title
Description
Prerequisite
Requirement For

Where Prerequisite would have the course's prerequisite entered, and the Requirement For would be the class that this course is a requirement for.

And then, as Scott suggested, change the CourseDates to incorporate both a start and an end date. For those courses that are one day long, just enter the Start Date as the End Date as well.

tblDates
CourseDateID autonumber PK
CourseID FK (tblCourses)
EmployeeID FK (tblEmployees)
CourseStartDate
CourseEndDate

**deanndra** · Sep 26 '07, 07:57 PM

Ok, you guys have been soooooo wonderful with your help! Truly, I'm very appreciative. So let me know if I just need to shut up and color!

I guess the part that is giving me trouble now is not the building of the tables themselves. My problem is of importing what I already have into the tables. Perhaps an example could begin to show what I mean because I just went and made out an entire diagram (I'm a visual person).

[PHP]Employee Course Date
John 1A 01/01/01
John 1B 01/02/01
John 1C 01/03/01[/PHP]

But working with Excel, my spreadsheet that has been keeping track of this beforehand (and it has almost 2100 people on it) is currently set up similar to this:

[PHP]Employee Course 1A Course 1B Course 1C Course 1D
John 01/01/01 01/02/01 01/03/01
Joe 03/01/03 05/01/05 N/A 05/03/05[/PHP]

So the thing that is just making me CRAZY is how to build a table like the one created above [tblCourseDates] which will somehow still import the data I have in excel so I don't have to retype all of it.

And I didn't mention again that Course 1A above is online so it can be taken 365/24/7. The other courses have specific dates, but each course has a different date.

Clear as mud? Because it is to me!!! I come into work feeling really hopeful, and leave wanting to toss my computer out the window.

I needed to vent. Sorry. Again, thanks for all your help. Perhaps I should have posted all that earlier but this morning I thought I could make it work somehow. But I didn't...

**Scott Price** · Sep 26 '07, 09:52 PM

Venting is OK! We all feel the need of that from time to time :-) Defenestrating your computer is up to you :-) (though if it's a work computer, probably not a good idea).

To start with, let's take things one at a time. The time a course might be taken really is a side issue that doesn't bear on what you're dealing with. It doesn't matter if it's 365/24/7 or once every 10 years, the format of your tables won't change because of it's frequency.

As for importing from excel, that will be a multi-part operation! First of all, you'll import everything into a temporary table that has the same structure as the excel spreadsheet. Then we'll take each field and run it through to conform it to the new table structure.

Are there any other fields in the excel spdsht than the type that you mentioned? I see EmployeeID and CourseID's... Anything else besides those? I'll see what I can work out for the data conversion/import.

Regards,
Scott

**deanndra** · Sep 27 '07, 12:26 PM

Originally posted by Scott Price

Venting is OK! We all feel the need of that from time to time :-) Defenestrating your computer is up to you :-) (though if it's a work computer, probably not a good idea).

To start with, let's take things one at a time. The time a course might be taken really is a side issue that doesn't bear on what you're dealing with. It doesn't matter if it's 365/24/7 or once every 10 years, the format of your tables won't change because of it's frequency.

As for importing from excel, that will be a multi-part operation! First of all, you'll import everything into a temporary table that has the same structure as the excel spreadsheet. Then we'll take each field and run it through to conform it to the new table structure.

Are there any other fields in the excel spdsht than the type that you mentioned? I see EmployeeID and CourseID's... Anything else besides those? I'll see what I can work out for the data conversion/import.

Regards,
Scott

Ok, thanks so much. The first paragraph allowed me the chance to laugh and thus end up hopeful again. We'll see how I feel at the end of the day. So my computer is safe...for now. :)

Well, the fields currently in the excel spreadsheet are the following:

Employee
SSN (no dashes)
Date of course
Date completed
Course Title
Location
Supv [yes/no]
Course Code
Form 1556 [yes/no]
Signed Roster [yes/no]
Credit hrs [#]

Then of course as I was formatting this in the beginning, I had to be VERY anal-retentive and went further to break it down into tabs. So I have 2 different tab (worksheets) with the same fields, but broken down into whether it was general course for all, tech course, and then supv course.

I have had to re-format temp excel sheets already to import them into Access. And I've continued to try and figure out a way to do it with the dates as well. Hmm, I feel the frustration setting back in, and it's only 8:30. :) But I'm SURE this is going to work out today. The odd thing is I find Access so challenging that it's eerily fun!

Trouble deciding tables w/ info

Trouble deciding tables w/ info

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment