Query: Combination of Group By, Top 10, Sorting...

[Guest]

I'm trying to make a new query that will end up in a report grouping/sorting
sales information...

I have a query:
**q_SalesHistory, with the relavent fields: Customer, ProdID, SalesRevAll...
**q_SalesHistory is line-by-line sales, so grouping is neccesary on both
Customer and ProdID to get the proper totals, or if neccesary I could create
a second query that groups these items together no problem.

I'd like to produce a report showing the following:
**All Customers sorted in descending order of their total SalesRevAll
**Within each customer, the top 5 ProdIDs purchased based on a descending
sort of SalesRevAll, and an 6th row for "all other"

Customer A with Most Sales
ProdID1 1st Most Purchased by Customer A
ProdID2 2nd Most Purchased by Customer A
ProdID3 3rtd Most Purchased by Customer A
ALL OTHER ProdIDs SalesRevALL TOTAL
Customer B with 2nd Highest Total Sales
Prod 1...
etc...

These links touch on these different issues, but I haven't been able to put
it all together into one functioning query...
http://www.craigsmullins.com/ssu_0900.htm
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/rch7rptslnpatternrecipes.mspx
http://support.microsoft.com/kb/q153747/

Any help would be very appreciated! Thanks

 

[Michel Walsh]

Hi,


rank the data, a little bit like this:


SELECT a.ClientID, a.Item, Last(a.Amount) As theAmount, COUNT(*) as rank
FROM myTable As a INNER JOIN myTable As b
ON a.clientID=b.ClientID AND a.item = b.item AND (a.amountSold <
b.amountSold OR ( a.amountSold = b.amountSold and a.pk <= b.pk))
GROUP BY a.ClientID, a.Item


or use your favorite expression to rank, by client, by item, accordingly to
the AmountSold. pk is to be replaced by the primary key field name (used,
here, to break any equality in position 5).

Have that query saved under the name, say q1. Then:

SELECT ClientID, iif( rank <5, "" & rank, "6+" ), Sum(theAmount),
FIRST(item)
FROM q1
GROUP BY ClientID, iif( rank <5, "" & rank, "6+" )


The job is done by the GROUP BY clause, which can use an expression. Here,
if rank <= 5, the rank value will make its own group; only one record will
be part of the group, and SUM(theAmount) will sum the value of just one
record, same for FIRST; on the other hand, if rank >5, the record will be
part of a catch all group, called "6+" and the SUM will occur on all those
record having fallen in the catch-all bucket (for a given clientID). An item
from this group will also be supplied, but it can be any item from this
catch-all group. You can also try something like: iif( MAX(rank) < 5,
MAX(item), Null) in place of FIRST(item), which is untested by should
supply the item if rank <=5, and a NULL in the catch-all case.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Here another way. The first query sums the sales by customer and product.

q_SalesHistory_Sum ---
SELECT q_SalesHistory.Customer, q_SalesHistory.ProdID,
Sum(q_SalesHistory.SalesRevAll) AS SumOfSalesRevAll
FROM q_SalesHistory
GROUP BY q_SalesHistory.Customer, q_SalesHistory.ProdID
ORDER BY q_SalesHistory.Customer, Sum(q_SalesHistory.SalesRevAll) DESC;

This query selects the top 5 products for each customer. If product 5 and 6
sales are equal it will only display 4.
q_SalesHistory_TOP5_Cust-Prod ---
SELECT T.Customer, T.ProdID, T.SumOfSalesRevAll
FROM q_SalesHistory_Sum AS T
WHERE ((((SELECT COUNT(*)
FROM [q_SalesHistory_Sum] T1
WHERE T1.Customer = T.Customer
AND T1.SumOfSalesRevAll >= T.SumOfSalesRevAll))<=5))
ORDER BY T.Customer, T.SumOfSalesRevAll DESC;

This query totals all sales not included in the customer top 5.
q_SalesHistory_Other_Sales ---
SELECT q_SalesHistory.Customer, Sum(q_SalesHistory.SalesRevAll) AS [TOTAL
all other product sales]
FROM q_SalesHistory LEFT JOIN [q_SalesHistory_TOP5_Cust-Prod] ON
(q_SalesHistory.Customer = [q_SalesHistory_TOP5_Cust-Prod].Customer) AND
(q_SalesHistory.ProdID = [q_SalesHistory_TOP5_Cust-Prod].ProdID)
WHERE ((([q_SalesHistory_TOP5_Cust-Prod].Customer) Is Null) AND
(([q_SalesHistory_TOP5_Cust-Prod].ProdID) Is Null))
GROUP BY q_SalesHistory.Customer;

