By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,166 Members | 1,130 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,166 IT Pros & Developers. It's quick & easy.

Forms With M:M relationship

P: 6
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
Jul 13 '06 #1
Share this Question
Share on Google+
23 Replies


P: 28
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
Jul 13 '06 #2

P: 6
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
Jul 13 '06 #3

100+
P: 179
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
Jul 13 '06 #4

P: 6
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
Jul 13 '06 #5

100+
P: 179
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 "intersection" 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 "tblAttributeIdent"). 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 tblAttributeIdent 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 tblAttributeIdent and Attribute (tblAttributeIdent 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 tblAttributeIdent table). The Attribute table will remain as a subform of tblAttributeIdent. Again, it is best to use comboboxes, and create pull down lists from your tblAttributeIdent 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
Jul 14 '06 #6

P: 6
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
Jul 14 '06 #7

100+
P: 179
no problem. just share your knowledge with anyone else that may have similar problems.

comteck
Jul 15 '06 #8

P: 6
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
Jul 15 '06 #9

100+
P: 179
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
Jul 15 '06 #10

P: 6
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
Jul 20 '06 #11

P: 70
What do you mean by intersection table?
Jul 20 '06 #12

100+
P: 179
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
Jul 21 '06 #13

P: 12
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 "intersection" 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
Jul 24 '06 #14

100+
P: 179
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
Jul 24 '06 #15

P: 3
Another Beginner here (actually more of an occasional/seldom user) - sorry,

I had been trying to select from a list of people and asigning them to a repair job and had tried and failed setting up many to many relationships (my "logic" being that over time many repair jobs, each one using from the same pool of people thus giving the potential for many uses of the same person)

Anyway I used your suggestion about attributes to products and solved the problem (for products I used repair job and for attributes I used person name) Perfect. Then I added a second table of people (say the first were carpenters, the second set were plumbers) and did the same. Also perfect. My main report (jobs) has two sub forms - one for each discipline. and all is fine.

However, I found that I cannot organise a report to show me, by job, all the people attached to that job. It works ok with just one set of people (in fact it almost did itself), but goes haywire with two - it seems that one group seems to "take priority"
Can you help please?
Jul 28 '06 #16

100+
P: 179
Sorry Ian,

I might need a little more detail here. Are you saying that you have a table with a list of jobs, and there is a field with people attached to the job? It seems like you want to select a job name/number, and then produce a report with all the names (Is this correct). Can you clarify this?

As well, can you include the tables where the data is taken from, relationships between the tables, PKs, etc.

It sounds like setting up a one-many relationship between 2 tables, and creating a query, and then having your report based on that query.

comteck
Jul 28 '06 #17

P: 3
Sorry It wasn't too clear. Yes I have a table with a list of repair jobs - Title REPAIRS and a PK of REPAIRID (autonumber). Other things in there include date and number of hours and description etc. Then, because I want to record a team of electricians (each job may have a different number of electricians but all taken from the same pool of people), I have another table ELECTRICIANS with simply one field ELECNAME which is set as PK (text). I then have a table ELECREPAIR with two fields which are the two PK's of the aforementioned tables. Both these fields are also set to PK on this table. (on this last table, the REPARID is a number field).
I built this by copying your advice at the beginning of this thread and it works perfectly (many thanks)
I then made a form based on REPAIRS and within it made a subform with the names and could thus allocate any number of people to the job. Also I made a report listing repairs and people involved. I also made a second report from this to list for each name, the repair jobs that person was involved in - all great and not one query in sight (although I foresee a need to filter by date and/or by name but this is ok)
NOW my problem begins;-
I want to record people from another pool against the jobs AS WELL AS the electricians, i.e. I have a group of Mechanicals and again there may be any number of these (or sometimes none) involved. I know I could add them to the same people list but then it gets very long as a dropdown list so I tried to get clever and make a second subform in the REPAIRS form. This part is ok, I used the same logic and thus made a table MECHANICS with a single PK field of MECHNAME (text) and a table MECHREPAIR with the PK's from MECHANICS and REPAIR and made the paralell links on the relationships diagram.

The form works fine with both these subforms BUT I cannot get a meaningful report for either of the two instances I want (as above). I would like to list the jobs and show the persons from both teams under each job but it seems that the two teams then interelate in various ways depending on how I run the wizard - In one way, I end up with just the ELEC NAMES correctly showing and an unwelcome prompt for a MECHNAME on opening the report which I ignore with the result of a list of errors in that column, or I get only the jobs that have a Mechanic and for each mechanic, the electrician names get repeated. etc etc.

