DB Design Hlp!

[dee]

Hi there,

I'm trying to keep a db of clients who take courses and the employees they
send for those courses:

TblClients
ClientID (autonumber PK)
ClientName
ClientAddress
etc.

TblCourseNames
CourseNameID (autonumber PK)
CourseName

TblCourseLevels
CourseLevelID (autonumber PK)
CourseLevel

TblCourseLanguages
CourseLanguageID (autonumber PK)
Language


TblCoursesAttended
CourseAttendedID (autonumber PK)
CourseName (number, FK from tblCourseNames)
CourseLevel (number, FK from tblCourseLevels)
CourseLanguage (number, FK from tblCourseLanguages)
etc.

TblAttendees
AttendeeID (autonumber PK)
ClientID (number, FK to tblClients)
CourseAttended (number FK, from tblCoursesAttended)
FirstName
Lastname
etc.


I will want to input client names, their employees and the courses they take.

There will *never* be a mix of clients. Each client will send 1 - 10
employees per class, all taking the same course at the same time.

Clients may send groups of employees to various courses throughout the year.

Question 1:
Is my structure sound?

Question 2:
How do I create a form that will allow me to view the client name, then add
the main course information, such as course name, level, language beneath it,
then as a sub or linked form, enter or view the employees who are linked to
that client?

Would this be with one query or more?

I would really appreciate any help.

Thank you.

 

[Gina Whipp]

My two cents worth...

tblClients
ClientID (autonumber PK)
ClientName
ClientAddress
etc.

tblCourseNames
CourseNameID (autonumber PK)
CourseName
***cnCourseLevelID - FK
***cnCourseLanguageID - FK
***cnCourseDescription

tblCourseLevels
CourseLevelID (autonumber PK)
CourseLevel

tblCourseLanguages
CourseLanguageID (autonumber PK)
Language


tblCoursesAttended
caAttendeeID - FK
CourseAttendedID (autonumber PK) - Not sure what you plan to use this for?
***caCourseNameID (number, FK from tblCourseNames)
***CourseLevel (number, FK from tblCourseLevels) - If attached to CourseName
then no need
***CourseLanguage (number, FK from tblCourseLanguages) - If attached to
CourseName then no need
etc.

tblAttendees
AttendeeID (autonumber PK)
ClientID (number, FK to tblClients)
***CourseAttended (number FK, from tblCoursesAttended) REMOVE already in
tblCourseAttended
FirstName
Lastname
etc.


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

 

[Dee]

Hi there,

Thanks so much for your response. I will be testing it out right away.
Please excuse my ignorance, but what is the significance of the ca and cn
acronyms?

Thank you.

My two cents worth...

tblClients
ClientID (autonumber PK)
ClientName
ClientAddress
etc.

tblCourseNames
CourseNameID (autonumber PK)
CourseName
***cnCourseLevelID - FK
***cnCourseLanguageID - FK
***cnCourseDescription

tblCourseLevels
CourseLevelID (autonumber PK)
CourseLevel

tblCourseLanguages
CourseLanguageID (autonumber PK)
Language


tblCoursesAttended
caAttendeeID - FK
CourseAttendedID (autonumber PK) - Not sure what you plan to use this for?
***caCourseNameID (number, FK from tblCourseNames)
***CourseLevel (number, FK from tblCourseLevels) - If attached to CourseName
then no need
***CourseLanguage (number, FK from tblCourseLanguages) - If attached to
CourseName then no need
etc.

tblAttendees
AttendeeID (autonumber PK)
ClientID (number, FK to tblClients)
***CourseAttended (number FK, from tblCoursesAttended) REMOVE already in
tblCourseAttended
FirstName
Lastname
etc.


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

 

[Dee]

Please disregard my blonde moment of the previous reply regarding cn and ca!

It's been a long day.

My two cents worth...

tblClients
ClientID (autonumber PK)
ClientName
ClientAddress
etc.

tblCourseNames
CourseNameID (autonumber PK)
CourseName
***cnCourseLevelID - FK
***cnCourseLanguageID - FK
***cnCourseDescription

tblCourseLevels
CourseLevelID (autonumber PK)
CourseLevel

tblCourseLanguages
CourseLanguageID (autonumber PK)
Language


tblCoursesAttended
caAttendeeID - FK
CourseAttendedID (autonumber PK) - Not sure what you plan to use this for?
***caCourseNameID (number, FK from tblCourseNames)
***CourseLevel (number, FK from tblCourseLevels) - If attached to CourseName
then no need
***CourseLanguage (number, FK from tblCourseLanguages) - If attached to
CourseName then no need
etc.

tblAttendees
AttendeeID (autonumber PK)
ClientID (number, FK to tblClients)
***CourseAttended (number FK, from tblCoursesAttended) REMOVE already in
tblCourseAttended
FirstName
Lastname
etc.


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

 

[Gina Whipp]

It's okay... it is my 'style' of identifying what table I have placed what
fields in. I don't expect everybody to get it!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

Please disregard my blonde moment of the previous reply regarding cn and
ca!

It's been a long day.

 

[Dee]

Thanks!

