G
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
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