Criteria based on a select statement

[Bobby]

Hello,
Is it possible, in Access, to base the criteria of a query on another
query. Here's an example of what I mean:

SELECT ORDER_NO, DESC, TYPE, NAME
FROM [Cutting List]
WHERE ((([Cutting List].[ORDER_ NO])= SELECT JobID from jobs where code
= 20846));

In otherwords, the order number in the Cutting list table is the jobid
from the jobs table where the code = 20846.

This is possible in SQL Server isn't it? Or did I dream it?

Thanks for any help

Colin

[Rick Brandt]

"Bobby" <bo****@blueyonder.co.ukwrote in message
news:11**********************@t46g2000cwa.googlegr oups.com...

Hello,
Is it possible, in Access, to base the criteria of a query on another
query. Here's an example of what I mean:

SELECT ORDER_NO, DESC, TYPE, NAME
FROM [Cutting List]
WHERE ((([Cutting List].[ORDER_ NO])= SELECT JobID from jobs where code
= 20846));

In otherwords, the order number in the Cutting list table is the jobid
from the jobs table where the code = 20846.

This is possible in SQL Server isn't it? Or did I dream it?

Try...

SELECT ORDER_NO, DESC, TYPE, NAME
FROM [Cutting List]
WHERE [Cutting List].[ORDER_ NO] = (SELECT JobID from jobs where code = 20846)

or

SELECT ORDER_NO, DESC, TYPE, NAME
FROM [Cutting List]
WHERE [Cutting List].[ORDER_ NO] In(SELECT JobID from jobs where code = 20846)
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Bobby]

Rick Brandt wrote:

Try...

SELECT ORDER_NO, DESC, TYPE, NAME
FROM [Cutting List]
WHERE [Cutting List].[ORDER_ NO] = (SELECT JobID from jobs where code = 20846)

or

SELECT ORDER_NO, DESC, TYPE, NAME
FROM [Cutting List]
WHERE [Cutting List].[ORDER_ NO] In(SELECT JobID from jobs where code = 20846)
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Thanks again Rick,

Colin