I hope this is clear - thanks for following through all this long explanation.
Jul 28 '06 #18

100+
P: 179
If you got the form to work ok, then the report should work ok as well. The report just presents the exact same data in a different manner.

As a matter of fact, try this. Open your form in design view. Select "Edit", and "Select All". Then select "Copy", to copy all the contents of the form. Start a new report in design view, and make the Record Source the same as you did for the form. Paste the entire contents onto the report.

The subforms won't copy (or they'll look blank). Delete them, and then add both subforms in exactly the same way that you did on the form (i.e they will be sub-reports).

Your report will now look the same as your form. You can move the fields around to make more space, etc. because normally a report will be laid out differently than a form. But, this should work.

Let me know.
comteck
Jul 28 '06 #19

P: 12
It looks like IanGoodall is using an N:M relationship. Comteck, how come he shouldn't change it to 1:N? I guess I never really understood what axlr was trying to do, but I also didn't understand your replies comteck.

IanGoodall, I think given your level of experience your design is fine. If there are fields (such as name) that are shared between electricians and mechanics (and other types of workers) then there should be a base table, or supertype, (call it WORKER) that defines those attributes, then the ELECTRICIANS, MECHANICS, etc. tables would use the same PK as the WORKER table, setting up various 1:1 relationships. In other words there is a WORKER table with a PK WorkerID column set as an autonumber, then you have an ELECTRICIANS table with PK called WorkerID that is just a number but is unique, and same thing for the MECHANICS table. The WORKER table can also have what's called a discriminator column that holds a value that determines what other table (ELECTRICIANS, MECHANICS, etc.) holds the rest of the worker's data. Databases don't understand discriminator columns though, but they are helpful in queries.
Jul 29 '06 #20

P: 12
I just reread your replies comteck. Access, or any other DB on the market cannot do a N:M relationship between two tables - you have to create two 1:N relationships between 3 tables. That is exactly what you had alxr do, however. You had him/her create a N:M relationship, yet said they should never be used.
Jul 29 '06 #21

100+
P: 179
First of all, there are only three types of relationships that I know of:

one-to-one (1:1)
one-to-many (1:M)
many-to-many (M:M)

However, you are talking about a 1:N and a N:M relationship. I have never even heard of those. What does the "N" stand for?

Secondly, when I look back at my replies, I fail to see where I recommended using any relationship other than 1:M. If I did recommend M:M relationships, then I apologize if I've confused anyone. But, as I've said before, M:M relationships should be avoided if at all possible.

Alixr and Ian, bottom line is you can experiment with either or. And then from that you can decide which works best for your application.

Good Luck.
comteck
Jul 29 '06 #22

P: 12
1:N, 1:M - it's the same thing. M:N, M:M - almost the same thing. Taking the Products - Attributes example M:M would mean if a product had 2 attributes then each of those 2 attributes would need to be related to 2 products. M doesn't really stand for many, it's just a letter that represents some number, like in Algebra. They say N:M so that both sides of the relationship do not have to have the same number of participants.

Maybe the first order of business should be for you to explain to me how to create an M:N relationship in Access because I am not an Access guru.

If Access has the ability to create M:N relationships between two tables, using only two tables, then they've certainly added a cool feature!

When you create a data model - say we're using the Entity-Relationship Model (ERM) - then you can specify an N:M relationship between two, and only two, entities. When you go to actually create the database you need to transform those into two one-to-many relationships involving an intersection table.
Jul 29 '06 #23

P: 3
Hi Comtek,

Many thanks, I got the report to work by removing all previous links to the people that I had imported previously, then by inserting 2 sub reports as you said in exactly in the same way as I made two sub forms in the form. Once I realised which partition of the main report that they should be in, it worked fine.

My thanks for your help and patience for this.

Can I ask you for an explanation of something you said earlier to axlr about using combo boxes. In his example "tblAttributeIdent" which I transposed into my table ELECTRICIANS, he had a numerical PK with the text item in the next field.

I found when I did that, that it works ok but in the sub-form, you only ge tto see the numerical field (the sub form would comprise of only two fields - the REPAIRID which ties it to the main form, and the PK of the subform table. putting other fields in seems useless as they do not show the selected field-even after selecting the PK). Technically it works but you would have to remember all the names by their ID. This is why I decided that because all my Electrician names were differeent, I could use their uniqueness as the PK.

I guess I am missing something? what if I wanted to show more fields associated with the ELECTRICIAN in real time i.e updating as I make my selection of NAME?
Jul 31 '06 #24

Post your reply

Sign in to post your reply or Sign up for a free account.