Select query too complex?

[Guest]

I'm working with a user who recently got a new computer. On her old machine,
this query would run fine. On the new machine, it tells her that her query is
too complex. We're running Office 2003 SP2, on Windows XP SP2. The query is
as follows:

SELECT MATERIAL_NBR, SERIES
FROM BOOKINGS
WHERE MATERIAL_NBR LIKE 'WV*'
AND MRP <> 336
AND MATERIAL_NBR NOT IN (SELECT MATERIAL_NBR FROM WAVEWORKS_LAM);

I think it has something to do with the additional SELECT statement in the
WHERE statement, because when I take that out the query runs fine. And like I
said, it ran just fine on the other machine.....

 

[Guest]

There doesn't seem to be anything wrong. It could be the computer.

What happens when you run this?
SELECT MATERIAL_NBR FROM WAVEWORKS_LAM;

Are the MATERIAL_NBR fields the same datatype in both tables?

Are BOOKINGS and WAVEWORKS_LAM tables or possibly queries?

SELECT MATERIAL_NBR, SERIES
FROM BOOKINGS
WHERE MATERIAL_NBR LIKE 'WV*'
AND MRP <> 336
AND NOT EXISTS
(SELECT "X"
FROM WAVEWORKS_LAM
WHERE BOOKINGS.MATERIAL_NBR = WAVEWORKS_LAM.MATERIAL_NBR);

 

[Guest]

When we run the SELECT MATERIAL_NBR.....statement on its own, it works just
fine.

The datatypes are the same.

Bookings is a table, and WAVEWORKS_LAM is a query.

We also have a backup copy of the database from before she changed PC's. It
doesn't have updated data, but the query runs fine on her machine.

 

[Charles Wang[MSFT]]

Hi GHawkins,
Since this issue seems to be environment related, I would like to collect
more information from you:
1. Could you please post the detailed error message here so that I can
check whether or not it is a known issue?
2. Does this issue only occur on the user's computer? What is the result if
other users run it on their machines?
3. What is the result if you create a new simple Access database with the
only several tables and the query, and then run it on the user's machine?

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Guest]

The warning message is simply "Query is too complex." That's the only message
given. It's a Microsoft Office Access warning message (I assume it's a
warning message because of the yellow triangle image on the message, even
though it doesn't let you run the query).

I will post more when we can try the other things.

 

[Charles Wang[MSFT]]

Hi GHawkins,
Thanks for your response.

From my research, this issue might occur if a query statement was very long
and query logic was complex. However your query statement seemed very
simple.
In this case, could you please check my 2nd and 3rd questions and let me
know the result? If this issue can be reproduced, it will be a good news
for further research.

Look forward to your confirmation.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Charles Wang[MSFT]]

Hi GHawkins,
Just a kind reminder, we have not received your response. If this issue
persists, please feel free to post back.
We are glad to work with you for further research.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Steven Cheng[MSFT]]

Hi GHawkins,

Have you got any progress on this issue? Charles has discussed this issue
with me and since the problem is likely machine specific and would be
difficult to troubleshoot, we would like to gather some further information
from you so that we can perform some local tests and help you confirm the
problem behavior. Is it convenient for you to provide us a simplified repro
package for testing?

Also, if you feel the issue very urgent and want to get rapid resolution
and close assisant, I suggest you consider contact the product phone
support center so that a dedicated Microsoft Support Engineer will work
closely with you and provide some further troubleshooting assitance on this.

Please feel free to post here if there is still anything we can help you.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Walter Wang [MSFT]]

Hi GHawkins,

Thank you for using MSDN Managed Newsgroup support service.

We're about to close this post since we haven't heard of you for several
days. I also understand that this issue is environment specific and you
want to get some idea what might be wrong. Unfortunate for such
non-reproducible issue, there's really not much we can do from our side,
newsgroup support service sometimes might only give some general
suggestions, but for some issue on-site debugging will be the only way to
fully find the root cause.

Our Customer Support and Service can provide such support service, if you
need to solve the issue promptly.

I am contacting you to see whether there is anything we can do for you. If
you have any comments on our support service, website design, our product
limitation, our process, and etc., please don't hesitate to let me know. I
will do my best to follow up.


Regards,
Walter Wang ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.