Top Ten - Top Ten 1

[Freckles]

Selecting the basic top ten is not the problem. However, I need to select the top ten customers by revenue, then the largest ten orders for each of those customers. I know that I could do it with 11 queries then joined with a union query. BUT, the data can change monthly and would not like to have to find the top ten customers, then manually enter each one into a query to get the orders for each customer.

If there is not a way to do this, then I plan on limiting the number of lines on each page of my report. Sloppy, but I sure hope doable!

: Deb Koplen
deb.koplen@verizon.com
koplend@swbell.net (weekends and nights)

A person can stand almost anything except a succession of ordinary days.

 

[Freckles]

MORE. . .

Because I have over a million records to process, I am trying a two step process.

FIRST: A query that list the top ten customers.
Second: Based on First query, a table that lists all of the orders for the Top Ten customers with the data required.

NOW, using the Table as a base, is there a query to get the top ten orders for each customer other than limiting the number of orders in my report?

: Deb Koplen
deb.koplen@verizon.com
koplend@swbell.net (weekends and nights)

A person can stand almost anything except a succession of ordinary days.

 

[amorous]

This gives you top ten costumers with in a revenue:

SELECT RevenueID,RevenueName, CustomerName,OrderAmount
FROM yourtablename t
WHERE OrderAmount in (
select TOP 10 OrderAmount
from yourtablename
where RevenueName = t.RevenueName
order by OrderAmount desc)

Try in the same lines and use your field names
-VJ

 

[Freckles]

I am trying, but do you mean top 10 revenues for each customer?

: Deb Koplen
deb.koplen@verizon.com
koplend@swbell.net (weekends and nights)

A person can stand almost anything except a succession of ordinary days.

 

[amorous]

Sorry try this to get top ten revenues per customer:

SELECT CustomerID,CustomerName,Revenue
FROM yourtablename t
WHERE Revenue in (
select TOP 10 Revenue
from yourtablename
where CustomerName = t.CustomerName
order by Revenue desc)

OR you can also try something like this:

SELECT CustomerID,CustomerName,Revenue
FROM yourtablename t
WHERE Revenue in (
select TOP 10 Revenue
from yourtablename
where CustomerID = t.CustomerID
order by Revenue desc)

Hope that helps..

-VJ

 

[Freckles]

I had sorta stumbled into your second version and came up with
SELECT DnbGlobalultname, billing_system, customer_name, account_status, [CU MO Current], [CUR MO 91+], [CUR MO TOTAL]
FROM [DATA_Top Ten with charges] AS t
WHERE ((([CUR MO 91+]) In (select TOP 10 [CUR MO 91+]
from [DATA_Top Ten with charges]
where DnbGlobalultname = t.DnbGlobalultname
order by [CUR MO 91+]DESC)));


BUT IT IS TAKING FOREVER. What am I doing wrong? I am now working on your first suggestion

: Deb Koplen
deb.koplen@verizon.com
koplend@swbell.net (weekends and nights)

A person can stand almost anything except a succession of ordinary days.

 

[amorous]

SELECT DnbGlobalultname, billing_system, customer_name, account_status, [CU MO Current], [CUR MO 91+], [CUR MO TOTAL]
FROM [DATA_Top Ten with charges] AS t
WHERE [CUR MO 91+] In (select TOP 10 [CUR MO 91+]
from [DATA_Top Ten with charges]
where DnbGlobalultname = t.DnbGlobalultname
order by [CUR MO 91+] DESC);

Remove all the parenthesis...and u need a space before the word DESC

-VJ

 

[Freckles]

Brillant! AnD so simple

Thanks so very much for working with me on this!!!!!

: Deb Koplen
deb.koplen@verizon.com
koplend@swbell.net (weekends and nights)

A person can stand almost anything except a succession of ordinary days.

 

[amorous]

I am glad that i could be of help..

thanks for the star

-VJ

 

[icsupt]

I am trying to do this same thing. I'm trying to get the top ten customers by Region by Totalamt.

My field names are:

TotalAmt (this is a calculated field that adds all amounts up)
qryRegion (this is the name of the query that I want to get just the top ten)
tblCust (this is what the qryRegion is based off of.

Can anyone help me do this?

Thanks in advance.

 

[PHV]

And what have you so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[icsupt]

I just have a query with all the fields in it. I don't know how to do anything else so I have to start at step 1.

Sorry and thanks.

 

[icsupt]

Here's my statement. I'm trying to get the Top 10 Totalall from each Region.

Totalall is a calculation of several fields. So for each region, I want the top 10 amounts. There are 4 regions, so I should have 40 amounts.

SELECT [Top 10].District, [Top 10].[Repetitivexx]*12+[Repetitive2]*12+[Repetitive3]*12+[Repetitive4]*12+[Repetitive5]*12+[One Timexx]+[One Time2]+[One Time3]+[One Time4]+[One Time5] AS Totalall,[Top 10].DateTransitioned, [Top 10].Term, [Top 10].[One Timexx], [Top 10].Repetitivexx, [Top 10].[One Time2], [Top 10].Repetitive2, [Top 10].[One Time3], [Top 10].Repetitive3, [Top 10].[One Time4], [Top 10].Repetitive4, [Top 10].[One Time5], [Top 10].Repetitive5, [Top 10].RIM
FROM [Top 10]
WHERE ((([Top 10].DateTransitioned) Is Null) AND (([Top 10].Term)=False))
ORDER BY [Top 10].Region;