Unshown fields are affecting sum in query

[cricketbird]

I'm having trouble with a query criteria causing SUM fields to not add up as I wish. Basically, I need to run criteria on a field, but I don't show that field in the resulting data table. I want it to ignore that field (NOTES) when summing the prices.

Test Table:
NAME CATEGORY PRICE NOTES
Item 1 A $5.00 Older version.
Item 2 B $3.00
Item 3 B $4.00 New model out in 2017
Item 4 B $8.00 Newer version available.

Query
SELECT Test.Category, Sum(Test.ItemPrice) AS SumOfItemPrice
FROM Test
GROUP BY Test.Category, Test.ItemNotes
HAVING (((Test.ItemNotes) Like "*new*"));


Result (not what I want - does not add up all of category B):
Category SumOfItemPrice
B $4.00
B $8.00

Result (what I want. Sum of category B where "new" is in the notes)
Category SumOfItemPrice
B $12

How can I get it to sum up category B using a text criteria to filter the results, but not account for the text criteria when summing?
Thanks!
CB

 

[plog]

GROUP BY Test.Category, Test.ItemNotes

Every clause is important and distinct. If you don't want to group by a field, it shouldn't be in the GROUP BY clause.

 

[plog]

Also, while it will work and I am sure you used the Access query builder to generate that SQL--your HAVING clause should instead be WHERE.

WHERE is criteria on an individual field, HAVING is criteria on aggregate data (like if you wanted SummOfItemPrice over a certain threshold).

 

[cricketbird]

Thanks! When you use the query designer, it isn't obvious that you have to add a column to force it create a WHERE vs. a HAVING statement. This page helped explain the difference to me, since it isn't always obvious.
Also, I'm so used to it giving me an error about the "specified expression not being part of an aggregate function" that I let it lull me into always just grouping by everything I wasn't summing.

With your prods, I was able to get the query to:

SELECT Test.Category, Sum(Test.ItemPrice) AS SumOfItemPrice
FROM Test
WHERE (((Test.ItemNotes) Like "*new*"))
GROUP BY Test.Category;