Forms With M:M relationship

[IanGoodall]

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?

[comteck]

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

[IanGoodall]

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.

[comteck]

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

[dna5122]

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.

[dna5122]

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.

[comteck]

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

[dna5122]

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.

[IanGoodall]

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 "tblAttributeId ent" 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?