Inclusion and Exclusion

  • Thread starter Thread starter JJP
  • Start date Start date
J

JJP

To simply the question I have a orders database with the following fields:
Order Number
Model Number
Options.

each record contains the same order number for a given order, more than one
model number may exist (no problem filtering on that) but there can be a half
dozen different options (call them A, B, C, D, E F, G) for example.

I would like to be able to search for specific configurations that inclulde
ony certain options such as B,D,F, but not A,C,E, or G.

Can anyone recommend the best way to do this?

Thanks,
 
Use an In phrase in your WHERE clause:

SELECT Table1.Field1
FROM Table1
WHERE (((Table1.Field1) In ("A","C","E")));

You can build the content of the In phrase in VBA and pass it to the query
as a parameter (or use a list box).
 
To simply the question I have a orders database with the following fields:
Order Number
Model Number
Options.

each record contains the same order number for a given order, more than one
model number may exist (no problem filtering on that) but there can be a half
dozen different options (call them A, B, C, D, E F, G) for example.

I would like to be able to search for specific configurations that inclulde
ony certain options such as B,D,F, but not A,C,E, or G.

Can anyone recommend the best way to do this?

Thanks,
Click to expand...

IN ("B", "D", "F") AND NOT IN ("A", "C", "E", "G")

should work.
 
Do you want to restrict the results per order or per order/model? If the
former:

SELECT *
FROM Orders AS O1
WHERE EXISTS
(SELECT *
FROM Orders AS O2
WHERE O2.[Order Number] = O1.[Order Number]
AND Optiions IN("B","D","F")
AND NOT EXISTS
(SELECT *
FROM Orders AS O3
WHERE O3.[Order Number] = O1.[Order Number]
AND Optiions IN("A","C","E","G");

If its per order/model then correlate the subqueries on both e.g.

WHERE O2.[Order Number] = O1.[Order Number]
AND O2.[Model Number] = O1.[Model Number]

I'm assuming the inclusion is for any of B, D or F, not all of them.

Ken Sheridan
Stafford, England
 
Brian, John and Ken,
Thanks for your help. This looks like it will do the the trick.

Ken Sheridan said:
Do you want to restrict the results per order or per order/model? If the
former:

SELECT *
FROM Orders AS O1
WHERE EXISTS
(SELECT *
FROM Orders AS O2
WHERE O2.[Order Number] = O1.[Order Number]
AND Optiions IN("B","D","F")
AND NOT EXISTS
(SELECT *
FROM Orders AS O3
WHERE O3.[Order Number] = O1.[Order Number]
AND Optiions IN("A","C","E","G");

If its per order/model then correlate the subqueries on both e.g.

WHERE O2.[Order Number] = O1.[Order Number]
AND O2.[Model Number] = O1.[Model Number]

I'm assuming the inclusion is for any of B, D or F, not all of them.

Ken Sheridan
Stafford, England

JJP said:
To simply the question I have a orders database with the following fields:
Order Number
Model Number
Options.

each record contains the same order number for a given order, more than one
model number may exist (no problem filtering on that) but there can be a half
dozen different options (call them A, B, C, D, E F, G) for example.

I would like to be able to search for specific configurations that inclulde
ony certain options such as B,D,F, but not A,C,E, or G.

Can anyone recommend the best way to do this?

Thanks,
Click to expand...
Click to expand...
 
Back
Top