Complex Queries

[cybin992000]

hi! maybe you guys could help me. i have two tables in msaccess, the first
table has two columns, the first column contains the names of columns in the
second table, and the second column has the "required values".
what I intend to do is compare the values from the second table with the
ones on the first. here's the sql code for your reference:

SELECT Table2.ID, Table1.Parameter, Table1.Required_Value, Table2.Column1 as
Actual_Value
FROM Table1, Table2
WHERE (((Table1.Parameter)="Column1") AND ((Table2.Column1)<>(SELECT
Table1.Required_Value
FROM Table1
WHERE (((Table1.Parameter)="Column1")))));

Union

SELECT Table2.ID, Table1.Parameter, Table1.Required_Value, Table2.Column2
FROM Table1, Table2
WHERE (((Table1.Parameter)="Column2") AND ((Table2.Column2)<>(SELECT
Table1.Required_Value
FROM Table1
WHERE (((Table1.Parameter)="Column2")))));

here are the columns for the output query:
ID | Parameter | Required_Value | Actual_Value

the query above works ok, up until the 30th repetition of the union query
when it generates the error "Query is too complex". does anyone know an
easier way of doing this? because i intend to check about a hundred columns.
i was thinking of writing it in VBA, but just as last resort.

i would greatly appreciate your response. thanks!

 

[Jeff Boyce]

I'm having trouble visualizing your data structure ...

Why (as in "what does it allow you to do?") are you storing
Table2.ColumnNames in Table1? How does having those column names listed
help you accomplish ...???! something.

I ask because there may be alternate approaches folks here could offer.

Regards

Jeff Boyce
Microsoft Office/Access MVP