Access Query, count column and merge unique values

[MushroomKing]

Hi guys!

I have a little question concerning my query.

I have a column (checklistID) with values i want to be unique (because im counting this in another query).

But i also want the boxes and pallets to add up (sum).

So in the example, my result should be:

PALLETS: 0 (empty)
BOXES: 28
checklistID: 765



Some assistance would be great. Cant seem to figure it out.

 

[Minty]

Instead of grouping on those fields, Sum them.

 

[MushroomKing]

Thanks minty,

There lies a problem. If i sum the checklistID field it just adds up all the numbers.

 

[Minty]

No , still group on that one, just sum the other fields

 

[MushroomKing]

Hi Minty Thanks.

Well, that ALMOST worked.

The data is really not correct.
I have 1 duplicate, so i should end up with 12 records instead of 13.
But i end up with 8???

Also when i count the pallets and the boxes, its 100 pieces off.

 

[Minty]

Remove the criteria(s) from the Sum, and move it to a separate Where clause.

At the moment you are using the criteria on the SUM() value. So in the SQL statement (If you look at the SQL window) you will see it is a HAVING clause which is applied after the SUM()

 

[MushroomKing]

Aaah i understand. Ofcourse! Thank you for sticking it out. Cheers!

 

[Minty]

The criteria in a grouped query if you are using the query designer can be a bit misleading, especially if you change a working select query to a grouped one.

The way you have seen it work (or not work!) will hopefully have given you a better understanding.