Statement to find where records are listed in another unrelated table

(Riki Armstrong) #1

I have 2 tables that are related only by an ENUMLIST that points to the other table as a lookup


While @LeventK has provided an amazing solution here:

it just seems to me that there should be a really simple work-around to achieve this “yellow” column.

(Levent Kulacoglu) #2

Have you tried with:

(Levent Kulacoglu) #3

Take a look at this pls.

(Riki Armstrong) #4

This is brilliant, thank you. A very simple solution to link two tables with a reversible one to many link.

I named my example tables and key fields the same name which might be confusing to some so here is the solution assuming table structure:

Table House
Key field - House_name
EnumList link to Pets - Pets_owned

Table Pets
Key_field - Pets_type
This virtual column to reverse link the houses:

SELECT(House[House_name], IN([_THISROW].[Pets_type],[Pets_owned]))

In words:
The list of values of column ‘House_name’
…from rows of table ‘House’
…where this condition is true: ((The value of ‘Pets_type’ from the row referenced by ‘pets_type’)
is one of the values in the list (The value of column ‘Pets_owned’))