Access Report but now show only random records

[rfuscjr via AccessMonster.com]

I inhereted an MsAccess Report. It shows data by group. The user now wants
(for auditing purposes) a random sampling (20% of records) from each group.

So, if group 1 had 10 rows on the original Access report, he wants to see the
same report but only showing 2 of those records, selected at random. I
should do this for all groups on the original report.

Any ideas on how to proceed with something like this?

 

[Marshall Barton]

I inhereted an MsAccess Report. It shows data by group. The user now wants
(for auditing purposes) a random sampling (20% of records) from each group.

So, if group 1 had 10 rows on the original Access report, he wants to see the
same report but only showing 2 of those records, selected at random. I
should do this for all groups on the original report.

You can select random records by using a record source query
something like:

SELECT TOP 20 PERCENT *
FROM table
ORDER BY Rnd(xxx)

Where xxx is any field in the table that is guaranteed to
contain a positive number value (e.g. an autonumber field).

You will also have to make sure that you run some code
somwhere (e.g. in the code that opens the report) that uses
the Randomize statement.

 

[rfuscjr via AccessMonster.com]

Close! This code yeilds 50 randam records in my first group. I need 50
random records PER group. TO be honest I was hoping to get 20% random
records from each group.

Thanks!


SELECT top 50 * from tblTEST
ORDER BY Group , RND(IdxMrn);

 

[rfuscjr via AccessMonster.com]

closer yet...this gets me 20% random records for ONE group...I need it to
pull for 20% for each group in the table...

SELECT top 20 percent * from tblTEST where BA_NUM = 203
ORDER BY (BA_NUM) , RND(IdxMrn)
;

Close! This code yeilds 50 randam records in my first group. I need 50
random records PER group. TO be honest I was hoping to get 20% random
records from each group.

Thanks!

SELECT top 50 * from tblTEST
ORDER BY Group , RND(IdxMrn);
[quoted text clipped - 16 lines]

somwhere (e.g. in the code that opens the report) that uses
the Randomize statement.

 

[Marshall Barton]

closer yet...this gets me 20% random records for ONE group...I need it to
pull for 20% for each group in the table...

SELECT top 20 percent * from tblTEST where BA_NUM = 203
ORDER BY (BA_NUM) , RND(IdxMrn)

Sorry, I missed the "for each group" part of the problem.
You can use a subquery to get that kind of filter:

SELECT *
FROM tblTEST
WHERE IdxMrn IN (SELECT TOP 20 PERCENT X.IdxMrn
FROM tblTEST As X
WHERE X.BA_NUM = tblTest.BA_NUM
ORDER BY RND(IdxMrn) )
ORDER BY BA_NUM

Because reports are sorted by what's specified in Sorting
and Grouping, you should remove the ORDER BY BA_NUM line
after you are done testing the query.
--
Marsh
MVP [MS Access]

Please use Copy/Paste when posting code or SQL so you don't
waste time retyping and potentially introducing typos.

 

[rfuscjr via AccessMonster.com]

Thank you sir...I will try this.

Sorry, I missed the "for each group" part of the problem.
You can use a subquery to get that kind of filter:

SELECT *
FROM tblTEST
WHERE IdxMrn IN (SELECT TOP 20 PERCENT X.IdxMrn
FROM tblTEST As X
WHERE X.BA_NUM = tblTest.BA_NUM
ORDER BY RND(IdxMrn) )
ORDER BY BA_NUM

Because reports are sorted by what's specified in Sorting
and Grouping, you should remove the ORDER BY BA_NUM line
after you are done testing the query.

 

[rfuscjr via AccessMonster.com]

SELECT *
FROM tblTEST
WHERE IdxMrn IN (SELECT TOP 20 PERCENT X.IdxMrn
FROM tblTEST As X
WHERE X.BA_NUM = tblTest.BA_NUM
ORDER BY RND(IdxMrn) )

I am not really sure what this did. tblTest has 4892 records. I would
expect the result to yield about 978 total records proportionally broken up
by BA_NUM. Instead I get 1332 total which is closer to 27%. Note that I get
too many records per BA_NUM. Here is an example of what I am after if I
wanted, say the top 10% of each BA_NUM:

tblTest:
MRN BA_NUM
1 33
2 33
3 33
4 33
5 33
6 33
7 33
8 33
9 33
10 33
11 533
12 533
13 533
14 533
15 533
16 533
17 533
18 533
19 533
20 533
21 533
22 533
23 533
24 533
25 533
26 533
27 533
28 533
29 533
30 533

TblRandom:
MRN BA_NUM
8 33
22 533
27 533

Thank you sir...I will try this.
[quoted text clipped - 16 lines]

and Grouping, you should remove the ORDER BY BA_NUM line
after you are done testing the query.

 

[Marshall Barton]

That seems very strange, the only way I can see that
happening is if Rnd() is returning the same value for
several records in each group, which should be very
unlikely. If something like that is happening you can break
the ties by adding the Mrn field to the end of the Order By
clause:
ORDER BY RND(IdxMrn), IdxMrn

OTOH, If the groups are such that 20% is rounded up to the
next whole number in most every group and you have hundreds
of groups, then one extra record per group could add up to a
lot of records.

 

[trezurehunter]

I have been looking for this for quite some time and you both just
helped me figure it out! The problem with the query is that the RND
function changes for every iteration so when it is trying to pull up
the top, the ranking changes constantly.

I did a simple process - had a table with group names with multiple
records attached, then added an autonumber field. When I used the
query below, it gave me records for every group (not quite at 20%
because of the rounding thing and my number of records being small),
but only a fraction for most. A larger group might get closer to
20%. (As a side note, I tried using TOP 2 versus TOP 20 % and it gave
me 2 records for each group, unless the group only had 1 to start
with.)

SELECT *
FROM Test_table
WHERE (((Test_table.auto_num) In (SELECT TOP 20 percent X.auto_num
FROM Test_table As X
WHERE X.GroupID =
Test_table.GroupID
ORDER BY auto_num)));

I would suggest if you want random, that you make a table from your
original table (or add a field and update) that adds/updates a random
number to an extra field. That way the random number won't change for
every iteration and will fit into the query style above.

--chris

 

[rfuscjr via AccessMonster.com]

First I appreciate all the time you guys are spending helping me. Her is
what I now have:

SELECT *
FROM table1
WHERE (((table1.key) In (SELECT TOP 20 percent X.key
FROM table1 As X
WHERE X.BA =table1.Group
ORDER BY key)));

Note that 'key' is an autonumber field I added to the table. This indeed
pulls a random sample the first time run, pulling 20% from each Group. BUT....
if I run it a second time I get the exact same resulting record set. I think
yor final paragraph was attempting to address this but I am a bit confused as
to what to do?

 

[Marshall Barton]

First I appreciate all the time you guys are spending helping me. Her is
what I now have:

SELECT *
FROM table1
WHERE (((table1.key) In (SELECT TOP 20 percent X.key
FROM table1 As X
WHERE X.BA =table1.Group
ORDER BY key)));

Note that 'key' is an autonumber field I added to the table. This indeed
pulls a random sample the first time run, pulling 20% from each Group. BUT....
if I run it a second time I get the exact same resulting record set. I think
yor final paragraph was attempting to address this but I am a bit confused as
to what to do?

Without using Rnd() there is nothing random about that
query.

 

[rfuscjr via AccessMonster.com]

On your solution I tried:
ORDER BY RND(IdxMrn), IdxMrn

still did not work.

First I appreciate all the time you guys are spending helping me. Her is
what I now have:

[quoted text clipped - 11 lines]

yor final paragraph was attempting to address this but I am a bit confused as
to what to do?

Without using Rnd() there is nothing random about that
query.

 

[jasfre7]

You would need to do the process in two parts, first make a table from
the following query - this will randomize the records.

Select Table1.*, Rnd(IdxMrn) as Random
From Table1

Then use the query from my post above, only replace the autonumber
field with the random field.

I know that often two part solutions would not work for some, but it
will work for me, so I thought I would suggest it.

 

[Marshall Barton]

On your solution I tried:
ORDER BY RND(IdxMrn), IdxMrn

Are you saying that the report is displaying more records
than the query displays?

chris is most likely onto somthing with the value of Rnd()
being different every time a record is processed. and who
knows how many times a record is processed in a subquery.

I don't have time to dig into this any more right now.
Maybe others will have more specific ideas, before I can
find time to set up a test scenario and analyze what's going
on.

 

[rfuscjr via AccessMonster.com]

I think I understand...will try...thanks to you both and have great holiday!