Limit number of records in report (Access 97)

Topic

New Reply

Is there any way that I can limit the number of records in a report group to a maximum number? I am using a sales file and I have the report grouped by item number and want to list in my report no more than the last 5 sales transactions for each item. Using the date range doesn’t work very well as some items may have been sold only three times in the past three years and others may have been sold 100 times in the past three years.
Any help would be greatly appreciated.

Bob Gott

Reply | Quote

Replies

Hi–

I haven’t actually done this myself, but there is a query property for TopValues. You can set the property in the design view of the query, then base your report on the query. Check it out in the help.

You can use the TopValues property to return a specified number of records or a percentage of records that meet the criteria you specify. For example, you might want to return the top 10 values or the top 25 percent of all values in a field.

Note The TopValues property applies only to append, make-table, and select queries.

Setting

The TopValues property setting is an Integer value representing the exact number of values to return or a number followed by a percent sign (%) representing the percentage of records to return. For example, to return the top 10 values, set the TopValues property to 10; to return the top 10 percent of values, set the TopValues property to 10%.

THere’s also an example in there.

Good luck!

Cecilia

Reply | Quote

I tried this but it only worked on the whole record set. I could not find anywhere where I could use this at the detail section level of a report.
Thank you very much for your quick respnse.

Bob Gott

Reply | Quote

This is one way – undoubtedly there are others:

Sort the report on transaction date – descending. In the Sorting and Grouping window, this must come below the Item number.

Put an unbound text box in the detail section.
Make it invisible.
Set the Control Source property to =1
Set the Running Sum property to Groups (don’t know the exact wording in English).
Name it Counter.

Now select the Detail section.
Create code for the OnFormat event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [Counter] > 5 Then Cancel = True
End Sub

The value of Counter will be increased for each record. Once it has passed 5, the detail section will be canceled. With each new Item number, Counter is reset to 1.

Reply | Quote

Thank you very much. This did exactly what I wanted and I was able to cut the report in half.
Again, thank you very much,

Bob Gott

Reply | Quote