• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

nested query interpretation

Is it true that "Select * From sometable Where idRecord IN(Select numOrder From tblOrders Where....);" - is interpreted litterally by jet as: "Select * From sometable Where idRecord IN(1000,1001,1002,1003,1004,1005,...);" !? - Hence the 'query too long' errors may appear sometimes?
0
NNOAM1
Asked:
NNOAM1
  • 7
1 Solution
 
JezWaltersCommented:
If your tblOrders sub-query returns (1000,1001,1002,1003,1004,1005,...), then the simple answer is yes!  :-)
0
 
JezWaltersCommented:
Depending on what you're using the resultant records for (specifically, if you need the recordset to be updatable or not), you can probably improve performance by using a join instead.
0
 
JezWaltersCommented:
Your question doesn't give a complete query so I can't either, but I was thinking along these lines:
SELECT sometable.*
FROM sometable, tblOrder
WHERE sometable.idRecord = tblOrders.numOrder AND
tblOrders....

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
JezWaltersCommented:
... or if you prefer using the JOIN keyword:
SELECT sometable.*
FROM sometable INNER JOIN tblOrder
ON sometable.idRecord = tblOrder.numOrder
WHERE tblOrder....

Open in new window

0
 
JezWaltersCommented:
Access is quite poor at optimising queries that contain sub-queries, and will typically evaluate the sub-query for EVERY record returned by its parent query - even when the sub-query records are identical each time.
0
 
NNOAM1Author Commented:
The point is - I need the parent query to be updatable!
0
 
JezWaltersCommented:
You should get an updateable query if you use the JOIN keyword (as in id 34186631 above).
0
 
Dinesh SubramanianSenior Software EngineerCommented:
hi,
the best solution is to use the join query. (as in id 34186631 above).
0
 
JezWaltersCommented:
You could try re-phrasing your query using the EXISTS predicate too, but I doubt you'll get much of a performance difference.

INNER JOIN is your best bet!  :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now