MS Access Tips/Sample and VBA and Blog customize etc...

クエリでランダムに並べ替える、また、指定件数を無作為に抽出する

例えば、
500問ぐらいの問題文の入ったテーブルがあります。そこから無作為に20件を抽出してレポートに出力したい。
というような時に使える方法です。

前に配列をランダムに並べ替える関数は紹介しましたが、

配列をランダムに並べ替える関数 - hatena chips

今回はそれをクエリで実現する方法です。

難易度:

まず、標準モジュールに下記の関数を作成します。

問題のテーブルをもとにクエリを作成して、必用なフィールドを表示させて、その後に、下記のように演算フィールドを追加します。

クエリのデザインビュー

SQLビューでは下記のようになります。

「問題番号」はどれでもいいのでテーブルにあるフィールド名にしてください。
できれば、主キーフィールドを推奨します。

以上でクエリを開く度に、全件のレコードがランダムに並べ替えられます。

指定件数を無作為に取り出すには、クエリプロパティの「トップ値」に件数を指定します。

クエリプロパティの設定

SQLビューでは下記のようになります。

このクエリをレコポートのレコードソースにすれば、問題用紙が印刷できます。

解説

Rnd関数でランダムな数値を生成するので、クエリから単純に呼び出せばいいように思うかも知れません。

※問題番号は、正の数値

テストしてみると、クエリを開きなおす度に並び順がランダムに変更されます。しかし、いったん、このMDBを閉じて、再度開いて、クエリを開くと、前回の最初のときと同じ並び順でなります。クエリを閉じてまた開くと前回の2度目と同じ並び順になります。

Rnd関数のヘルプに下記の記述があります。

初期シード値が変わらない限り、一連の Rnd 関数が返す乱数系列は同じになります。これは、連続する各 Rnd 関数が乱数系列の中の直前の乱数をシード値として、次の乱数をそれぞれ生成するためです。
システム タイマーから取得した新しいシード値を使って、乱数ジェネレータを初期化するには、Rnd 関数を呼び出す前に、引数を指定せずに Randomize ステートメントを実行してください。

つまり、Rnd関数を呼び出す前には、Randomize ステートメントを実行する必用があるのです。

では、MDBを開くとき、例えば AutoExecマクロで、Randomizeステートメントを記述したプロシージャを実行するとかしておけばいいように思うかも知れません。

しかし、これで、テストしてみると、やはり前回MDBを開いたときと同じ並び順になります。どうも、VBAのコードと、クエリの演算では、実行空間(←表現が適切かどうかはわかりませんが)が異なるようです。

仕方がないので、上記のように標準モジュールに関数を作成して、その内部でRndを呼び出すようにしてます。

次に、MyRnd関数ですが、関数内部では、引数Dummyは使用していないのに、なぜフィールド値を引数としているのか、と疑問に思うかもしれません。

これは、もし引数にフィールド値を与えないと1回しか呼び出されないというクエリの仕様があるからです。ですので、下記のように定数を与えると、すべて同じ値が返るので、並び替えることができません。

逆に、Randomize は1回のみ呼び出されればよいので、MyRandomize には引数を持たせません。

Randomize は1回のみ呼び出せば十分なので、AutoExecマクロを利用するなどして、起動時に呼び出しておけば、クエリで、MyRandomize を呼び出す必用はありません。クエリで「式1」という空白のフィールドが追加されるのが気になる場合は、こちらの方法をご使用ください。

2011/07/07 追記: kikuさんのブログの記事 ~ kiku tips ~  遊んでみますか の追記で当記事を参照している部分があるのですが、そこで「式1」という空白のフィールドを使わずにWhere句にRandomizeを実行する関数を記述するという方法を使用してました。これは目から鱗のスマートな方法だと唸らせられました。ということでこの方法を利用したものを下記に掲載しておきます。

サンプルファイルが下記からダウンロードできます。
QryRandomFilter_07.zip (Access 2007 形式 - 50kb)
QryRandomFilter.zip (Access 2002-2003 形式 - 45kb)
QryRandomFilter_2k.zip (Access 2000 形式 - 40kb)

拍手する

Leave a reply






Trackbacks

trackback URL
http://hatenachips.blog34.fc2.com/tb.php/310-5e01aeb0
該当の記事は見つかりませんでした。