TopValues

[Cmacvp16]

How do I query monthly values and return the Top 5 values in each month.

I am trying to list the Top 5 values in each month. Instead I am getting the top 5 values overall (globally) verses the Top 5 for Jan, the Top 5 for Feb...

Do I need to run 12 seperate queries for each month of the year?

 

[Parra]

To get this I would click on the sum icon on the tool bar "the thing that looks like an E". Where the month is choose Group by and I am sure you already have the criteria for the top 5.

 

[Russell Hauf]

No, you don't. It is a rather lengthy explanation though, so could you tell me if you have a field with the month in it, or if you are getting the month from a date field? Also, if you want to provide the names of your month and date fields, as well as your table name then I will use them in my example.

-rh

 

[Parra]

Russell, I see what you are getting at.

Sometimes I forget that not everyone has their data like I do. In most of my databases I have a date table, and one of they fields is month. I use this for grouping purposes. But if he does not I can see the delima with my response.

What would you do if is a short date? Create a calculated field and do a Len Left thing to seperate the month and group this.

 

[Cmacvp16]

Yes I have a date (mm/dd/yyy) field in a Customers Table. I used a query to group the daily values into monthly totals.

I need to retrieve the Top 5 customers per month not the Top 5 custormers per year.

 

[Russell Hauf]

Well, here's one way.

You'll need to actually make a table with your monthly values (just make the query where you group the daily values into monthly totals a Make-Table Query). For this example, I'll assume the following about your new table (change to your names as appropriate):

Table Name: tblMonthlyTotals
Month Field: fldMonth
Value Field: fldValues
Customer Field: fldCustomer

Ok, now make a new query based on the Monthly Totals table you created. Add the fields you want in the query, and sort by Month (either ascending or descending - whatever your preference is) and then by fldValues -descending.

Now, right-click on the table in the query design view and select Properties. Under Alias, type X.

Now add a field in your query something like this:

Rank: (Select Count(*) from tblMonthlyTotals Where ([fldMonth]=X.[fldMonth]) and ([fldValues]> X.[fldValues]))+1

And in the criteria for this field, put <= 5.

This should give you the top 5 customers by month.

HTH,

Russell