Many-to-Many Query problem

[bees292]

Hi.

I have a many-to-many relationship set up which seems to be working fine. I have now created a query that requests criteria from a form (See gif for relationship and query design).

The problem is that when i put in details for the first record the query works. however, for any other record it doesn't work. I'm not spelling things incorrectly and the data seems correct in the tables....

I hope this is a common problem when you first start designing queries!!! It's bugging me!!!

 

[Rickster57]

Missing data

My bet is that your joins are not correct. For instance, if you have a paper without a link to an experiment it will not show up in the query. Check your links where you may have to set it to something other than a direct link. In other words show me all authors with a paper that do not have a specific experiment associated with it. Hope this gets you in the right direction.

 

[bees292]

Thanks that's helpful....

My bet is that your joins are not correct. For instance, if you have a paper without a link to an experiment it will not show up in the query.

This is correct.

Check your links where you may have to set it to something other than a direct link.

I'm new to this and I'm not sure what you are refering to. Are you talking about the join types?

In other words show me all authors with a paper that do not have a specific experiment associated with it.

again, not sure exactly what you mean here.

 

[bees292]

Sorry, I'm having a bit of trouble understanding this. The join types can be set as 1,2 or 3. Could someone suggest which I might try for the relationship structure given above....

Thanks

 

[Rickster57]

Query

Yes, I am referring to the join type. Look at the record that "Works". What fields exist with data in which tables. Once you see the difference between that record and others that don't "worK" (by that I guess you mean no data shows up) you can determine which type of link you need between the tables.

Does the record that doesn't show up in your query have any data in the paper or experiment table. Look at using the join type #2.

 

[bees292]

Yes, records show up if and only if they have an experiment record. But to me this doesn't make any sense as 'Experiments' doesn't enter into the query. A join of type 2 between papers and experiments doesn't resolve this problem

 

[bees292]

what a div! I was changing the join type in the overall relationship not for the query. Join 2 works. Thanks for your help rickster