Query too Complex

[msabbasi]

Hi everyone!

I have two tables in my database, named 'n' and 'o' ... n for new and o for old. I have to compare the two tables to c if any value has been changed in any cell for a given cell Id which is the primary key. The fields I'm comparing are more than hundred. Also the values are in number but I'm showing them both concatinated in the result sheet to know which value has been replaced by which so for this I have also used cstr function. Though the query works when I use lesser fields but for all the fields it say's query too complex. I'm pasting the smaller query. Can someone plz tell me if I can make my query more efficient and easy too so the code is less and I can compare all the fields too. I using Acess as database.

SELECT o.CI AS CI, cstr(o.TCH)+' - '+cstr(n.TCH) AS TCH, cstr(o.NCC)+' - '+cstr(n.NCC) AS NCC, cstr(o.BCC)+' - '+cstr(n.BCC) AS BCC, cstr(o.BCCH)+' - '+cstr(n.BCCH) AS BCCH, cstr(o.CBA)+' - '+cstr(n.CBA) AS CBA, cstr(o.CBQ)+' - '+cstr(n.CBA) AS CBQ, cstr(o.MS_TXPWR_MAX_CCH)+' - '+cstr(n.MS_TXPWR_MAX_CCH) AS MS_TXPWR_MAX_CCH, cstr( o.[Static TRX Power class])+' - '+cstr(n.[Static TRX Power class]) AS [Static TRX Power class], cstr(o.RXLEV_ACCESS_MIN)+' - '+cstr(n.RXLEV_ACCESS_MIN) AS RXLEV_ACCESS_MIN, cstr(o.[RACH Min Access Level])+' - '+cstr(n.[RACH Min Access Level]) AS [RACH Min Access Level], cstr(o.[RACH Busy Threshold])+' - '+cstr(n.[RACH Busy Threshold]) AS [RACH Busy Threshold], cstr( o.CELL_RESELECT_HYSTERESIS)+' - '+cstr(n.CELL_RESELECT_HYSTERESIS) AS CELL_RESELECT_HYSTERESIS, cstr(o.[Cell Reselect Parameters Indication(PI)])+' - '+cstr(n.[Cell Reselect Parameters Indication(PI)]) AS [Cell Reselect Parameters Indication(PI)]
FROM o, n
WHERE o.CI=n.CI And (o.BCC<>n.BCC
or  o.MS_TXPWR_MAX_CCH<>n.MS_TXPWR_MAX_CCH Or o.CBA<>n.CBA Or o.CBQ<>n.CBQ Or o.BCCH<>n.BCCH Or o.NCC<>n.NCC Or o.TCH<>n.TCH Or o.[Static TRX Power class]<>n.[Static TRX Power class] Or o.[DL DTX]<>n.[DL DTX] Or o.[Cell Extension Type]<>n.[Cell Extension Type] Or o.[Direct retry]<>n.[Direct retry] Or o.[Allow Reassign]<>n.[Allow Reassign] Or o.[Half rate supported]<>n.[Half rate supported] Or o.[Cell type]<>n.[Cell type] Or o.[Layer of the cell]<>n.[Layer of the cell] Or o.[Cell priority]<>n.[Cell priority] Or o.[Min DL level on candidate cell]<>n.[Min DL level on candidate cell] Or o.[PBGT HO allowed]<>n.[PBGT HO allowed] Or o.[Load HO allowed]<>n.[Load HO allowed] Or o.[Intra-cell HO allowed]<>n.[Intra-cell HO allowed] Or o.[SD HO allowed]<>n.[SD HO allowed] Or o.[PBGT HO Thrsh]<>n.[PBGT HO Thrsh] Or o.RXLEV_ACCESS_MIN<>n.RXLEV_ACCESS_MIN Or o.[RACH Min Access Level]<>n.[RACH Min Access Level] Or o.[RACH Busy Threshold]<>n.[RACH Busy Threshold] Or o.CELL_RESELECT_HYSTERESIS<>n.CELL_RESELECT_HYSTERESIS Or o.[Cell Reselect Parameters Indication(PI)]<>n.[Cell Reselect Parameters Indication(PI)] );

[NeoPa]

I would certainly recommend that the FROM clause is changed to use an INNER JOIN.
Otherwise, the layout of the SQL is not such as to make it practical to read.

[msabbasi]

I would certainly recommend that the FROM clause is changed to use an INNER JOIN.
Otherwise, the layout of the SQL is not such as to make it practical to read.

Can u plz tell me how n how it will help. The two tables are identical represent data of 2 diff days. I need to point out of the changes wherever they might have occurred.

[NeoPa]

I can't tell you how to precisely as I don't know the MetaData for your tables. Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Assuming that the primary key of each table is a field called [CellID], it would be like :

FROM o INNER JOIN n
  ON o.CellID=n.CellID

PS Please don't use text-speak when asking for help in the forums (FAQ - How to Ask a Question). It's hard enough to understand the questions as it is (missing most of the relevant information), without the extra complication of translating into simple English.

[msabbasi]

Thank you so much for replying. I know how inner join works.
The MetaData for my tables is:

Table Name=n
Field;Type;Index/info
CI;Numeric;PK
[TCH Flow Control Allowed];Numeric
[Frequency Band of Reassign];Numeric
[T3109(s)];Numeric
CELL_RESELECT_HYSTERESIS

