Junction Table

[snowboarder234]

http://www.box.com/s/34ccaa6d7c85953274f8

I need to set up a relationship between the student and the funding sources. I think a junction table is the tool for that, but I haven't been successful in setting it up.

The two tables are:
tblStudents -- one student can have multiple funding sources. This table already is linked to another table.
tblFundingSources -- one funding source can have multiple students

I tried to set up a junction table: tblFundingJunction. However, when I link the junction table to the student table I cannot open the existing subrecords in the student table. Further, it doesn't achieve the many-to-many relationship I was trying to set up.

I'm sorry I am not able to save this database into a lower version.

Thanks in advance to anyone who is able to help.

[AccessMSSQL]

You need fields like this in all tables:
tblStudents should have a StudentID as primary key
tblFundingSources should have a FundingSourceID primary key
tblStudentsFunding should have a StudentID and FundingSourceID field as foreign key relationships.
I am downloading your app now to take a look so I will give you more feedback shortly.

[AccessMSSQL]

You have the right fields set up but you don't have the relationship drawn between the tblStudents and tblFundingJunction. You need to create the relationship.

[snowboarder234]

You have the right fields set up but you don't have the relationship drawn between the tblStudents and tblFundingJunction. You need to create the relationship.

Thank you for your assistance. However, I took the steps you recommended and now I have another concern. Now, the tblFundingJunction, when linked to the tblStudents, overrides the previous link I had before, which was to the Attachments table.

To further illustrate: My original linking to the tblStudent was to an Attachment table.


Now, having linked the tblStudent to the tblFundingJunction, the attachment table is no longer visible as a subdata record.


It's good that the funding data displays. However, I need BOTH the attachment table and the funding table to display as subdata records in the tblStudent. Is that possible?

[AccessMSSQL]

You cannot display both at the same time in your table. You can change what datasheet you want to appear - see attached images:

[snowboarder234]

You cannot display both at the same time in your table. You can change what datasheet you want to appear - see attached images:

The information about the technique for accessing more than one subdata sheet was helpful. Thank you.

Unfortunately, the junction table is not working and I simply cannot see what I need to do differently. The problem is the funding source is showing up on EVERY entry in the student table when, in reality, only two entries in the student table should be assigned additional funding thus far. In other words, only a few people in the student table will have additional funding. But, one student might have up to 3 or more additional funding sources, while another student, for example, will have none or maybe one.
Updated database



Thank you again for your assistance.

[AccessMSSQL]

Okay. Let me take a look and see what's going on.

[AccessMSSQL]

Ahhh..I see the problem here. Your tblFundingSources has student information in it. You need to remove the student information from that table.
tblFundingSources should only have the funding source information. The tblFundingJunction must be populated with a studentID and a FundingSourceID. See attached modification.

You might want to add a subform to your main student form that is bound to the tblFundingSources table (just like your attachments subform).

[snowboarder234]

Okay, I removed the student information from the tblFundingSources and populated the tblFundingJunction with only the StudentID and FundingSourceID. I added the tblFundingSources as a subform (or I thought I did), but when I look the main student table (tblStudent) again, ALL students display funding sources, when at this point, there are only two students.

Is my expectation incorrect in assuming that when, after setting up the junction table the tblStudent subform will selectively reveal ONLY those individuals who are linked to funding sources? Am I trying to do something that's not possible? I had envisioned the same kind of relationship as when I view the tblAttachment that is linked to the tblStudent. Each student is assigned to the correct attachment. Some have attachments at this point, some don't, and the relationship bears that out.

I will say, when I look at the tblFundingSource, the subform has the correct linkage to the correct student. However, if you look at the resultant frmFundingSource, it's a rather inelegant design. The student who has 3 sources of funding appears in the frmFundingSource as 3 separate records. Again, I was hoping to achieve something that would show 1 student = multiple funding sources. Maybe that's not possible at this point?

Thank you once again for your help.
Updated Database

[AccessMSSQL]

You are getting there but I think problem you are having is you are getting tblFundingSources confused with tblFundingJunction. The tblFundingJunction table is the table that needs to be in the subform on your main student form and also appearing in your datasheet. Base your subform on tblFundingJunction. Open the tblStudent table and change the subdatasheet to point to tblFundingJunction - see my earlier screenshot on how to do this. I re-posted your project in my last post and it has all of the changes I recommended. Here are some screenshots of how it should be set up.
Here is the student table with subdatasheet shown:


I hope this helps!

[QIZHIQIANG]

[snowboarder234]

You are getting there but I think problem you are having is you are getting tblFundingSources confused with tblFundingJunction. The tblFundingJunction table is the table that needs to be in the subform on your main student form and also appearing in your datasheet. Base your subform on tblFundingJunction. Open the tblStudent table and change the subdatasheet to point to tblFundingJunction - see my earlier screenshot on how to do this. I re-posted your project in my last post and it has all of the changes I recommended. Here are some screenshots of how it should be set up.
Here is the student table with subdatasheet shown:


I hope this helps!

Wow -- I would have never been able to do this without your help. Thanks so much. Those junction tables are really tricky. It's working now exactly as I had envisioned. Next challenge will be doing the same thing for individuals who have published articles and books. I'm stoked to start that after all the funding data is entered.

Thank you, thank you, thank you.