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

MushroomKing

Registered User.
Local time
Today, 08:57
Joined
Jun 7, 2018
Messages
100
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.
 

Minty

AWF VIP
Local time
Today, 15:57
Joined
Jul 26, 2013
Messages
10,354
Instead of grouping on those fields, Sum them.
 

MushroomKing

Registered User.
Local time
Today, 08:57
Joined
Jun 7, 2018
Messages
100
Thanks minty,

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

Minty

AWF VIP
Local time
Today, 15:57
Joined
Jul 26, 2013
Messages
10,354
No , still group on that one, just sum the other fields
 

MushroomKing

Registered User.
Local time
Today, 08:57
Joined
Jun 7, 2018
Messages
100
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
Local time
Today, 15:57
Joined
Jul 26, 2013
Messages
10,354
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

Registered User.
Local time
Today, 08:57
Joined
Jun 7, 2018
Messages
100
Aaah i understand. Ofcourse! Thank you for sticking it out. Cheers!
 

Minty

AWF VIP
Local time
Today, 15:57
Joined
Jul 26, 2013
Messages
10,354
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

Top Bottom