I have 1 table with 800 entries (rows), each has 23 columns containing product IDs. Table 1 example:

Product1 Product2 Product3..... Product23
1 A C H P
2 B H P J
3 F C T C
4 B S B B

I have a second table with has only one column with a list of approx 70 product IDs only. Table 2 example:

Prodcut IDs
A
B
J...

I want to run a query that selects all rows (1-800) from Table 1 which contain at least one of the Products IDs (across the 23 Product ID columns) listed in Table 2. In this example the query would select rows 1,2 and 4 only on the basis that they contain at least one of the IDs from Table 2 (A, B, J...)

Many thanks in advance.

Matt

You either have to (a) write 23 queries, one for each column, and build them into a single union query or (b) write one 'Distinct records' query based on the 2 tables (no join/cartesian join) with 23 OR statements in the WHERE clause (or via its equivalent in the query grid).

Thanks Peter,

It seems very convoluted but I will give the second option a try, I already tried doing something similar using 70 OR statements but this exceeded the 1,024 limit imposed on SQL queries

Your problem is being caused by your structure not being properly normalized ...
You have "repeating groups" in your table ....
Each product entry should be in another table and not as seperate fields within the same record ...
Until you address and fix this problem you will continue to have this problem .. and many other problems getting the information out of your database ...

RDH

Thanks for the advice Rioky, I take your point and will split the table as required