OK, I've reworked my tables. I'm now creating a form that will allow me to
add/modify/view as follows:

Client Name main form
CourseName and details subform
Attendee names subform

In essence, I'd like to be able to see the client name, with a list of
courses they've taken and then be able to open a linked form (or view a sub
form) to see which attendees attended that particular course.

I can't quite seem to be able to do this the way I've got things set up
right now, although it is late and perhaps I'm just not seeing things clearly.

Any thoughts?

Thanks again.

 

[Gina Whipp]

Dee,

I would have

frmClientName (tblClients)
sfrCourses (tblAttendees) OR (qryCoursesAttended - tblAttendees,
tblClients, tblCourseNames)

Link the two frm/sfr together by ClientID

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

 

[Dee]

Hi Gina,

Thanks again. I've tried this and have no problem getting either Client
with subform Course name, language, etc. Or if I want Client, then a list of
the attendees who are employees of that client and when I click them, see the
courses they have attended.

However, as soon as I try to create a sub-sub form that shows the attendee
names from a particular client that attended a particular course I run into
a road block. In other words, client name at top of form, course name,
language, etc. beneath (within) that, then the attendee names for a
particular course linked (within) that.

Thanks for your help.

 

[Gina Whipp]

Dee,

The main form should be clients only. The subform should have everything
else. The two should be linked otgether by ClientID. When you look up a
Client from the main form you should see a list of Attendees and Course they
took. Set up the query to get what you want then create the subform from
the query you just made.

I will look at your reply as soon as I get back tomorrow (it's late here and
I'm getting a bit tired myself)... I have to go see a Client tomorrow AM.
OR someone might jump in before I get back you. EIther way not forgetting
you!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

 

[Dee]

Thanks Gina.

I will also look at this with fresh eyes tomorrow.

I think I'm looking for have the client link to the course taken and then
the course taken link to all of the employees that took that particular
course.

Just in case I wasn't clear: One client may take many courses. Each course
may have many attendees.

Thanks for your help.

 

[Dee]

Hi again,

Think I figured it out:
One to many relationship from TblClients to TblCoursesAttended
One to many relationship from TblClients TblAttendees

Created a junction table called TblCourseDetails that is one to many from
TblCoursesAttended and also one to many from TblAttendees.

I can now create a form based on TblClients (main), TblCoursesAttended (sub)
and TblCourseDetails (sub sub) that seems to do the trick.

I'll take a second look tomorrow, but I think it's solved my question.

Thanks!

 

[Gina Whipp]

Got tied up longer at the Client then anticipated...

Just ready your reply and I think you missed my last reply detailing how to
lay out the main form - subform. Please re-review, it is actually easier
then what you are suggesting.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

 

[Dee]

Hi Gina,

I tried your suggestion, but I can't get it to display the way I want it to.
I must be missing something.

I basically need a main form and 2 subforms. The main form will contain the
client information, but sub form will contain the courses they attended,
including course name, date, etc. Then, the sub-sub form will display the
attendees of each course.

Does your method achieve this? If it does, then I am missing something.

Thanks so much!

 

[Gina Whipp]

My method will include 1 main form for client information and look-up and
one subform for all the other information. You will not need two subforms.
Did you make the query for the subform?

Once again, I have a meeting with a Client onsite. HOPEFULLY, I will be
back in about two hours but I never know. If you like you can send me your
empty database and I will set up the queries for you OR you can type the
exact names of the fields and the tables and I will write the SQL for you to
paste into a query.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

 

[Gina Whipp]

Dee,

I have realigned your tables... You were close but you got off course,
refer back to your earlier posting. I got the form set-up for you. I THINK
that's the way you wanted to see it and what I was trying to explain. If
you run into more isues post back, there is always someone willing to help!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

 

[Dee]

Hi Gina,

Thanks so much for your help and your time. I very much appreciate it.

I took a look at your redesign, with the form shwoing the client and listed
underneath, in the subform, the listing of employees and their courses
attended.

However, if you look at what I sent you, you'll see that I need to see the
client (as you have it), then a listing of the courses that client has taken
(with the option of adding additional courses).

I then need to be able to "expand" each course in order to see which
employees attended a particular course. (I had this as a linked form.)

Would this be possible with the new design?

I really appreciate your help and your time. Thanks so much!

 

[Gina Whipp]

Answers Inline...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

Hi Gina,

Thanks so much for your help and your time. I very much appreciate it.

I took a look at your redesign, with the form shwoing the client and
listed
underneath, in the subform, the listing of employees and their courses
attended.

However, if you look at what I sent you, you'll see that I need to see the
client (as you have it), then a listing of the courses that client has
taken
(with the option of adding additional courses).

Not sure I understand... If you open the frmCoursesAttended in the subform
you will see the Employees from that Client and what classes they attended.
You might want to create another form and place frmCoursesAttended on it.
On the new form just have a Client lookup!

I then need to be able to "expand" each course in order to see which
employees attended a particular course. (I had this as a linked form.)

Answered above

Would this be possible with the new design?

I really appreciate your help and your time. Thanks so much!

Your welcome...

 

[Gina Whipp]

Try the one I just sent.... Open frmClients and select a Client

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm