How to select 5 random records in each group 2

[vatawna]

I'm using CR 9 and MS Access database. I use the following formula to select 5 random numbers based on the total count of records in each group:

WhilePrintingRecords;
numberVar array RandomArray := [1, 2, 3, 4, 5];
numberVar i;

For i := 1 To 5 Do (
RandomArray := Truncate (Rnd * Count ({someTable.someField}, {@StringToDate}, "daily"));
)


where Count ({someTable.someField}, {@StringToDate}, "daily") = the total number of records in a group, and each group is an appointment date.

Then I'm stuck here and don't know how to proceed. Does anyone how to apply any random formula to record selection to print out only 5 random records for each group?

Thanks.

 

[synapsevampire]

I would suggest doing this in a Query on the Access database though, not in Crystal.

Here's a FAQ on Displaying a Random Sample:

http://www.tek-tips.com/faqs.cfm?spid=767&sfid=3260&newpid=767

Now you could just suppress all rows after the first 5 for each group using the three formula method or a Running Total.

To do it in Crystal, you should be able to use something like the following to determine the number of rows per group to verify that you have at least 5:

count({table.field},{table.groupfield})

Hope this helps.

-k

 

[lbass]

You could use the random function as follows:

Insert your group on the date field and then create a formula {@random}:

rnd()

Place this in the details section and then add {@random} as the sort field (report->sort fields). Next create a running total {#cntwingrp} that counts the records within each group by using the running total editor: select {table.id} or some other recurring field, count, evaluate for each record, reset on change of group (date).

Then go to format->section (section expert)->details->suppress->x+2 and enter:

{#cntwingrp} > 5

The random function will assign a value to each record randomly, and when used as a sort field, this creates a randomly ordered list, so that the first five values will be a random selection from the entire group.

-LB

 

[lbass]

Sorry to be redundant--posted just after you, SV.

-LB

 

[synapsevampire]

Nae worries, LB, 90% of what I post feels redundant

-k

 

[vatawna]

Hi LB,

I'm sorry. It doesn't work. After I entered the formula to suppress the details section if the count > 5, nothing shows in the details section. Besides, if you place only rnd() in the details section and sort by that formula, how does it know what record to pick randomly. Maybe I'm missing something.

 

[lbass]

When you use the random function as a formula {@random} in the detail section:

rnd()

...it applies a random number between 0 and 1 to the records in the detail section. When you then sort by this formula, it is then creating a random order of records.

For this to work, you cannot use the count summary. Instead you need to use a running total, which I've called {#cntwingrp}, which counts the records within each group. The suppression formula in the section expert for the details needs to be:

{#cntwingrp} > 5

There is no reason I can think of that this wouldn't work.

-LB

 

[vatawna]

Sorry, LB. It works. I had it sorted by the appointment time, then by rnd(). And I couldn't figure out why it didn't work. It works fine without the appointment-time group.

Thanks.