How do I join tables using a link table in a form?

[imellor]

Hi All

I'm a newbie at access, I believe I understand the basics of relational databases and queries, but I am really stuck on an issue.

I have been asked to maintain a directory for our local Scout district. It's currently in a spreadsheet, but is crying out to be a database.

Here is what I have done so far:

I have created a "group" table, this lists all the groups in the district.
I have a 2nd table called "people" this holds details of all the people in the district.
As some people can have a role in multiple groups, I have created a 3rd table called "link", this is to link the people to groups.

Having read various different posts on sub forms, I am still at a loss on how to create a form / subform to populate the link table.

My initial thoughts were to have a combo box on the main form, listing the people and then have a list of groups on the sub form (the are only 24). I would have a check box next to each group and if checked, it mean that the person selected in the combo is associated with that group.
However my issue is that the tick box would be a "yes/no" field and the entry in the link table needs to be a number.

So I am at a loss on how to proceed, can anyone point me in the right direction please?

Thank you

Ian

 

[imellor]

Post your spreadsheet with dummy data and column headings.

Thank you.

I have attached the spreadsheet with anonmylised minimal information.
There is one tab for Group and one tab for people

Regards

Ian

 

[imellor]

Ian,

see if this makes sense.

Richard

Richard

Thank you, I follow the logic. However I thought as it is possible for a person to have a role in multiple groups, I would need an intermediate table to form a many to many relationship?
This is what I have set up, but I can't my head around how to set up updating the data in a form?

Regards

Ian

 

[imellor]

Ian,

In this example, ONE ARTIST can have ONE or MANY ALBUMS and an ALBUM can have ONE or MANY ARTISTS related to it.

link removed

You can download this example and get a closer look.

Richard

Thank you, I spent quite some time scouring the web looking for some guidance, but couldn't find any. This download is ideal, as I can dissect and see how it is done. Thank you very much.

Ian

 

[imellor]

Aggghhh why is Access sooo difficult.

Rather than amending the example database, I thought I would learn more from starting from scratch using the example database as a guide.

I have added the tables and set the relationships ,however as usual the forms are a PTA..

I added a form with a record source as my people table. Then I added a subform using the wizard. However when I change the source object of the subform to my link table, as per the example database, I just get a blank white sheet, which you can't add any controls to?

The annoying thing is I can understand the logic of the example database, but trying to reproduce it so hard :-(

Thanks very much in advance, for any pointers anyone can give me.

Ian

 

[jdraw]

Ian,

Go through this tutorial and get your database designed.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

Good luck

 

[imellor]

Ian,

Go through this tutorial and get your database designed.
"Linked removed, so I can post"

Good luck

Thank you , but I have done the relationships (I think!!)

Ian

 

[imellor]

Ian,

You're right Access has a fairly steep learning curve. My first application for our school district, maybe 20 or more revisions. Don't be discouraged at the number of times you have to start from scratch. The more you learn the more sophisticated your designs get.

Richard

Richard

Thanks for your encouragement, I am getting closer.
Can I check I am selecting the correct tables for the subform?

If I have the following three tables, which hopefully will be self explanatory...
tblPerson
tblLink
tblGroup

When I add the subform, I am selecting fields from both tblPerson and tblLink

I then delete all the control that have been automatically added, change the form to continuous and add the combo control.

When I add the combo control, I only select fields from tblGroup, but store the result to a bound field in tblLink

Does this sound correct?

Thank you

Ian

 

[imellor]

Richard

Thanks for your encouragement, I am getting closer.
Can I check I am selecting the correct tables for the subform?

If I have the following three tables, which hopefully will be self explanatory...
tblPerson
tblLink
tblGroup

When I add the subform, I am selecting fields from both tblPerson and tblLink

I then delete all the control that have been automatically added, change the form to continuous and add the combo control.

When I add the combo control, I only select fields from tblGroup, but store the result to a bound field in tblLink

Does this sound correct?

Thank you

Ian

All

Thanks for all your assistance, I finally got it working.
Either I or Access was getting confused by having the same field name for ID in several tables. I have now made them unique and got it all working.