only print top 50 accounts per location

[sportsguy]

I have 140 sales locations in 10 regions and 3 operations.

I am printing net discounts by each location's customers,
in descending order of most discounts.

I want to stop at 50 for each location, as some locations have 10,
others have 500. . .

I have a running sum of 1 in the detail, so when that gets to 50,
how do i move to the next location and stop printing the remaining 450 for the big location?

thanks in advance.
sportsguy

nice new features in the forum, i haven't been here for awhile.

 

[pbaldy]

Is this what you're after?

http://support.microsoft.com/kb/153747/en-us

 

[sportsguy]

Here is a picture of the query, attached

I want the top 50 ASC order NetDiscounts by CUSTOMERDISCOUNTS.strDistricts NetDiscounts are negative sales

My SQL criteria is:

In (Select Top 50 [NetDiscounts] from CUSTOMERDISCOUNTS Where [strDistrict]=[CUSTOMERDISCOUNTS].[strDistrict] Order by [NETDiscounts] ASC)

I am only getting the top 50 customers, not the top 50 customers by strDistrict.

or do i write the select SQL for the AccountNbr ? the link was almost too simple. . .

thank you

sportsguy

 

[sportsguy]

I figured it out, though its kind of different than the example given.

the IN(query) selects the customer account numbers, based upon the top 30 ranking of the netdiscounts, without a join. Maybe beause I don't read query language in bullet form that I can't properly interpret the solution.

My trouble was with the output and the query design to get the output desired. The Miscrosoft web page did not have a picture of the query, and I don't have the northwind database.

thanks

sportsguy. . .

 

[pbaldy]

No problem.

Northwind should have been installed with Access. If not, I would imagine it's available from the MS website. If not, as the old TV show said, "we have ways...".