Filtering in a query

[Guest]

Query1

OrderNumber
OrderLine_DeliveryMethod
ShipQty
Wgt
ShipWgt
ExtendedRetailCharge
ShippingAmount
StateProvince
PostalCode

G0594267
WHTGLOVE
1
120
120
3198
262.781
MI
48306

G0594267
STANDARD
1
21
21
398
52.557
MI
48306

G0594267
WHTGLOVE
1
102
102
3125
306.576
MI
48306

G0594269
STANDARD
1
1
1
500
0
Ca
90025

G0594269
STANDARD
1
1
1
100
0
Ca
90025

G0594270
FLRSALE
1
10
10
149.4
0
IL
60661

G0594274
STANDARD
1
46
46
598
34
MA
2210

G0594276
STANDARD
3
8
24
894
45.8
NH
03755-1404

G0594277
STANDARD
0
14.5
0
0
0
NY
10011

G0594278
STANDARD
1
91
91
1097
0
CA
94110-1313




I’m trying to build a query that excludes those order numbers that do not
include “white glove†(as shown in the â€OrderLine_DeliveryMethod†field)
items. i.e…the order number G0594267 has standard delivery items and white
glove delivery items, of which I need my query to capture the entire order,
while excluding these other orders that do not include white glove delivery
items. This is of course a small portion of the data. Any
thoughts?????Thanks

 

[Allen Browne]

Use a subquery to select the OrderNumbers that have this kind of delivery
method.

You will end up with an expression such as this in the Field row in query
design:
EXISTS (SELECT TOP 1 ID FROM OrderTable AS Dupe
WHERE Dupe.OrderNumber = OrderTable.OrderNumber
AND Dupe.OrderLine_DeliveryMethod = 'WHTGLOVE')

Replace "ID" with whichever field is your primary key.

If you have a normalized design, you will have an Orders table and an
OrderDetails table, and it would be more efficient to run this against the
Orders rather than OrderDetails. (See the Northwind example database if that
comment doesn't make sense.)

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066