Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-02-2018, 12:22 PM   #1
ErickaMeade
Newly Registered User
 
Join Date: May 2014
Posts: 10
Thanks: 4
Thanked 2 Times in 1 Post
ErickaMeade is on a distinguished road
Getting Undesired Counts in Query

I have created a query to get a count of items based on "Pricing Category" and "Location" columns. If I leave the query to only include the location, amount, and pricing category then I get the desired count. However, I need to build another query off this, so I need to carry another column through for the next query to be accurate. When I add that data column then my counts get screwed up. Please see attached example of how I can get an accurate count, how it looks with the added column, and the desired output that I am looking for.

Is there a way to count the Pricing Category without any consideration of the Model column?


Code:
SELECT DISTINCT [Master Oracle].Location, [Master Oracle].Model, [Master Oracle].Amount, [Master Oracle].[Pricing Category], Count([Pricing Category] & [Location]) AS CntPCat
FROM [Master Oracle]
GROUP BY [Master Oracle].Location, [Master Oracle].Model, [Master Oracle].Amount, [Master Oracle].[Pricing Category];
Attached Files
File Type: xlsx Q2B - Mstr Doors Query.xlsx (76.4 KB, 23 views)

ErickaMeade is offline   Reply With Quote
Old 10-02-2018, 01:20 PM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,476
Thanks: 0
Thanked 577 Times in 573 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Getting Undesired Counts in Query

Provide raw data not just the query output. To provide db for analysis, follow instructions at bottom of my post.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is online now   Reply With Quote
Old 10-02-2018, 02:22 PM   #3
ErickaMeade
Newly Registered User
 
Join Date: May 2014
Posts: 10
Thanks: 4
Thanked 2 Times in 1 Post
ErickaMeade is on a distinguished road
Re: Getting Undesired Counts in Query

Quote:
Originally Posted by June7 View Post
Provide raw data not just the query output. To provide db for analysis, follow instructions at bottom of my post.
That's a lot of high;y confidential front end and back end data to delete and there is a lot going on in that db to send the whole thing stripped of data. There are many other tables and many other queries. This is a pretty complex db and I am trying to get help on a single piece of the puzzle. If I can solve this, than it will solve my final problem and this db will do what I need it to do.

The source table is actually a singe query. This is not a join query or a complicated query. I provided the results to help understand what I am getting and what I am aiming for.

The source table is set up

Location
Account Number
Update
Name
Description
Pricing Category
Model
Grouping

The query I am trying to build needs to have location Pricing Category, Model, Amount, & the count of each price category per customer & Amount. The problem I am having is that I get the right count if I do not include the model column but if I add the model column then it includes that too and suddenly every row has the answer as 1. I am trying to figure out a way to include the model column while ignoring the column for the counting function.

It really boils down to am I able to get the count function to ignore a column and count the way it should.

ErickaMeade is offline   Reply With Quote
Old 10-02-2018, 03:59 PM   #4
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,369
Thanks: 115
Thanked 3,111 Times in 2,829 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Getting Undesired Counts in Query

If you can upload an Excel file then you can upload a copy of your database containing just the table & related query.
We don't need to see anything else.
If you need to remove/edit confidential data first then spend the time doing so.
Otherwise we can't help you with the information provided.

Quote:
It really boils down to am I able to get the count function to ignore a column and count the way it should.
All we can do at the moment is make guesses which is a waste of your time and ours.

Look forward to seeing the info we need to provide assistance
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
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.


Website links:
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.
,
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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 10-02-2018, 09:31 PM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Getting Undesired Counts in Query

Count(xxx) does not count the instances of xxx. It counts the number of rows in the domain where xxx is not null.

Quote:
Is there a way to count the Pricing Category without any consideration of the Model column?
1. Remove Distinct
2. Change the Count(xxx) to Count(*).
3. Remove Model from the Select and Group by clauses
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
ErickaMeade (10-03-2018)
Old 10-03-2018, 05:46 AM   #6
ErickaMeade
Newly Registered User
 
Join Date: May 2014
Posts: 10
Thanks: 4
Thanked 2 Times in 1 Post
ErickaMeade is on a distinguished road
Re: Getting Undesired Counts in Query

Quote:
Originally Posted by Pat Hartman View Post
Count(xxx) does not count the instances of xxx. It counts the number of rows in the domain where xxx is not null.


1. Remove Distinct
2. Change the Count(xxx) to Count(*).
3. Remove Model from the Select and Group by clauses
Thank you for your suggestion. If I remove model from the query then the following queries that use this one as part of the join query does not give me the results I need. This is my conundrum. I need the model in the there but it needs to not be considered in the count function.

I did attach a sample database per previous request. I had to make a sample one to ensure that I did not pass on any confidential data from the back end that I may have missed. I created one using the sample data I provided on the spreadsheet with a query using the sql statement I provided in my original question.

I cannot figure on how to get the model column to remain in the query while getting the desired count of the price category column by location
Attached Files
File Type: accdb Database10.accdb (408.0 KB, 22 views)
ErickaMeade is offline   Reply With Quote
Old 10-03-2018, 07:23 AM   #7
ErickaMeade
Newly Registered User
 
Join Date: May 2014
Posts: 10
Thanks: 4
Thanked 2 Times in 1 Post
ErickaMeade is on a distinguished road
Re: Getting Undesired Counts in Query

Figured it out. I had to do the original query without the models per Pat Hartman's suggestion. Then I had to run a secondary query that added in the models. The secondary query gave me the results I needed.

Thanks!


ErickaMeade is offline   Reply With Quote
Reply

Tags
count , query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Record makes undesired automatic change johansena Tables 10 03-06-2014 07:12 AM
undesired result nur annie Modules & VBA 5 10-13-2012 12:59 AM
Undesired and misterious double insertion PepeGallo Forms 1 05-08-2008 01:01 AM
A query that counts and more?? ftblstr2319 Queries 1 10-15-2007 10:14 AM
Undesired Rounding... kdm3 General 3 08-27-2004 03:36 AM




All times are GMT -8. The time now is 07:47 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