Tables structure & relationships

[Granddad]

Hi

I've been tasked with rebuilding (starting again!) a database at work, but only have limited knowledge of Access so apologies in advance.

What we want to do is link multiple context records (archaeological term for a wall, ditch, soil layer etc) to multiple photo records. eg CONTEXT 1 is shown in Photo 1, 2 and 4, and PHOTO 1 shows Context 1, 10 and 11. PHOTO 2 shows Context 2 and 3. etc etc. From this information we should be able to run various queries and reports eg CONTEXT by PHOTO number etc.

1) We currently have separate tables for Context and Photo (which both contain other fields), and we've linked these through a third table containing only fields for PhotoNo and ContextNo (see attached image). We've done this because its the only way we can see that it's possible to join multiple contexts to multiple photos but if there is there a better way of doing this I'd appreciate the advice.

2) However we then come to the forms for each table, and I am unsure how to best add the info. Ideally we want enter the Photo details on one form (including the PhotoNo and some other details, but excluding ContextNo). On a separate form we then want to add the Context details (inc ContextNo) and link it to the relevant photo(s). Because there could be multiple ones ideally we want a button to a third form which automatically inserts the ContextNo from the previous form into it so you only need to add the PhotoNo.

Obviously if there is a simpler/better way of doing 1) it may change how we go about 2) but I hope that makes some sort of sense - any help would be much appreciated.

 

[EasyCarlo]

1) ....We've done this because its the only way we can see that it's possible to join multiple contexts to multiple photos but if there is there a better way of doing this I'd appreciate the advice.

what you are dealing with is called a "many-to-many relationship" and as far as I know there is no other way to handle it so you already did a good job

you may get a further confirmation for that searching for "Access 2007 many-to-many". you will an article on Microsoft Office website (sorry cannot paste the link because I posted less than 10 post)

2) However we then come to the forms for each table, and I am unsure how to best add the info. Ideally we want enter the Photo details on one form (including the PhotoNo and some other details, but excluding ContextNo). On a separate form we then want to add the Context details (inc ContextNo) and link it to the relevant photo(s). Because there could be multiple ones ideally we want a button to a third form which automatically inserts the ContextNo from the previous form into it so you only need to add the PhotoNo.

you may insert a subform to either the Photo or Context form bound to the many-to-many relationship table which automatically inserts one of the two foreign keys to that table. for the second one you may insert a Combo or Listbox that displays the records inserted in the other form.
Actually there are many ways to handle such a situation and you should find the one which best fits your needs....

hope it helped
Carlo

 

[Granddad]

what you are dealing with is called a "many-to-many relationship" and as far as I know there is no other way to handle it so you already did a good job

you may get a further confirmation for that searching for "Access 2007 many-to-many". you will an article on Microsoft Office website (sorry cannot paste the link because I posted less than 10 post)

you may insert a subform to either the Photo or Context form bound to the many-to-many relationship table which automatically inserts one of the two foreign keys to that table. for the second one you may insert a Combo or Listbox that displays the records inserted in the other form.
Actually there are many ways to handle such a situation and you should find the one which best fits your needs....

hope it helped
Carlo

Hi Carlo

Many thanks for your reply - it's great to know we are on the right track.

One query - I added a subform but how do we get it to automatically insert one of the foreign keys?

Cheers!

 

[Granddad]

Don't worry I've sorted it!

 

[Frothingslosh]

Don't worry I've sorted it!

In order to help future users who encounter this problem, would you please explain how you solved it?

 

[EasyCarlo]

The Subform must be linked to the parent form (let's say the Photo form bound to the Photo table). To ensure that, just set the Link Master Fields and Link Child Fields properties of the control which holds the subform respectively to the name of the two fields linked in the Photo and "many-to-many table" tables relationship)
This way, for each record added to the subform, the PhotoID foreign key will be automatically updated to the master field value.

To fill data in the second foreign key of the "many-to-many table" (let's say the Context key) just bound a combo box to that foreign key. The rowSource of that combo draws values from the context table. If you display in the Combo more that 1 column from the Context table make sure that the first column is set to the Context Table primary key (which should be the foreign key of the child table). Or, more in general, make sure that the order of the column containing the primary key (first column count as 0) is the same as the Combo Bound Column property (first column count as 1!!)

Carlo