Top 75% of items

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with SKU sale by store and want to build a query that pulls
the skus that make up the top 75% of sales by store. I'm a fairly basic
Access user, most comfortable with the Query design tool, not so fluent in
coding in SQL text (or VB for that matter). Is there a straightforward way
to build this type of query?

With gratitude,
zt
 
For that you would need a subquery which really isn't handled all that well
in the QBE grid. You need to do it by a SQL statement. It would look
something like:

SELECT TblSales.Store, TblSales.[SKU]
FROM TblSales
WHERE TblSales.[SKU] In
(SELECT TOP 75% tblSales.[SKU]
FROM TblSales AS T2
WHERE T2.[SKU] =tblSales.[SKU]
ORDER BY T2.[SKU] DESC);
 
Thanks for the input. I think that what both responses indicate is a way to
get the top 75% of items, when in fact what I want is the items that make up
the top 75% of sales. That will end up being a varying number of items
depending on store.

Regardless, I think I've found some answers in the many posts on this board
regarding running sums. Using the dSum function and some language that,
frankly, I don't really understand, I've gotten it to work almost right. I
hope to figure it out perfectly today.

Thanks to all for the input and advice.

zt

Jerry Whittle said:
For that you would need a subquery which really isn't handled all that well
in the QBE grid. You need to do it by a SQL statement. It would look
something like:

SELECT TblSales.Store, TblSales.[SKU]
FROM TblSales
WHERE TblSales.[SKU] In
(SELECT TOP 75% tblSales.[SKU]
FROM TblSales AS T2
WHERE T2.[SKU] =tblSales.[SKU]
ORDER BY T2.[SKU] DESC);
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Jerry Whittle said:
Sure.

First you need to rack and stack the stores according to sales in descending
order in a query. You'll need to sort on a field or fields that define sales.

Then open up the query in SQL View and change

SELECT ...
to
SELECT TOP 75% .....
Click to expand...
Click to expand...
 
Back
Top