My God I'm Screwed REPOST

[Sky Warren]

Hello to all,

I followed instructions from MVP Sprinks and have set up the tables below
along with relationships. I'm still having trouble bringing data into the
form. The only thing I have working is the Employees part.

I have posted screen shots of portions of what I've done so far. The
Relationships photo contains all the split tables along with the (not sure if
I needed it) original unsplit table.

It can be viewed at this link if anyone cares to comment:

http://members.aol.com/skylow1/access.html



(The following is code provided by MVP Sprinks which I include here for
reference purposes.)


Departments
---------------
DeptID AutoNumber (PK)
Department Text

Employees
---------------
EmpID AutoNumber (PK)
LName Text
FName Text
DeptID Number (Foreign Key to Departments--use combo box)
BirthDate Date
....etc.

Courses
--------------
CourseID AutoNumber (PK)
Course Text

Since a many-to-many relationship exists between Employees and Courses (many
employees can take the same course, and an employee may take many courses),
you need an intermediate table to represent which employee has taken or needs
to take which courses. Does each employee of a particular department have to
take the same courses? If so, after loading the tables described above, you
could create a DeptCourses table that could insert EmployeeCourse records
based on the department.

EmployeeCourse
--------------------
EmpCourseID AutoNumber (PK)
EmpID Number (FK to Employees)
CourseID Number (FK to Courses)
Completed Yes/No
CompletionDate Date
Score Number
....etc.

DeptCourses
---------------
DeptCourseID AutoNumber (PK)
DeptID Number (FK to Departments)
CourseID Number (FK to Courses)

Then you could have a data entry form based on Employees, with a continuous
subform based on EmployeeCourses. When an employee completed the course,
you could check it off, and update the CompletionDate.

 

[Arvin Meyer]

Sprinks is not an MVP, nor did he claim to be one. IIRC, his answer was a
reasonable solution.

Check the last thread. Tom Wickerath is still waiting for your email.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Sky Warren]

Arvin,

OOOPS! Your absolutely right. Sprinks never claimed to be an MVP. His
solution though was so MVP-ish I just thought of him that way. Nevertheless,
I tried emailing Tom but there's somthing up with his email address. I will
post to him in the forums again and try getting a working email for him.

Thanks for the correction. Did not mean any disrespect to anyone.

-Sky

 

[Tony Toews]

OOOPS! Your absolutely right. Sprinks never claimed to be an MVP. His
solution though was so MVP-ish I just thought of him that way.

Thanks for the correction. Did not mean any disrespect to anyone.

<smile> I'm sure Sprinks is taking this as quite a compliment. As
he/she should.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Sky Warren]

Hi Tony,

Thanks for the reply, it set my mind at ease. For the past couple of days I
was feeling bad because I was thinking Sprinks was upset concerning my stated
mis-representation of status.

In reality, I had a choice of replies to follow re: my database. I chose to
follow the reply I got from Sprinks because it looked like the easiest path.
No disrespect to Allen Browne though, with whom I have the highest regard.
The two of them offered viable solutions for me and it was indeed a tough
choice.

So, following the model that Sprinks offered I find myself still with so
many questions. I've only been working with Access about four months in my
spare time and I never traveled the "Relational Database" path before.

Right now I'm trying to figure out why my data from the Courses table is not
showing up in the form. I think it's related to my query so I'm concentrating
in that area for the moment. The more I look at the database I realize how
much I still need to learn. Exasperating but Access is still fun for me

-Sky

 

[John Vinson]

It can be viewed at this link if anyone cares to comment:

http://members.aol.com/skylow1/access.html

Ok... you're not getting any results in your query because you're
joining employees to Courses, joining the EmployeeID to the CourseID.

This will give you those records for which the (arbitrary and
meaningless) numeric EmployeeID of the person is equal to the (equally
arbitrary and meaningless) numeric CourseID of the course. That is, if
you have an Employee 31 (Joe Schmoe) and a course number 31
(Basketweaving), you'll - just by coincidence - pull up a record with
the information about Joe, and the information about basketweaving.
NOT what you want, since Joe's signed up (in the JUNCTION table!!!)
for Relational Database Design, Basic and Advanced SQL, and Forms
Development, courses 83, 22, and 45.

Reread Sprink's excellent advice:

Then you could have a data entry form based on Employees, with a continuous
subform based on EmployeeCourses. When an employee completed the course,
you could check it off, and update the CompletionDate.

He said nothing about creating a query joining Employees to Courses.
Try what he did suggest - he's right on target, as he usually is.

John W. Vinson[MVP]