"Query is too complex" (1 Viewer)

[crownedzero]

I am running a simple query to that appends data to a table by ItemNumber, unfortunately I have about 300 ItemNumbers to run against my Inventory table. The error I'm getting is "The Query is too complex", other than creating multiple queries and appending the data each time is there a way around this?

 

[namliam]

what is the exact query you are trying to execute??

 

[crownedzero]

INSERT INTO tblCSN ( [UPC Number], Available, Description, Type )
SELECT tblInventory.[UPC Number], tblInventory.Available, tblDescription.Description, tblInventory.Type
FROM (tblInventory INNER JOIN tblCSN ON tblInventory.[UPC Number] = tblCSN.[UPC Number]) INNER JOIN tblDescription ON (tblCSN.[UPC Number] = tblDescription.[UPC Number]) AND (tblInventory.[UPC Number] = tblDescription.[UPC Number])
WHERE (((tblInventory.ItemNumber)="4210"));

However I have a large criteria of ItemNumber, so I've got a good number of OR's in there.

Also trying to figure out why the above statement is not appending any records.

 

[crownedzero]

Figured out why it wasn't appending any records now just curious to the length of my sql statement, for the moment I've written 4 different queries and just appended one onto the next.

 

[SOS]

Did you know you can use IN to replace a lot of ORs?

WHERE (((tblInventory.ItemNumber) In ("4210", "4211", "4212", "4215", "4466", "4500" ))

for example.

 

[crownedzero]

I was not aware of that, learning as I go lol

Is there a limit the the number variables using an IN statement?

 

[SOS]

Is there a limit the the number variables using an IN statement?

Not that I'm aware of, but there may be a limit to the entire SQL String length.

 

[crownedzero]

Thanks, I'll give it a shot

 

[namliam]

There is actually a limit in the In statement, but its many many characters IIRC.... I forget the details, but there is a limit.

The SQL itself can be as many characters/joins/etc as you see fit though...

Strangest thing though...
tblInventory.ItemNumber="4210"

Having the NUMBER be a string?? *Ugh*

Also why append records to a new table with the same details that is already contained within other tables?? Maintaining duplicated data is a major headache!