not equal relationship

[chris martoglio]

I am having trouble understanding how the not equal relationship works. Consider two tables with a many to many relationship, say Group and Individual. I have linked these two tables using a third table (called GroupIndiv) consisting of two fields: GroupID and IndividualID. On a layout attached to GroupID, I have two portals. In one portal, I want to show the individuals that are part of the current group. In the other portal I want to show the individuals that are not part of the current group.

I

[The Shadow]

What I'm seeing with your setup is that it only works as desired when there's only a single member in the group. Otherwise, I see all rows.

My thinking is that each GroupIndiv record is processed independently, then the result is the union of all those results. So, with Inviduals {A,B,C,D} if I have a group with members A and C in it, the <> set for A is {B,C,D}, and the <> set for C is {A,B,D}, the union is always everyone: {A,B,C,D}.

Rather than the union (OR), you want an intersection (AND), to give: {B,D}, but I'm not seeing a clean way to do that without resorting to multi-keys.

I've added an attachment with a simple setup if someone else wants to take a look.

[The Shadow]

Here's a multi-key example using a value list to generate the necessary key.

[chris martoglio]

That's the conclusion I came to also. The not equal works well with a single record, but when attempting to exclude multiple records from a related table, it shows all records. I did experiment with the less than and greater than signs independently with multiple records in both original and related tables, and found some interesting results there also. When relating one group of records via less than to another group, the result appears to be all records that are less than the highest record in the original table. And when relating one group of records via greater than to another group, the results appears to be all records that are greater than the lowest record in the original table.

I'll give the multi-key approach a try. Thank you very much.

Chris.