Excluding lowest numbers on a report

[jbelf]

Hi,

I'm trying to do several reports with graphs that count the amounts of records, grouped by type. For example, if there are 15 'types', I want to count the sales for the top 5 types, so I can create a meaningful graph (basically exclude the outliers).

I've successfully found the 'count' command. Is there any way I can have this list of 'counted' records, but exclude all types where the count is 5 or less (only show types where there are at least 6 records for that type).

Hope that's clear - any insight greatly appreciated!

 

[scriverb]

In a query you can select the TOP X records by using the TOP X predicate. Here is an example of selecting the top 5 sales records:

Select TOP 5 A.*
FROM tblSales as A
Order by A.YTD_Sales Desc;

By sorting YTD_Sales in descending order then the greatest 5 YTD sales record will be selected.

Post back if you have any questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[jbelf]

Thanks for the resonse. How would I translate that to simply count only the top 5? My current line states this:
=Count([end date]), which counts all records in the query where an end date is filled out.

any thoughts? many thanks!

 

[scriverb]

A count of the TOP 5 is going to be 5. I guess I don't understand what you are looking for here. Count function counts the number of rows returned in a query. If the Top 5 predicate is used then only 5 rows will be returned.

Please explain what you mean.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[GoDawgs]

I think all of this needs to be done in the query that's the recordset for the report. If I'm not confused (big if) then you need something like this:

Create a summary query with the Type, Amount, and Type again...with the first Type you need to select "Group By", Amount you need to select "Sum", and the second Type select "Count"...this will give you the total number of records and total amount for each type. I think you only want types where they have at least 5 records (I was confused here), that means you just need to put >=5 in the criteria for the Count column.

Once this query is set up you need to set it as the report recordset. Hope this helps...I may be way off.

Kevin

 

[jbelf]

That's it - thanks!