Getting Undesired Counts in Query (1 Viewer)

ErickaMeade

Registered User.
Local time
Today, 16:00
Joined
May 26, 2014
Messages
10
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];
 

Attachments

  • Q2B - Mstr Doors Query.xlsx
    76.4 KB · Views: 75

June7

AWF VIP
Local time
Today, 15:00
Joined
Mar 9, 2014
Messages
5,423
Provide raw data not just the query output. To provide db for analysis, follow instructions at bottom of my post.
 

ErickaMeade

Registered User.
Local time
Today, 16:00
Joined
May 26, 2014
Messages
10
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.
 

isladogs

MVP / VIP
Local time
Today, 23:00
Joined
Jan 14, 2017
Messages
18,186
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.

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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:00
Joined
Feb 19, 2002
Messages
42,976
Count(xxx) does not count the instances of xxx. It counts the number of rows in the domain where xxx is not null.

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
 

ErickaMeade

Registered User.
Local time
Today, 16:00
Joined
May 26, 2014
Messages
10
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
 

Attachments

  • Database10.accdb
    408 KB · Views: 74

ErickaMeade

Registered User.
Local time
Today, 16:00
Joined
May 26, 2014
Messages
10
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!
 

Users who are viewing this thread

Top Bottom