CODEWITHSUNDEEP
sqlms-access
Michael Harvey
Michael Harvey

Reputation: 148

SQL query doesn't return what I expected

First and foremost, thank you so much for taking the time to look at my SQL issue. I'm building a query MS Access and I'm pulling my hair out because I can't figure out what the heck is wrong with my query!

SELECT First(StoreNum) AS FirstOfStoreNum, 
       NewItem, ItemNum, Wrin, TotalUnits, Avg(UnitAmt) AS AvgOfUnitAmt 
FROM qryPhyInvGTZeroWithUnitPrice 
GROUP BY NewItem, ItemNum, Wrin, TotalUnits;

All I want from this stupid query is the average unit amount of all like NewItems. Please gurus! I need your help. What am I missing?

This is an excerpt of what is being returned when I run this query. There should be 20 stores for this [NewItem], but there are only 19 being returned.

enter image description here

I hope this helps better explain my question.

UPDATE!!! I needed just step away from it. I re-read the answers and you guys were right (of course). Here's the updated code.

SELECT StoreNum, NewItem, ItemNum, Wrin, Avg(UnitAmt) AS AvgOfUnitAmt
FROM qryPhyInvGTZeroWithUnitPrice
GROUP BY StoreNum, NewItem, ItemNum, Wrin;

I also got rid of the total units, because it wasn't beneficial to this project. The query works like it should after implementing your tips! Thanks again!

Upvotes: 0

Views: 69

Answers (2)

Eng. Samer T
Eng. Samer T

Reputation: 6526

try this:

SELECT First(StoreNum) AS FirstOfStoreNum, 
       NewItem, ItemNum, Wrin, TotalUnits, Avg(UnitAmt) AS AvgOfUnitAmt 
FROM qryPhyInvGTZeroWithUnitPrice 
GROUP BY StoreNum, NewItem, ItemNum, Wrin, TotalUnits;

Upvotes: 1

CaptainBli
CaptainBli

Reputation: 4201

I think what you need to add is the qryPhyInvGTZeroWithUnitPrice.StoreNum to your Group By and it should work. Calling an aggregate function like AVG with either another aggregate function or group by will cause a failed query.

Edit: The data you are getting back appears to be that of items and not StoreNums. I would say you have 19 different combinations of NewItem, ItemNum, Wrin, TotalUnits in the one StoreNum.

Upvotes: 2

Related Questions