Distinct Count Problem

[infomania]

I have a data set that looks like this:
ITEM QTY BoxNbr
ABC 6 1
ABC 6 1
ABC 6 1
ABC 6 2
ABC 6 2
ABC 6 2

The query result I want is:
ITEM Sum-Qty Boxes
ABC 36 2

I want to count(distinct BoxNbr) (as in SQL). How can I do this in Access?

 

[EstuardoSierra]

infomania:
The Jet does a great effort adopting the main sql keys not all them, and we must accept its lack of power conpared with sql t-slq. Even so we always can find ways to gracefully overcome these non frequent issues.
for you question, what you want to do with a single line in access is just not possible, the easier you can do it is having two queries, one to group and one to get the totals; something like this
Query 1

SELECT Table1.Item, Sum(Table1.Qty) AS SumOfQty, Table1.box, First(Table1.box) AS FirstOfbox
FROM table1
GROUP BY Table1.Item, Table1.box;

Query 2
SELECT Query1.Item, Sum(Query1.SumOfQty) AS SumOfSumOfQty, Count(Query1.box) AS CountOfbox
FROM Query1
GROUP BY Query1.Item;

Good luck

Estuardo

 

[Edski]

One way...

1. create a query that groups BoxNbr, Where Item = "ABC" - (Qry1)
2. create a query that counts the number of records (boxes) produced from Qry1 - (Qry2)
3. create a query that sums QTY, Where Item = "ABC" - (Qry3)
4. create a query that includes the 3 fields from Qry2 and Qry3.

All 4 queries above are just select queries.