Many-to-many relationship

[WalterInOz]

I’m building a database of protein mutants we use in the lab. A lot of analysis is done on these proteins by various people and it’s becoming a bit of a nightmare keeping track of all the information per protein. What we need is a database. I’m getting a reasonable grip on it all I think with the excellent examples I have found on the forum but there are obviously areas that I have great difficulty with. The main problem for now is that many-to-many relationships are involved and I just cannot get them to work properly.

On each protein up to 8 different types of test are performed. To keep things easy I’ll call them A1 (for Analysis1), A2, A3 etc. These are done by different people from a group of 7. I’ve called them O1 (Operator1), O2, O3 etc. Each of the operators can do multiple analysis and the operators overlap. So e.g. O1 might be particularly good at A1, A4 and A7, O2 might be good at A2, A4 and A5, and O3 predominantly does A1, A4 and A6. What it comes down to is that each operator can in principle do each of the analysis so I have to give that option in the database.
What does work (but is not the solution as it introduces 8x duplication of data) is if I have a separate table with operators for each analysis. The same tblOperators is linked 8 times. There must be a way to link each of the analyis to only 1 tblOperators, probably with the use of a linking but I just cannot work it out from the examples I've found here.

Can someone help me on my way please?

Thanks,
Walter

 

[Banana]

You will need three tables for this:

tblOperators
OperatorID (Autonumber Primary key)
FirstName
LastName
blah blah
blah blah

tblAnalysis
AnalysisID
AnalysisType

tblOperatorAnalysis
OperatorAnalysisID
OperatorID (Foreign key, related to OperatorID tblOperator)
AnalysisID (Foreign key, related to AnalysisID in tblAnalysis)

The tblOperatorAnalysis is where you create many-many relationship. It should be related to both tblOperator and tblAnalysis as one-many relationship.

HTH.

 

[WalterInOz]

Hi Banana,

Thanks for your reply.
Apart from the OperatorAnalysisID that is what I have (Why would you need that field? I don't see it in linking tables of other examples I've seen).

However, that still doesn't allow me to select Operators for each of the analysis.
Not on a form anyway.

What am I missing?

 

[Banana]

OperatorAnalysisID exists only to give a unique record, while allowing more than one OperatorID and AnalysisID to be duplicated.

If you didn't have that field and let's say OperatorID is a primary key in the junction table, you would have a One-One relationship, which would mean an operator can only have one analysis because that's all a one-one record can hold. If you didn't have any keys at all in the junction tables, this will cause problem with queries or other actions where you want to be able to look up and modify fields.

If that didn't help, explain more; what kind of error you get when you try to add more operator on the form; how is your form designed?

 

[WalterInOz]

better explanation of the problem

I think I need to explain the problem a bit better. The solution Banana proposes doesn't do it I think.

Attached is a screenshot of the table that gives me all the grief: tblQuality
This table must hold data on 9 different analysis/test and who did them.
As a result there are 9 operator field, one for each test.

I have this part of the database working with the same tblOperator 9x linked to this table, one link to each operator field. If I have a linking table as proposed by Banana above I don't see how I can select up to 9 different operators if need be for the various operator fields.

I have the feeling that I somehow have to include the 9 fields in the linking table or indicate to the database in another way which operator name has to be insert in the tblQuality. But How?

Thanks for your help,

Walter

 

[Karma]

Hi,

From the info you gave banana his/her analysis is correct, from the table in your picture the same principle can be applied.

I've knocked together a quick db and attached is a piccy of my best interpretation. I have added a machine table so that you could make sure that a test is associated with the correct type of machine i.e. your not choosing a HPLC assay with a mass spec. It also allows you to associate more than 1 machine with a type of test so if you have 2 HPLC's designated for a particular test you can.

I can upload the db if you want.

hth

k.

 

[WalterInOz]

I've followed your example and with data in the tables the queries go. Howver, when I enter new test data using a form it's a dissaster. The tables don't hold the data or I cannot save the data because corresponding data is missing from the other tables. I've set al relations to enforce referential integritie and cascade update and delete fields. It cannot be really that difficult, I must be missing something really silly I think. Any pointers?

 

[Banana]

Did you say you were basing your form on a query?

If so, understand that not all queries are updatable, which is why you get this kind of behavior.

Do a search around the forum on updatable query to learn how it work. Be sure to look at how joins help this matter, too. Any queries are updatable if you can make relationship clear to Access/Jet. For one-many relationship, you may want to use a subform instead.

HTH.