Query Displaying Standard Deviations

[neo651]

Alright, so because I'm a little OCD and an insufferable penny pincher I have a database for tracking groceries and such. To keep this short, each entry has a Grocery Type and a Grocery subtype. I want to create a query that displays, for every combination of grocery type and subtype, the standard deviation of all prices paid per unit. So, for example, in the query grocery type: beef, subtype: Ground would display the standard deviation of $/lb for all the entries where I have prices for that.

If it's unclear let me know...I'm having trouble describing it myself.

[ItsMe]

If it's unclear let me know...I'm having trouble describing it myself.

I think you have done a good job. The way I understand it, you are a penny pinching miser who likes beef but will purchase ground beef because it is cheapest. I can relate to that.

So, what is the question? Do you have anything started with your DB yet?

[Dal Jeanis]

I don't see a standard deviation aggregate function here http://www.techonthenet.com/access/functions/. Too bad, that would have made it simple.

However, I do see a DStDev function here http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

That's about the same thing, with a slightly different syntax. Here's a very rough aircode example -

SELECT
   MyQuery.MyType,
   MyQuery.MySubType,
   Avg(MyQuery.UnitPrice) As MyAvgPrice,
   DStDev("[UnitPrice]", "[MyQuery]","[MyType] = " & MyQuery.MyType & " AND [MySubType] = " & MyQuery.MySubType) As MyStDev 
FROM MyQuery
GROUP BY 
   MyQuery.MyType,
   MyQuery.MySubType;