Getting Undesired Counts in Query

[ErickaMeade]

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?

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];

 

[June7]

Provide raw data not just the query output. To provide db for analysis, follow instructions at bottom of my post.

 

[ErickaMeade]

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]

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]

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]

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

 

[ErickaMeade]

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!