Access Query, count column and merge unique values (1 Viewer)

MushroomKing

Registered User
Joined
Jun 7, 2018
Messages
93
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

querymergeprint.png

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

MushroomKing

Registered User
Joined
Jun 7, 2018
Messages
93
Thanks minty,

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

Minty

AWF VIP
Joined
Jul 26, 2013
Messages
6,539
No , still group on that one, just sum the other fields
 

MushroomKing

Registered User
Joined
Jun 7, 2018
Messages
93
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.

beforeafter.png
 

Minty

AWF VIP
Joined
Jul 26, 2013
Messages
6,539
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()
 

Minty

AWF VIP
Joined
Jul 26, 2013
Messages
6,539
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.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom