Forms With M:M relationship

[axlr]

Hi ,
I am currently working on developing forms for data entry using access 2003. My parent form Product is linked to another form Attribute , via many to many relationship . the intersection table is populated with FK from both tables and a Unique PK . My problem is i am unable to create a link using the form wizard. the wizard populates the new records within the respective tables but it does not establish a link between the parent product and its attributes or vice versa.
any help would be much appreciated , I am not very proficient with access or coding languages . but if any one could take time out to explain this problem to me , I would really appreciate that.

Thank you all for your efforts

[zzqxxq]

If I am understanding you right, you have a product table and an attributes table - and each product can have many attributes. You could have a product called peach and its attributes would be sweet, fuzzy skin, grows on a tree. Then you could have a product called grape with attributes sweet, smooth skin, grows on a vine. As you see each SET of attributeS describes ONE product. this is a ONE (product) to many (attributes) relationship. You have the relationship set up for Many to Many. That is the issue that is preventing Access from setting up a parent child relationship for your products and attributes. Try editing the relationship in the Relationship window to One to Many then try the wizard again. If that doesn't do it write back

[axlr]

My apologise , I did not explain the relationship well enough . For example if you have one product , peach and it may have several atrributes like sweet , fuzzy etc. But the problem lies when we try and use the same attributes for other products. For example attribute sweet can be associated with product apple as well . So that is why we need a many to many relationship , as some attributes will be re used for products.
I hope this clarifies the problem a bit.

Thank you for your help

[comteck]

I'm not really understanding what you're asking either. Could you give a little more detail as to which of the fields are the primary and foreign keys in each table, and some examples of the types of data in each table.

comteck

[axlr]

There are two forms based on two seperate table. the Products form inputs data within the product table and is linked to the attribute form. Between the products table and attibute table , I have an intersection table whose PK is an autonumber field .PK for products table is PRODID , it is again an autonumber , and the PK for attributes is ATTRID , again an autonumber field. Both PRODID and ATTRID are also in the intersection table , because i did not know how else to elstablish a relationship between the tables. One attribute can be listed with many products and many products can have multiple attributes. However when i created the form using wizard , i was able to populate data within the respective tables but there was no way of linking the two. For example if i enter a new product and associate attributes with this new product, two things happen; one the intersection table does not get populated with the required attrid and prodid keys.and if i look up the attributes in the attribute table , they are not linked with the products and vice versa.
I hope this clarifies the question somewhat.
any help would be much appreciated , I am really stuck with this problem and I dont know to fix it, as you can tell my knowledge of access is limitited.

Thank You

[comteck]

OK, I think I understand your problem, and the solution is not exactly an easy one. The first thing you have to do is copy your database to a different directory in case this doesn't work. However, I am 99.9% sure that it will.

After you've backed up your database, do the following:
First of all, I don't see the purpose of the "intersecti on" table. You don't need a third table to set up a relationship between 2. Secondly, the only autonumber that you should have is PRODID in the product table. Actually, your "Attributes " table is, in a way, going to be your intersecting table. Also, you are going to have to identify the attributes with a specific ID (i.e "sweet" would have an id of "01". It would have to have this same id for all products that has sweet as an attribute). For example, the way you're set up right now is you have ATTRID as an autonumber. Let's say that the first product has "sweet" as an attribute. It's id could be "01" (created by the autonumber). The next product that has sweet as an attribute will have a different id.

