Many to Many Relationship

[BlueMonkey]

Hi

I'm trying to create for my boss which will record filenames of photographs and allow the user to mark one or more categories against the photo (eg Interior, Exterior, client site etc). That, to me sounds like a many to many relationship (one category might have many pics, one pic might have many categories).

I've created a "go-between table", linking the photo's table with the categories table, but am unsure how to get the thing working on a form etc.

What's the next step???

Cheers Blue Monkey

 

[montrose]

Blue Monkey, Could be just a one-to-many relationship.

tblPictures
PictureID (Primary key, Autonumber) (One side of 1:N relationahip)
PictureName

tblPictureDetails
DetailID (Primary key, Autonumber)
PictureID (Foreign key from tblPictures) (Many side of 1:N relationship)
Category (Comes from a look-up tbl of Picture catgeories)

tlkpPictureCategory
CategoryID (Can be an autonumber field, or an abbreviated text field that succinctly describes picture category, e.g., Intr for Interior, ClSite for Client Site, etc.)
TypeCategory (word description of category)

After you've set up your relationship with the one picture having many details, make your main form from the tblPictures. Make your subform a continuous form or datasheet view so that you can see the many types of categories that might belong to that one picture. When you put your "Catgory" field on the subform, make it a combo-box with the data source coming from you look-up tbl of categories of pictures.

When you put your subform on your main form, the parent-child links will be from Picture ID, and you'll be able to enter multiple categories for any picture.

If this doesn't seem to fit for you, post back. HTH, Montrose

Learn what you can and share what you know.

 

[BlueMonkey]

Montrose,

I've just re-created your steps in a new, clean db and it would appear to do the trick - thx a million. What little tweak do I need to make, in order for the category to appear as the actual text, not the ID (eg Exterior should appear not '2')

Thanks again Blue Monkey

 

[montrose]

Blue Monkey, In the combo box properties, Data Tab, set the Bound Column to 1 (one). On the Format Tab, set the Column Widths property to 0; 1.5. The use of the 0 (zero) for the first column width in essence tells Access to 'hide' that column and your actual data in column 2 (I just picked 1 1/2 inches, make it whatever you need) is now 'Visible' to the user. Set your List Width to correspond to whatever you pick for the best width for the now'visbile' column. If you have a lot of descriptors, you may want to change the default List Rows property from 8 to 10 or 15, or if you've got less desriptors, change it to 4 or 6 or whatever.

May also want to do a "Keyword Search" here on tek-tips for using the "NotInList" event with combo boxes. Eventually someone going to say they need to add a descriptor and the "NotInList" event will take care of that. I'm vba challenged so I can't help too much there.

Glad you got it going this far-best of luck, Montrose
Learn what you can and share what you know.

 

[BlueMonkey]

Montrose,

Brilliant - so far so good cheers. I'd made a small mistake which led to the above problem - I'd kept column 1 visible while messing around trying to sort problem 1 !!!!

One final query, if you don't mind. I've got the core of the database working OK now. I've noticed that in places have have a combo on my main form collecting data from another table. Sometimes this is with a defined relationship, othertimes it's without. I'm surprised this is possible - are there any pros and cons to either method?

Thanks again Blue Monkey

 

[montrose]

Blue Monkey, Glad you've got it going well. Not sure if I'm following your last question?? A comobo box usually allows the user to see a list of choices and pick what they need. If you've got it bound to a field, then what has been selected is then entered into that bound field. The source of the combo box can be whatever you need it to be-just so the value that you select and store is a valid field on your form. Case in point-your descriptors are 'selected' from another tbl, but the bound field to store the info in is actually in another tbl. This 'lookup' ability does not require that a relationship be established by you in the relationship window.

If you're basing the combo box on an updateable query using more than one tbl and have not defined a relatioship between the tbls in your relationship window, Access will 'know' that you've actually set the relationship between the tbls in your qry through the joins. Some folks are surprised when they go to the relationships window, click on SHOW ALL relationships, and relationships they did not set are showing up (e.g., tblCustomer_1 ) because Access 'knows' that you've used tblCustomer in a query and therefore made an indirect relationship.

Relationships are set between keys and the keys are usually indexed. Indexing may help to speed up searches. Since Access is primarily a "Relational Database" structure, the relationships will be defined by you in the Relationship window or in query design, recognized by Access. My readings of a few hundred pounds of Access books has led me to believe that setting relationships between your main data tbls and look-up tbls may or may not be done. Differing opinions, but it all works. There could be some other reason but it is not in my realm of knowledge.

Hope this is clearer than mud to you! If I haven't answered your question let me know or re-post the question so some of our more brilliant fellow "tek-tippers" will see it and respond. HTH, Montrose
Learn what you can and share what you know.