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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


"KARL DEWEY" wrote:

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.
"SEdison" wrote:

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

--
SEdison
.

Relevant Pages

  • Re: Incorrect subtotals in a report
    ... You can create multiple values by combining a crosstab and cartesian query. ... > sales, ... > Subsection 01 subtotals for all of the above quantities. ... whilst the daily totals are incorrect. ...
    (microsoft.public.access.reports)
  • Re: a query to produce sales activity totals
    ... The query generates a row for each salesperson, ... Allen Browne - Microsoft MVP. ... of the sales process, including the number of sold and delivered vehicles. ... put these totals into a spread sheet that shows each salesperson's ...
    (microsoft.public.access.queries)
  • Re: Aggregate string concatenation efficiency problem
    ... Column5 -- it looks like you want to list multiple consultants ... for each customer - on the same record. ... Here is my pseudo code for your query ... search, in this case, in Sales, a VIN or Stock#, and what is returned ...
    (comp.databases.ms-access)
  • Re: Sql query to count how many sales between dates
    ... be less efficient than using a Totals query. ... table to the sales table in the query, as you should be, then set up a LEFT ... JOIN from the employee table to the sales table, ... StartDateControl with the correct name of the control where you enter the ...
    (comp.databases.ms-access)
  • Re: a query to produce sales activity totals
    ... In query design view, depress the Total button on the toolbar Access adds a Total row to the query design grid. ... number of contacts and the varous steps they have taken in the sales process. ... put these totals into a spread sheet that shows each salesperson's numbers... ...
    (microsoft.public.access.queries)