query for any quote detail not ordered

[bicyclops]

I'm trying to produce a query that will show all quote numbers that have no
line items that were ordered. Structure is this:
Tables: Quotes & QuoteDetails, one-to-many
Field: [Ordered] Yes/No in QuoteDetails

There will be several records in QuoteDetails for each quote number. Some of
the records for a given Quote will have [Ordered] turned on, but not others.

I can produce a query that has shows quote numbers that have ANY item
ordered easily enough, but I'm not sure how to produce one that has NO items
ordered.

Thanks in advance.

 

[David Lloyd]

One alternative is to get a list of all Quotes that have at least one item
ordered and then join the results of this query against the QuoteDetails
table to find the Quotes with no items ordered.

At least one item selected (named: QuoteDetails_Yes):

SELECT DISTINCT QuoteDetails.QuoteNum
FROM QuoteDetails
WHERE (((QuoteDetails.Ordered)=Yes));

Now do an OUTER JOIN on the QuoteDetails table to find non-matching Quote
numbers:

SELECT DISTINCT QuoteDetails.QuoteNum
FROM QuoteDetails LEFT JOIN QuoteDetails_Yes ON QuoteDetails.QuoteNum =
QuoteDetails_Yes.QuoteNum
WHERE (((QuoteDetails_Yes.QuoteNum) Is Null));

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I'm trying to produce a query that will show all quote numbers that have no
line items that were ordered. Structure is this:
Tables: Quotes & QuoteDetails, one-to-many
Field: [Ordered] Yes/No in QuoteDetails

There will be several records in QuoteDetails for each quote number. Some of
the records for a given Quote will have [Ordered] turned on, but not others.

I can produce a query that has shows quote numbers that have ANY item
ordered easily enough, but I'm not sure how to produce one that has NO items
ordered.

Thanks in advance.

 

[bicyclops]

That works great. I thought it had something to do with a join but couldn't
quite get it. Thanks.