Compare Groups with AND OR

[Simon]

I have 2 tables:

Name Code
Red 1
Red 2
Red 3
Blue 1
Blue 2
Blue 3
Green 4
Green 5
Green 6

Group Code
1 1
1 2
2 3
2 5
3 3
3 4
3 6

Now I want to compare Group 1 and Group 2 and return any Names that match,
so fully normalised it would be as follows:
1 and 3 or 1 and 5 or 2 and 3 or 2 and 5

I would expect the following results:
Group 1 and Group 2: Red, Blue
Group 1 and Group 3: Red, Blue
Group 2 and Group 3: Green

Ideally I would like to also show which code matched within the results, but
I think this is too complicated:

Compare 1:
Red: 1 and 3, 2 and 3
Blue: 1 and 3, 2 and 3

Compare 2:
Red: 1 and 3, 2 and 3
Blue: 1 and 3, 2 and 3

Compare 3:
Green: 4 and 5, 5 and 6

Do I require a 3rd table to list of the comparisons I wish to make?
If so, how would I code/query all this information to get the results?

Thanks,
Simon

 

[Allen Browne]

Here's a solution, but I'm going to suggest that you use an alias for your
field names. Both Name and Group are reserved words in JET, so to avoid
grief, we will make them show up as TheName and TheGroup.

1. Create a query using the 2 tables, joined on Code.
Drag the Name field into the grid, type TheName and a colon before this so
it looks like:
TheName: Name
Drag the Group field into the grid, and alias it so it appears at:
TheGroup: Group
In the query properties set Unique values to Yes.
Save as (say) qryNameGroup.
Close the query.

2. Create another query using qryNameGroup as an input 'table.' Use the Add
Table dialog to add qryNameGroup again, so it looks like 2 tables in the
upper pane of query design. Access will alias the second one as
qryNameGroup_1.

3. Join the 2 'tables' on both TheName and Code fields (i.e. you now have
qryNameGroup.TheName joined to qryNameGroup_1.TheName, and qryNameGroup.Code
joined to qryNameGroup_1.Code.)

4. Add TheName and TheGroup from qryNameGroup to the output grid. In the
Criteria row under TheGroup, enter:
<> qryNameGroup_1.TheGroup
This prevents a record from seeing itself as a duplicate.

The query now outputs a list with 2 columns: the first is the name, and the
2nd contains other groups where the same name appears.

If you want to concatenate the other group names together beside each
example, you can now use code to generate such a string to show in your
query. For details, see:
Concatenate values from related records
at:
http://allenbrowne.com/func-concat.html

 

[Simon]

Thanks Allen
This worked great, but you probably already knew that.

I would like to develop this further by being able to specify which groups
to compare, is this possible?

Thanks
Simon

 

[Allen Browne]

You can add criteria to the groups.

Alternatively, you could create a table with 2 fields, so you can enter the
pairs you want to match. Then use this table as another source table between
your 2 query source tables (i.e. join them to this table rather than to each
other.)