Trouble linking records

[kruseanderson]

I am trying to design a database to track CPR training. I have access 2010. I am new to access but can usually Google what I need and figure it out. This one has been eluding me for a week.

I have the tables and such designed. The problem is displaying the data. One table is for the Instructor data and one has the class Roster data. In the Roster there is always a lead instructor and sometimes an assisting instructor. I can create a form or show on the Instructor table every time an instructor is the lead. That is easy.

How can I show on the same table or subform when that instructor is an assisting instructor on another roster? If I try to add more items to the child field or create a relationship to more than one thing the result is blank. I think because it thinks I am trying to filter it. What I am trying to get is one datasheet view that show all the times an instructor taught. Whether it is as a lead or assisting.

Instructor table has InstructorID
Roster table has LeadInstructor, AssistingInstructor (1,2,3,4)
I have a relationship between InstructorID and LeadInstructor. (If I add a relationship between InstructorID and any AssistingInstructor my subform or table is blank)
InstructorForm has a Roster subform which shows the classes when an instructor is the lead, but not when they are assisting.
The only way I have figured out to show this is by having 5 subforms on the InstructorForm. One for each of the fields from the Roster table with the corresponding data.

I want to give credit in one easy to see view.



Make sense?!?!?
Thank you
Major

[HMEpartsmanager]

tblInstructors
----------------
InstructorId
FirstName
LastName

tblRoster
---------------
RosterId
InstructedClassName
LeadInstructor
AssistingInstructor

Assuming ALL instructors are in Instructor table, and they are either going to be the Lead or the Assistant, I think the following would be needed.

tblInstructors.InstructorId to tblRoster.LeadInstructor
tblInstructors.InstructorId to tbleRoster.AssistingInstructor

An instructor could be the lead or the assistant for any class? Just trying to point you in the right direction, have to go.....

Tim


Meaning you could have any instructor be a lead or the assistant.

Train of thought got disrupted..doing this at work...

[kruseanderson]

Yes all instructors are in the Instructor table but not every one will be on every roster
.

Assuming ALL instructors are in Instructor table, and they are either going to be the Lead or the Assistant, I think the following would be needed.

tblInstructors.InstructorId to tblRoster.LeadInstructor
tblInstructors.InstructorId to tbleRoster.AssistingInstructor

Not sure where these would go.

[ssanfu]

It sound like you might need to tweak your structure a little.
Would you post a pic of the relationship window?

[kruseanderson]

**** MODERATORS **** Image attachment does not seem to work through Int Exp 10 had to use Chrome. Something to check?

Here is what I currently have for relationships between these tables.



I have tried multiple variations to try to get this to work. Including trying a junction table. I left off the other tables that don't pertain to this problem. I am still working on making those play nice but have been focused on this area first.

[ssanfu]

I looked at the relationship pic. I've figured out a little, but there is a lot I don't understand about your project..

Your current structure limits the ability to add certifications - say an instructor get instructor status for EMT-III or PA. To add a new certification, you would have to change tables, queries, forms and reports.

Same for the "RosterT" table. What happens if you need to add "First Aid - Back Country Winter Survival"? Massive changes....

Pretty good on names - only a couple of fields with spaces and 6 fields with special characters (the "/"). Both can cause you headaches....

Is "FAInfant" = "First Aid Infant"?
These I haven't figured out:
FACInfant
TCFBLS
TCFACLS

Just curious, do you have a table for students?

I used pencil & paper (actually a whiteboard) to work on the instructor table. I don't have the data for testing (I tried to make up some) so this is not complete - still needs more changes. But it is a start. Designing a table structure is an art and science - not easy. I still struggle - a lot!!
But I'm getting better at it


Attachment 11920

[kruseanderson]

ssanfu,

I updated the field names and removed the problematic characters per your suggestion. Thank you.

FAInfant is First Aid Infant and the TCF has to do with training center faculty. These were just my shorthand for the courses or subsections of courses.

I appreciate your efforts to make this a higher functioning database, but it will only be used to track programs and instructors for American Heart Association classes. As such I wont ever need to add different certification or classes (Unless AHA changes). Your relationship chart look very involved and beyond my 1 week of self teaching Access. It is not an easy program to just sit down and do for the beginner.

I have been able to design and program my database to do everything I want/need so far except for the assisting instructor credit.

When someone teaches as the lead instructor that roster shows up in a subtable under their name. (Works great!) If there is an assisting instructor listed on the roster nothing shows up under their name. (This is the problem) I need to be able to give credit for teaching a class to anybody whether they are the lead or assistant instructor. Each instructor has to teach 4 classes every two years. It does not matter whether they are lead or assistant.

If I link the InstructorT.InstructorID to RosterT.AssistantInstructor1 that roster will show up under whoever is listed as AssistantInstructor1. But if I also connect InstructorT.InstructorID to RosterT.LeadInstructor then nothing shows up under anybody (presumably due to filter effect?!?)

Is there any way for two different fields of a form to be linked to two corresponding records in another table?

[ssanfu]

I would still recommend taking the time to develop a properly normalized dB, but.........

Try this:
Open a new query. Add tables "RosterT" and "InstructorT". Link the tables as described above.
Now add another "InstructorT" table. It will display as "InstructorT_1". Link it to "AssistingInstructor1"
Add another "InstructorT" table. It will display as "InstructorT_2". Link it to "AssistingInstructor2"
Add another "InstructorT" table. It will display as "InstructorT_3". Link it to "AssistingInstructor3"
Add another "InstructorT" table. It will display as "InstructorT_4". Link it to "AssistingInstructor4"

Design view: (I didn't put all of the table fields in this example)
Attachment 11943


This is what the SQL would look like:

SELECT RosterT.RosterID, RosterT.CourseDate, [InstructorT_4].[FirstName] & " " & [InstructorT_4].[LastName] AS Assistant4, RosterT.Coursetime, RosterT.Course, [InstructorT].[FirstName] & " " & [InstructorT].[LastName] AS Lead, [InstructorT_1].[FirstName] & " " & [InstructorT_1].[LastName] AS Assistant1, [InstructorT_2].[FirstName] & " " & [InstructorT_2].[LastName] AS Assistant2, [InstructorT_3].[FirstName] & " " & [InstructorT_3].[LastName] AS Assistant3
FROM InstructorT AS InstructorT_1 RIGHT JOIN (InstructorT RIGHT JOIN (InstructorT AS InstructorT_4 RIGHT JOIN (InstructorT AS InstructorT_3 RIGHT JOIN (InstructorT AS InstructorT_2 RIGHT JOIN RosterT ON InstructorT_2.InstructorID = RosterT.AssistingInstructor2) ON InstructorT_3.InstructorID = RosterT.AssistingInstructor3) ON InstructorT_4.InstructorID = RosterT.AssistingInstructor4) ON InstructorT.InstructorID = RosterT.LeadInstructor) ON InstructorT_1.InstructorID = RosterT.AssistingInstructor1;

This would be for a report, not a data entry form.

[kruseanderson]

I think I have figured out how to more or less do what I what using a union query. It doesn't differentiate whether the instructor was the lead or assistant but will show that they where an instructor of some sort in the class. Then I put that as a subform of my instructor form and it works.


You are probably correct on the database design. This is my first database and I am still learning access. Like I said before I have only played with this program for about a week.

Thank you for your assistance.

Major

[ssanfu]

For a week, you are doing very well.

Try the query in my previous post. It shows whether the instructor was the lead or assistant..

Keep working at it..