Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-20-2018, 01:23 AM   #1
MushroomKing
Newly Registered User
 
MushroomKing's Avatar
 
Join Date: Jun 2018
Location: The Netherlands
Posts: 88
Thanks: 55
Thanked 1 Time in 1 Post
MushroomKing is on a distinguished road
Access Query, count column and merge unique values

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 is offline   Reply With Quote
Old 07-20-2018, 01:37 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,439
Thanks: 166
Thanked 1,738 Times in 1,707 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Access Query, count column and merge unique values

Instead of grouping on those fields, Sum them.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
MushroomKing (07-20-2018)
Old 07-20-2018, 02:34 AM   #3
MushroomKing
Newly Registered User
 
MushroomKing's Avatar
 
Join Date: Jun 2018
Location: The Netherlands
Posts: 88
Thanks: 55
Thanked 1 Time in 1 Post
MushroomKing is on a distinguished road
Re: Access Query, count column and merge unique values

Thanks minty,

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

MushroomKing is offline   Reply With Quote
Old 07-20-2018, 03:11 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,439
Thanks: 166
Thanked 1,738 Times in 1,707 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Access Query, count column and merge unique values

No , still group on that one, just sum the other fields
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
MushroomKing (07-20-2018)
Old 07-20-2018, 03:40 AM   #5
MushroomKing
Newly Registered User
 
MushroomKing's Avatar
 
Join Date: Jun 2018
Location: The Netherlands
Posts: 88
Thanks: 55
Thanked 1 Time in 1 Post
MushroomKing is on a distinguished road
Re: Access Query, count column and merge unique values

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
MushroomKing is offline   Reply With Quote
Old 07-20-2018, 03:55 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,439
Thanks: 166
Thanked 1,738 Times in 1,707 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Access Query, count column and merge unique values

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()
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
MushroomKing (07-20-2018)
Old 07-20-2018, 04:24 AM   #7
MushroomKing
Newly Registered User
 
MushroomKing's Avatar
 
Join Date: Jun 2018
Location: The Netherlands
Posts: 88
Thanks: 55
Thanked 1 Time in 1 Post
MushroomKing is on a distinguished road
Re: Access Query, count column and merge unique values

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

MushroomKing is offline   Reply With Quote
Old 07-20-2018, 06:03 AM   #8
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,439
Thanks: 166
Thanked 1,738 Times in 1,707 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Access Query, count column and merge unique values

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.

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Reply

Tags
count , merge , query , sum , unique value

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Total count of unique values in a query Soundje Modules & VBA 5 01-13-2015 10:06 AM
Count Unique Values Crosstab Query David_Isbell Queries 1 11-10-2010 08:18 PM
A Query For Counting Unique Values In A Column viraks Queries 9 05-31-2008 01:11 PM
Count Unique Records in a Query Column hkluther Reports 2 10-07-2004 06:47 PM
Count Unique records with yes/no values in Query JoysTick Queries 2 10-18-2001 01:34 AM




All times are GMT -8. The time now is 02:20 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World