One way perhaps could be what you have yourself outlined above :
1)
An aggregate select query with
grouping on the Order & the Item
and
Sum on the Quantity & the Total
and
Having the Item criteria as the Item1.
2)
An aggregate select query with
grouping on the Order & the Item
and
Sum on the Quantity & the Total
and
Having the Item criteria as the Item2.
3) Join the 2 queries with an INNER JOIN & add the TOTALQTY & TOTALTotal to get the result.
4) Alternatively, also check if below ( a different way ) gives some guidelines :
Code:
SELECT
a.Order,
Sum(myTable.QTY) AS SumOfQTY,
Sum(myTable.TOTAL) AS SumOfTOTAL
FROM
(
SELECT
b.Order,
Count(b.[DRUG/ITEM]) AS [CountOfDRUG/ITEM]
FROM
(
SELECT DISTINCT
myTable.Order,
myTable.[DRUG/ITEM]
FROM
myTable
WHERE
(
(
(myTable.[DRUG/ITEM])="betamethasone"
Or
(myTable.[DRUG/ITEM])="mometasone"
)
)
ORDER BY
myTable.Order
)
AS b
GROUP BY
b.Order
HAVING
(((Count(b.[DRUG/ITEM]))=2))
)
AS a
INNER JOIN
myTable
ON
a.Order = myTable.Order
WHERE
(
(
(myTable.[DRUG/ITEM])="betamethasone"
Or
(myTable.[DRUG/ITEM])="mometasone"
)
)
GROUP BY
a.Order;
Thanks