This query has the data for your report.
q_SalesHistory_for_report ---
SELECT q_SalesHistory_Total_sales.Customer,
[q_SalesHistory_TOP5_Cust-Prod].ProdID,
[q_SalesHistory_TOP5_Cust-Prod].SumOfSalesRevAll,
q_SalesHistory_Other_Sales.[TOTAL all other product sales]
FROM (q_SalesHistory_Total_sales LEFT JOIN [q_SalesHistory_TOP5_Cust-Prod]
ON q_SalesHistory_Total_sales.Customer =
[q_SalesHistory_TOP5_Cust-Prod].Customer) LEFT JOIN
q_SalesHistory_Other_Sales ON q_SalesHistory_Total_sales.Customer =
q_SalesHistory_Other_Sales.Customer;

Place the [TOTAL all other product sales] field below the other field and
set Hide Duplicate property to YES. Also you might want to hide customer
duplicates.

 

[Guest]

Karl,
Thanks for your easy-to-implement reply.

This method works but is tremendously slow -- over an hour (I'm doing this
on ~15k records). To speed the process, I changed the SalesHistory_SUM query
to a make-table query and referenced the table from then on --this still
takes has performance issues, taking a good ten minutes or so.

I'm curious if you have any tips or if Access' pivot-functionality can
produce any of this any speedier.

Thanks Again.



--
SEdison

Here another way. The first query sums the sales by customer and product.

q_SalesHistory_Sum ---
SELECT q_SalesHistory.Customer, q_SalesHistory.ProdID,
Sum(q_SalesHistory.SalesRevAll) AS SumOfSalesRevAll
FROM q_SalesHistory
GROUP BY q_SalesHistory.Customer, q_SalesHistory.ProdID
ORDER BY q_SalesHistory.Customer, Sum(q_SalesHistory.SalesRevAll) DESC;

This query selects the top 5 products for each customer. If product 5 and 6
sales are equal it will only display 4.
q_SalesHistory_TOP5_Cust-Prod ---
SELECT T.Customer, T.ProdID, T.SumOfSalesRevAll
FROM q_SalesHistory_Sum AS T
WHERE ((((SELECT COUNT(*)
FROM [q_SalesHistory_Sum] T1
WHERE T1.Customer = T.Customer
AND T1.SumOfSalesRevAll >= T.SumOfSalesRevAll))<=5))
ORDER BY T.Customer, T.SumOfSalesRevAll DESC;

This query totals all sales not included in the customer top 5.
q_SalesHistory_Other_Sales ---
SELECT q_SalesHistory.Customer, Sum(q_SalesHistory.SalesRevAll) AS [TOTAL
all other product sales]
FROM q_SalesHistory LEFT JOIN [q_SalesHistory_TOP5_Cust-Prod] ON
(q_SalesHistory.Customer = [q_SalesHistory_TOP5_Cust-Prod].Customer) AND
(q_SalesHistory.ProdID = [q_SalesHistory_TOP5_Cust-Prod].ProdID)
WHERE ((([q_SalesHistory_TOP5_Cust-Prod].Customer) Is Null) AND
(([q_SalesHistory_TOP5_Cust-Prod].ProdID) Is Null))
GROUP BY q_SalesHistory.Customer;

This query has the data for your report.
q_SalesHistory_for_report ---
SELECT q_SalesHistory_Total_sales.Customer,
[q_SalesHistory_TOP5_Cust-Prod].ProdID,
[q_SalesHistory_TOP5_Cust-Prod].SumOfSalesRevAll,
q_SalesHistory_Other_Sales.[TOTAL all other product sales]
FROM (q_SalesHistory_Total_sales LEFT JOIN [q_SalesHistory_TOP5_Cust-Prod]
ON q_SalesHistory_Total_sales.Customer =
[q_SalesHistory_TOP5_Cust-Prod].Customer) LEFT JOIN
q_SalesHistory_Other_Sales ON q_SalesHistory_Total_sales.Customer =
q_SalesHistory_Other_Sales.Customer;

Place the [TOTAL all other product sales] field below the other field and
set Hide Duplicate property to YES. Also you might want to hide customer
duplicates.

I'm trying to make a new query that will end up in a report grouping/sorting
sales information...

I have a query:
**q_SalesHistory, with the relavent fields: Customer, ProdID, SalesRevAll...
**q_SalesHistory is line-by-line sales, so grouping is neccesary on both
Customer and ProdID to get the proper totals, or if neccesary I could create
a second query that groups these items together no problem.

I'd like to produce a report showing the following:
**All Customers sorted in descending order of their total SalesRevAll
**Within each customer, the top 5 ProdIDs purchased based on a descending
sort of SalesRevAll, and an 6th row for "all other"

Customer A with Most Sales
ProdID1 1st Most Purchased by Customer A
ProdID2 2nd Most Purchased by Customer A
ProdID3 3rtd Most Purchased by Customer A
ALL OTHER ProdIDs SalesRevALL TOTAL
Customer B with 2nd Highest Total Sales
Prod 1...
etc...

These links touch on these different issues, but I haven't been able to put
it all together into one functioning query...
http://www.craigsmullins.com/ssu_0900.htm
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/rch7rptslnpatternrecipes.mspx
http://support.microsoft.com/kb/q153747/

Any help would be very appreciated! Thanks