0

I am trying to check if the values from Table1 exist in Table2.
The thing is that the values are comma separated in Table1

Table 1

ID TXT
1 129(a),P24
2 P112
3 P24,XX
4 135(a),135(b)

Table 2

ID
P24
P112
P129(a)
135(a)
135(b)

The following only works if the complete cell value exists in both tables:

SELECT Table1.ID, Table1.TXT
FROM Table1 LEFT JOIN Table2 ON Table1.[TXT] = Table2.[ID]
WHERE (((Table2.ID) Is Null));

MY QUESTION IS:
Is there a way to check each comma separated value and return those that do not exists in Table 2.
In above example the value XX should end up in the result.

MK01111000
  • 770
  • 2
  • 9
  • 16
  • Is this really a comma separated string and not a multi-value field? Data samples should be provided as text tables or CREATE and INSERT action SQL, not images. – June7 Mar 28 '22 at 17:51
  • If this is CSV and not multi-value field, I expect will need a VBA custom function. – June7 Mar 28 '22 at 18:08
  • it's a short text field with values separated by comma's – MK01111000 Mar 29 '22 at 04:15
  • Storing values as comma delimited lists is a bad design from a performance and data integrity standpoint. Storing the individual values as separate rows, instead of as a list would save a lot of future headaches. `the value XX should end up in the result.` Why? I'd expect to see `3P129(a)` because that value is not contained in any of the TXT lists. – SOS Mar 29 '22 at 19:01
  • `3P129(a)` was indeed incorrect. I edited my post. Value `XX` is not in Table 2. – MK01111000 Mar 30 '22 at 06:31

1 Answers1

0

Not sure why you store your data in that way (which is bad practice as sos mentioned above), but you need to mimic the temp table like in SQL server.

  1. Select from table1 and create different txt rows per id.
  2. Insert the results from section 1 into the table3.
  3. Select from table3 and join it to table2.
  4. Delete table 3.

Table3 the temp table

ID TXT
1 129(a)
1 P24
2 P112
3 P24
3 XX
4 135(a)
4 135(b)

Here is some explanation MS Access database (2010) how to create temporary table/procedure/view from Query Designer

The scion
  • 1,001
  • 9
  • 19