These are 4 of the fields in my tables o and n which are identical.The rest of almost 100 fields are also numeric.
I've modified your code for inner join a bit to make it compare all the fields in both tables

SELECT *
FROM o INNER JOIN n ON o.ci=n.ci And (o.BCC<>n.BCC
Or o.[TCH Flow Control Allowed]<>n.[TCH Flow Control Allowed]
Or o.[Frequency Band of Reassign]<>n.[Frequency Band of Reassign]
Or o.[T3109(s)]<>n.[T3109(s)]

This code works very nicely for the 4 fields here but when I add rest of the 100 similar OR clauses it says 'query too complex".

Secondly I want to identify where the values have changed eg where
o.[T3109(s)]<>n.[T3109(s)]. Instead of showing all the fields of both tables n and o I want to show only 1 column per similar column eg only 1 column with heading [T3109(s)] instead of 2. And i want to show a concatenated value if value has eg " 2 - 3" which shows that 2 from table o has changed to 3 in table n.

[NeoPa]

Thank you so much for replying. I know how inner join works.

You certainly seem to. Confused that you asked the question (Post #3).

The MetaData for my tables is:

Table Name=n
Field;Type;Index/info
CI;Numeric;PK
[TCH Flow Control Allowed];Numeric
[Frequency Band of Reassign];Numeric
[T3109(s)];Numeric
CELL_RESELECT_HYSTERESIS

These are 4 of the fields in my tables o and n which are identical.The rest of almost 100 fields are also numeric.
I've modified your code for inner join a bit to make it compare all the fields in both tables.

SELECT *
FROM o INNER JOIN n ON o.ci=n.ci And (o.BCC<>n.BCC
Or o.[TCH Flow Control Allowed]<>n.[TCH Flow Control Allowed]
Or o.[Frequency Band of Reassign]<>n.[Frequency Band of Reassign]
Or o.[T3109(s)]<>n.[T3109(s)]

An impressive job. I had to check in the help file to make sure it was valid, but it certainly is 8) In this case you may be missing a closing parenthesis to match the opener before o.BCC<>... Nevertheless still impressive.

This code works very nicely for the 4 fields here but when I add rest of the 100 similar OR clauses it says 'query too complex".

There are limits to what Jet SQL can handle. We'll get to sneaky ways around this later.

Secondly I want to identify where the values have changed eg where
o.[T3109(s)]<>n.[T3109(s)]. Instead of showing all the fields of both tables n and o I want to show only 1 column per similar column eg only 1 column with heading [T3109(s)] instead of 2. And i want to show a concatenated value if value has eg " 2 - 3" which shows that 2 from table o has changed to 3 in table n.

It's perfectly possible to show the "2 - 3" instead of both fields individually. You simply specify that in the SELECT clause.

SELECT ...
       '"' & o.BCC & ' - ' & n.BCC & '"' AS BCCDiff,
       ...

Unfortunately, the number and specifications of the columns in a query is built into the query. You can't select different columns on a record by record basis.

[NeoPa]

Now, to get to the part about comparing more fields than Access usually likes, it is possible to define QueryDefs (Saved Access Queries) that create a combination of the fields as a single, large composite field. You would then work with these queries and compare the resultant composite fields from the two queries.
As an illustration :

Qo =
SELECT *,
       [TCH Flow Control Allowed] & ',' &
       [Frequency Band of Reassign] & ',' &
       [T3109(s)] & ',' &
       CELL_RESELECT_HYSTERESIS AS CompField
FROM o
 
Qn =
SELECT *,
       [TCH Flow Control Allowed] & ',' &
       [Frequency Band of Reassign] & ',' &
       [T3109(s)] & ',' &
       CELL_RESELECT_HYSTERESIS AS CompField
FROM n

This can, of course, be done with subqueries if you prefer. It would probably make for quite a large and complex SQL string though. That was English understatement by the way.
The code to use these queries would be of the form :

SELECT *
FROM Qo INNER JOIN Qn
  ON Qo.CI=Qn.CI And Qo.CompField<>Qn.CompField

If the composite field itself gets too long, then break it down into two or three of them which are individually smaller.

[msabbasi]

I'm working on doing it as 2 queries and then combining it as you said. I hope it'l work.
But one thing I cant find a solution to is that the comparison code that I posted earlier gives me the rows in which the change has occurred but I also want to know which cells in the rows have been changed. Had I been accessing the dataset from a programming language I know what I'd do. I would have used a for loop with an if statement and display data only in the cells where a change has occured else it would be blank. Can I do this in Access some how?

[NeoPa]

...But one thing I cant find a solution to is that the comparison code that I posted earlier gives me the rows in which the change has occurred but I also want to know which cells in the rows have been changed. Had I been accessing the dataset from a programming language I know what I'd do. I would have used a for loop with an if statement and display data only in the cells where a change has occured else it would be blank. Can I do this in Access some how?

I did cover this question in post #6.
However, if you like code it is possible (not straightforward but possible) to build a table of the results that you want by processing through the input query. The table contents could then be displayed in a simple query. Even then the query would have a fixed set of columns in the output. That's what queries do.