Full Version: qeury across columns, rows and tables
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
mrudd
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
Peter46
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).
mrudd
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
R. Hicks
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
mrudd
Thanks for the advice Rioky, I take your point and will split the table as required
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.