Access Query: SELECT Unique Avg > Avg across all items

[i.am.sophie]

Hi All,

I’m working on a database that will show us when and possibly why we had stock issues in store.

I have a form, where the user can:
1. Look up certain stores and see how often they run out of stock on average.
(Number of instances when they ran out / Total number of days)
2. List all stores which run out frequently
(i.e. Store’s average when they run out > Average across all stores)

I don’t have problems with the first one, I could figure out how to do that, but I am having problems with the second. It seems like I would need to include two SELECT statements into the WHERE clause, which doesn’t seem to work.

The code I have for the specific store average:

SELECT 
DISTINCT SOHData.StoreName,
FORMAT((COUNT(IIF([SOHData.StoreName]=[Forms]![Search]![StoreName] AND
	[SOHData.Ran out]= "X",1,Null))) /
(COUNT(IIF([SOHData.StoreName]=[Forms]![Search]![StoreName],1,Null))),
"0.0%") AS StoreAvg,
COUNT(IIF([SOHData.StoreName]=[Forms]![Search]![StoreName],1,Null)) AS Total,
COUNT(IIF([SOHData.StoreName]=[Forms]![Search]![StoreName] AND
	[SOHData.Ran out]= "X",1,Null)) AS RanOut

FROM SOHData
WHERE (SOHData.StoreName = [Forms]![Search]![StoreName])
GROUP BY SOHData.StoreName;

So *The number of instances when there is an X in the ran out column AND the store name equals the one on the search form* >> That’s how many times the store ran out.
*The number of instances it finds the store name within the raw data* >> This the total number of days for the store.
Then, to get the average, it divides the first one with the second.
This works perfectly.

I was trying to get my head around how to do the second user option, to list all stores which run out frequently.

To me, it looks like it needs to be something like this:

SELECT 
SOHData.StoreName, 
Count(*) AS Instances, 
Count(IIF([SOHData.Ran out]="X",1,Null)) AS RanOut,
FORMAT((Count(IIF([SOHData.Ran out]="X",1,Null))) / (Count(*)),"0.0%") 
AS AvgPercent,

FROM SOHData
WHERE 
AvgPercent > (SELECT ((COUNT(IIF([SOHData.Ran out]="X",1,Null)) / (COUNT(*)))
AS TotalAvg FROM SOHData;)

The problem is with the AvgPercent bit. Obviously, I can’t just use an alias because it won’t recognise it.

I tried putting the formula in I used to get AvgPercent

FORMAT((Count(IIF([SOHData.Ran out]="X",1,Null))) / (Count(*)),"0.0%")

but I get an error message saying an aggregate function cannot be part of the WHERE clause.

I also tried putting it into a SELECT statement, so eventually I’d have a..

WHERE
(SELECT …) > (SELECT…)

.. but this doesn’t work either. I’m not even sure if it’s supposed to work or not, but my search doesn’t return any results, so it’s clearly not working for me.

Anyone could help with this, please?
(Sorry about the long post.)

 

[MarkK]

I didn't read your whole post in detail, but you can replace this . . .

Count(IIF([SOHData.Ran out]="X",1,Null))

. . . with this . . .

-Sum([SOHData.Ran out]="X")

. . . because this expression: [SOHData.Ran out]="X" returns a zero (0) or a minus one (-1), so if you Sum() it and negate it, you get a count.

Then, keep in mind that you can write a query, save it, and then use it as an input table in a second query, so base a query on a query. I think you are trying to do too much in a single query. Return basic results first, then use those result in a second query, and so on.

Hope this helps,