group orders with same values

[Guest]

I have a table with say 3 fields: Orders, Items and Charge as follows:
Order Items Charge
1 A 2
1 B 3
1 C 2.5
2 A 2
2 B 3
3 D 2
3 E 2
3 F 2
4...
......
I have to find orders which have charge equal to a given value say 2 for
all items in that order (an example of such an order would be order 3 in the
above table). How can I write a query for this?

 

[David S via AccessMonster.com]

Sounds like an odd sort of request, but not difficult to do. First, create a
query OrderCharges to get all of the unique Order and Charge combinations:
SELECT Table1.Order, Table1.Charge
FROM Table1
GROUP BY Table1.Order, Table1.Charge;

From this, you can figure out which orders have the same charge for all of
their items - let's call this query SameOrderCharges:
SELECT OrderCharges.Order, Count(OrderCharges.Charge) AS CountOfCharge
FROM OrderCharges
GROUP BY OrderCharges.Order
HAVING (((Count(OrderCharges.Charge))=1));

You can then use this in anothe rquery to do what you want it do:
SELECT Table1.*
FROM Table1 INNER JOIN SameOrderCharges ON Table1.Order = SameOrderCharges.
Order
WHERE (((Table1.Charge)=[Enter Charge]));