So, here is what you do. First, get rid of the intersecting table. Create another table (we'll call it "tblAttributeId ent"). The 2 fields for this table will be "ATTRID" (Number) and ""Attribute " (Text). Then open the table and assign each attribute a specific ID. For example, "sweet" could be "1". ATTRID is the PK.

Secondly, in your Attribute table, change ATTRID from autonumber to number, and in the description say that it's the same as the ATTRID in the tblAttributeIde nt table. Add another field called PRODID, and make it number as well. Add a description that this is the same as PRODID in the Product table. Make both ATTRID and PRODID the PKs.

Now create a one to many relationship between the Product table and Attribute table (Product table being the one, and Attribute many). Also, create a one to many relationship between tblAttributeIde nt and Attribute (tblAttributeId ent being the one, and Attribute the many). That's it for the tables.

Now, when you enter the attribute on the form, the ATTRID will have to be entered into the table as well. The best way to do this is with a combobox, creating pull down menus. This will ensure the same ATTRID is entered for each attribute. If you're not sure how to do this let me know. If there is a link between the Product table and the Attribute table (works best if Attribute is a subform of Product), a link will be created between the 2 tables with PRODID. PRODID will then be assigned automatically to the Attribute table for each attribute for that record.

Lastly, to refence the other products using the attributes (which was your original problem), you may need to create another form. The form will be set up pretty much the same as your product form (instead of the main form linking to the Product table, it will link to the tblAttributeIde nt table). The Attribute table will remain as a subform of tblAttributeIde nt. Again, it is best to use comboboxes, and create pull down lists from your tblAttributeIde nt table to ensure you are getting the correct ID. Again, let me know if you don't know how to do this. If you can get everything else to work, but not this part, just use a text box for testing purposes, and link the text box to ATTRID. To test just type in the ATTRID and see if you get the results. Only thing is you will have to memorise or write down all your IDs.

Hope this helps. Good Luck.
comteck

[axlr]

Wow that worked perfectly , thank you for all the help you provided , I really appreciate you taking the time to help me out with my problem
Please let me know if i can be of any assistance to you in the future

Thanks

[comteck]

no problem. just share your knowledge with anyone else that may have similar problems.

comteck

[axlr]

I have another question linked to the same problem i mentioned above . If i Was to join another table to the Product table , using the PK PRODID and a table called Measures with PK MEASID. Now these tables have a M:M relationship too and I created an intersection table called PRODMEAS and used to the PK from Product and Measures to form the PK for this intersection table.My problem is that access won't let me link the PRODID twice . I can either have it linked to display the attributes wrt product ( as we did above) or display measures wrt product. Is there a way around this other than creating a duplicate table.

Thanks

[comteck]

Again, why do you have intersecting tables. You should not need a third table to create a relationship between 2. I'm assuming that the Measures Table has values that are related in some way to the Product table.

Also, you should be able to link PRODID as many times as you like to any other table, providing there a field to link to (i.e PRODID must exist in the other table).

So, again get rid of the intersecting table. Pretty much the same as you did before... Set up PRODID and MEASID as PKs in the Measures table (data type for both is "Number", not "Autonumber "). You should then be able to create a relationship between the Product table and the Measures table.

You didn't say whether the Measures table had one or many values related to the Product table. I'm assuming it has many. Therefore, the relationship should not be many to many... it should be one to many (Product table one, Measures table many).

Good Luck.
comteck

[axlr]

sorry , I did not respond earlier , I was sick and thus unable to get to work.I set up the tables as you suggested above, Now i have product table with a M:M relationship with attributes and product tables also has M:M with measures.I want to know if this is good DB practice or if this is even a feasible option because for some reason if two M:M relationship , the data associations get skewed up.
for example if product mango has attributes num 1 and 2 , if i enter a related measure for mango . what happens is that all the attributes and measures start getting associated with all the products.

Is there a way around this issue?

Thank you for your time

[wlc04]

What do you mean by intersection table?

[comteck]

Look back through my replies, and you will find that at no point did I say to set up an M:M relationship. As a matter of fact, many programmers recommend that M:M relationships never be used in database design. It can create too much confusion.

Both your tables should be set up as one-many relationships, with Products table being the "one" in both cases (i.e a one-many relationship between Products and Attributes table, and a one-many relationship between the Products and Measures table).

You should go back through this entire thread, and read all the replies again. It might help you to understand it better.

comteck

[dna5122]

I'm brand new to these forums - came across them on a Google search. I wouldn't normally reply, but I can't believe some of the things being taught in this thread and it's recent.

I'm having the same problem with Access 2003. I have an M:N relationship that I need to be able to edit on a form. I've been trying to use subforms, but to no avail.

"Many programmers recommend that M:M relationships never be used in database design." ?!?! There's no confusion - we're professionals for God's sake! M:N relationships (as they're supposed to be called) are necessary a lot of the time, they're a part of database design. The best illustration is when you need to limit the values (in axlr's example, Attributes) that can be associated with other entities (in axlr's example, Products). You want to define a specific, limited set of Attributes, and allow Products to be associated with as many Attributes as needed. That's where the "intersecti on" table comes in, zzqxxq. The Products table has a primary key, and the Attributes table has a primary key. The intersection table has a column for the primary key of the Products table and a column for the primary key of the Attributes table.

Staying with this example, my problem is akin to having a form for editing a single Product with a subform that lists Attributes. In the subform I want each item to be a combo box whose source is the table of attributes. I set the record source of the subform to be a join between the intersection table and the Attributes table (so the combo box can display the friendly name of Attributes associated with the current Product). But when add a new row in the subform, it also adds a new row to the Attributes table, not just the intersection table. I'm assuming that's because the source for the subform contains both the intersection and Attributes table.

I hope that all made sense. And I don't want to hear nothin about changing to no 1:N relationships! :D

[comteck]

First of all, welcome to the forum. Secondly, as much as your response is appreciated, I'm not sure what you mean by you "can't believe" what is being taught here.

Whether you agree with it or not, those are the recommendations of a lot of programmers (i.e not to use M:M relationships). If you look at a lot of the online tutorials, they say that M:M relationships are seldom used as well. Simply because in most cases, they are not required (as in axir's case).

I'm not saying that an M:M relationship can never be used. Obviously, it can be if it's available under MS Access. However, you will find in most cases, a 1:M relationship is much easier to set up with much less chance for error, since M:M relationships are quite often confusing (especially for the inexperienced programmer). As we saw in axir's case, the 1:M relationship produced better reults than the M:M.

comteck