Creating A Database for employee's and there courses

C

carrickr

I am trying to create a database for our company. It would be for keeping
track of any courses that our employees have been too.

Table one
PersonnelID
LName
FName
OfficeID
JobTitle

Table two
AgencyID
CourseTitle
Location
Dates Attended
Instructor
Hours completed

My problem is trying to get the two tables to share data. I know from a
couple of college courses from years ago that I need relationships between
the two but getting that is another matter.

I also need be able to type in an employee's last name and have that data
auto fill into a form. before updating any course data.

Any suggestions very welcome.
 
P

PC Datasheet

Change your tables to this:
TblPersonnel
PersonnelID
LName
FName
OfficeID
JobTitle

TblAgency
AgencyID
AgencyName

TblCourse
CourseID
AgencyID
CourseTitle

TblInstructor
InstructorID
FirstName
LastName

TblLocation
LocationID
Location

TblCourseOffered
CourseOfferedID
CourseID
InstructorID
LocationID
StartDate
EndDate
HoursCompleted

TblCourseAttendance
CourseOfferedID
PersonnelID

I am in the final stages of creating an application just like this that runs
on an intranet. The employees and managers interect with the database
through Internet Explorer. If you are interested in an application like
this, contact me at my email address below.
 
C

carrickr

Thanks for the tip! Any more about getting the form to auto fill if say the
last name of an employee is typed in?
 
J

John Vinson

Thanks for the tip! Any more about getting the form to auto fill if say the
last name of an employee is typed in?

Use the Form Toolbox combo box wizard to create a Combo Box to find
the employee's record when you select their name from the combo.

John W. Vinson[MVP]
 
P

PC Datasheet

There is actually no place you would ever do that. Once you enter a new
employee in TblPersonnel, forever after that employee is represented by
PersonellID which is a long integer. You might have occasion to search for
an emplyee record in which case you could use a combobox based on
TblPersonell that displays the employee name but actually has the value of
PersonellID. You might look at the autoexpand property of a combobox where
you begin to type in an employee's name and the list scrolls to the names
beginning with what you have entered with each character you enter. If you
were entering an attendance record in TblCourseAttendance you might use a
form/subform where the main form is basd on TblPersonel and the subform is
based on a query based on TblCourseAttendance and other tables. In this
case, the main form's purpose is to navigate to the employee record (all
employee data has already been entered previously) and the actual data entry
is in the subform to enter course attendance data. You would navugate to the
employee record you want with the navigation buttons at the bottom left of
the screen. BTW, you can highlight the number you see there in the box, and
type in a number. The form will jump to the record represented by that
number, Sometimes that beats sitting and scrolling through the numbers one
at a time.
 
C

carrickr

I have a question for PC Datasheet, I made another database with the tables
that you suggested. After they were created I looked at them and tried
create all of the relationship based on all of the keys that I created for
each table. My problem is that when I go to test the database I can add
certain Item cause it keeps give me the error about not being about to enter
data because another table needs data to be added. To say the least I'm kind
of frustrated that I can't seem to find alot of info on Access. I search the
net looking for any answer and can't seem to find any. Oh I can find general
answers, but nothing really indepth. Is there any place that you could
suggest that I can go to? Or for that matter can anyone answer that question
for that matter.
 
P

PC Datasheet

I just looked at the Help file. Go to Primary Keys and the subtopic
Relationships between tables. Read all that is there to see if that helps
you. Also look in the Help file for subforms and how they work.

It sounds like you had a data entry form that was a form/subform and you
tried to enter data in the subform before you created a record in the main
form.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top