[Heisenberg]

Hi

Please can some help me with this relationship design problem - it really has me stumped!

Here is what I am trying to get the design to do for me:

• I have 200-300 students that as part of their course need to complete practice placements
• These students go to different placements during the duration of their course
• They are assigned a mentor in each area they attend
• Mentors can mentor more than 1 students (I think I need a many to many relationship for this but I can't figure this out)
• A PLT/ facilitator is assigned to each area to which they are responsible for ( this changes sometimes so not sure if it would be best keeping this on a word document instead)

It's just this relationship design I'm struggling with - please can someone help.

Daniel

[ScottGem]

Ok, Yes you have a many to many between students and mentors. But your table; tblStudent Placement Attendance seems to cover it. It would need one record per combination of student area and mentor. So mentorID would be a foreign key in that table.

No you don't need a Word doc, you either keep a record for each change or edit the record is you don''t need a history.

[Heisenberg]

Hi Scott

Am I right in that from what you're saying the relationships are already correct? So in theory I should now be able to make multiple records linking students to a mentor - am I right?

Really appreciate you help with this as it will save me lots of time from using a spreadsheet. Thanks again with the last few answers on AskExperts.

Daniel

[ScottGem]

Once you make the change to include a single record per combination as I suggested, I think you do have your relationships correct.

To enter the data you will need to use forms and subforms.

[Heisenberg]

Hi Scott

When you say single record per combination, what do you mean? Do you mean when the mentor field in the student placement table is the foreign key as I think this is already set.

[ScottGem]

Initially, you had the field in that table named Mentors. That implied that you might have one record with several mentors. What I'm saying is that the combination of area, student and mentor should be unique.

[Heisenberg]

Hmm is it possible you could have a quick look for me - I've attached the database as this might make easier for me to understand.

Really sorry for being a pain but I appreciate your advice.

[ScottGem]

Your not being a pain, I will take a look as soon as I can.

[ScottGem]

OK, I took a look and I'm not sure what I'm supposed to be looking for. The attachment didn't include the Placement Attendance table we've been talking about.

But I do see some issues. First, don't just accept the default name for your Autonumber PKs. I always name my PKs so they refer to the table. For example, MentorID. Then make your Foreign Keys the same name. Makes it easier to spot relationships. Second, Do not use spaces in object names, This will come back to haunt you. Use either underscores or CamelNotation. Also don't use special characters like / or -. You can use the Caption property to display the spaces and characters. Third, in one table you have Address line 1 & 2. but you don't in the other tables. You should be consistent. Because Access can store a line feed within a field, I don't see any need for multiple lines. So one StreetAddress field should be enough.

Finally, your Special Circumstances field. Using lookup fields on the table level is not recommended. And the only time you should use a Value list is when you have a short, static list of selections. I'm not sure what you are planning for Special Circumstances, but you should probably have a lookup table for them and a junction table to assign them to a student.

[Heisenberg]

Hi Scott

Thank you for having a look for me. The placement attendance table which we have been discussing is called tbl studentplacementattendance, which links students, mentors and placements; because you mentioned that the combination of area, student and mentor should be unique, I just wanted you to have a look to see if this was right. You may need to click 'view all relationships' to see the seek linkage. This table was what were discussing? Or am I wrong?

I have to admit I've heavily modified this from something else but as you mentioned it does need tweaking. I don't have any use for the special circumstances so will be deleting that.

[ScottGem]

Ok, I should have thought to hit Show all. So, again, If I understand that table, it shows each instance of the combination of Student, Area and Mentor. So if Student A attended Area 1, and worked with Mentor X, that would be one record. If the same student in the same area worked with Mentor Y, that would be another record.

And to repeat I would name my Foreign Keys the same as my PKs. So in that table I would have AreaID instead of Placement Area and MentorID instead of Mentors.

[Heisenberg]

That's right, that's what I am trying to achieve and hope that that table will be able to do that. The student will move to different areas also so I am hoping a new record will be made for this as I want a record of where the student has been in the past and where they are in the present. That table should be able to do this?Will follow your advice with consistency.

[ScottGem]

Yep, you add a record for each instance of the combination.

[Heisenberg]

That's great :)Thanks again for your help and advice Scott with this - as well as the Askexperts website.
I'd probably encounter all sorts of probs otherwise.

Thank you pal

[ScottGem]

Glad to help. Feel free to ask if you encounter any more stumbling blocks.