Training Records DB

[erycka00]

Hello All,
I have searched through google, this forum and many others and haven't found my answer just yet. I think I am close, but missing the loop somewhere. What I have gathered so far is:

tblEmployees
StaffID (Autonumber)PK
Lname (Text)
fname (Text)
Agency (Text)
Unit (Text)
Section(Text)

tblTrainingCourses
CourseID (AutoNumber) PK
TrainingDesc Text

tblCompletedTraining
StaffID (AutoNumer)


CourseID Numeric
DateCompleted (Date Date/Time)

So in my relationship window, I currently have


tblEmployees
<-StaffID (Autonumber)PK
|
|tblCompletedTraining
|->StaffID (AutoNumer)


tblTrainingCourses
<-CourseID (AutoNumber) PK
|
|tblCompletedTraining
|->CourseID Numeric

So my problem is when I am trying to enter names under the training course (titles), I only have the option to enter the DateCompleted. The StaffID is automatically entered as a new entry is made and does not match up with the StaffID on the Employee Table. Should that StaffID in the CompletedTraining Table not be an autonumber and just a regular number instead?

How do I tie it so that rather than an ID # shows up, the actual F&L name of the person who took it shows up? I hope I am describing my issue well enough. Any help would be so wonderful!!

[RayMilhon]

Right off I see one problem. Since StaffID is linked between the employees table and the Completed Training Table Only one (Employees table should be an autonumber. the Completed training Table should just be a number.

[erycka00]

RayMilhon,
Thank you for taking the time to review my post and responding. So the StaffID link, do I need it somewhere else as well? Or better question from me...how do I fix it? What I would like to do with the attachment on post, is enter the last name, first name, and then date the person took it. I'd like to have L&F name separate fields.

Right now, I have no where to enter the person's name for the training course they took.

Erycka00

[erycka00]

Here is how my tables and relationships are set up as of now.

[RayMilhon]

First I recommend you modify your data structure slightly.

StaffRoster
StaffId Autonumber PrimaryKey
Lastname String
Firstname String
--------------------
Agency String
Section String
Unit String
Grade String
---------------------

The items between the ----'s are things you may want to look at. As long as each Person can only have 1 value for each of those your fine. However if any single person could have multple values in any of those fields you may want to break the table up a bit more. Read up on Normalization if this is possible.

tblTrainingCourses
CourseID Autonumber PrimaryKey
CourseDesc String


CompletedTraining
StaffID Number Foreign Key to Staff Roster
CourseID Number Foreign Key to tblTrainingCourses
Datecompleted Date

[erycka00]

First I recommend you modify your data structure slightly.

StaffRoster
StaffId Autonumber PrimaryKey
Lastname String
Firstname String
--------------------
Agency String
Section String
Unit String
Grade String
---------------------

The items between the ----'s are things you may want to look at. As long as each Person can only have 1 value for each of those your fine. However if any single person could have multple values in any of those fields you may want to break the table up a bit more. Read up on Normalization if this is possible.

tblTrainingCourses
CourseID Autonumber PrimaryKey
CourseDesc String


CompletedTraining
StaffID Number Foreign Key to Staff Roster
CourseID Number Foreign Key to tblTrainingCourses
Datecompleted Date
-------------------------------------------
Ray,
I have attached a new relationship screenshot. The StaffRoster, a staff can only belong to one agency, section, unit, and grade. They will have multiple training courses that they have taken.

With the way I set up the tables/relationship now, when I go to the tblTrainingCourses to enter information, I only have the option to put in the enter the staffID and dateCompleted. Is there a way so that I can put in the First and Last name of the staff instead?

[RayMilhon]

Not exactly sure what I'm looking at. Your tblTrainingCourses should only have 2 Data elements the first is an Autonumber to identify the courses requires no data entry as this is done by Access automatically and the 2nd is a description of the Course. the Staff Roster as well should be simple data entry with the staffid created automatically again autonumber and the remaining fields just typed in. The Completed Training Table requires you to lookup the staffid and courseid from those 2 tables and only enter the Date completed.

If it was me I would create 3 Data Entry forms 1 for each table. The Staff Roster and tblTrainingCourses would use their respective tables as data sources Hiding the StaffID and CourseID Elements. These 2 fields do not require data entry and would typically be under a maintenance area.

the CompletedTraining form would have 2 combo boxes 1 for the staffid and 1 for the course. staff cbo would have 3 Columns 1 for the Staffid, 1 for the firstname and 1 for the lastname staffid would be the bound column and size would be 0. Course cbo would have 2 again the ID column would be the bound column and size would be 0

[erycka00]

Hahaha now you've gone above my level of understanding lol. Well it's lunch time and perhaps that is whats keeping me from comprehending anything you mentioned :-). I will take a shot at it after.

Thanks for your help!!

[RayMilhon]

Take a look at the attached DB. Just a real quick sample.

[RayMilhon]

Oops didn't notice that the upload didn't work. size was too big. Here it is I hope.

[erycka00]

Thank you for the sample...there was no relationship set up? I don't see how they are tied together. So for example, Jane Doe (StaffID:1) took Access Training (TrainingID:3) on 12/15/11. None of the forms allowed me to either select a training and put the staff that took that class or enter the training for a particular staff. So my thing is, I have tons and tons of rosters from the end of training. I would like to go to my table, choose Access Training and be able to enter 40 staff (off the roster) names and date of when they took it. Does that make any sense?

[RayMilhon]

Look at the form for completed Courses. There are 2 combo boxes and 1 text box the first combo box should show the names from the staff roster table. The second will show the courses from the tblTrainingCourses. The text box will allow you to enter a date when it was completed. In the sample I sent both combo boxes will show as empty as I did not insert any data whatsoever. You need to open each of the staff and course forms and put in a few records of sample data to see how they work.