クエリで自動連番のふり方

質問

次のようなテーブル（T_連番）が元となったクエリ（Q_連番）があります。

テーブル：T_連番
　氏名
　あああ　あああ
　いいい　いいい
　ううう　ううう
　えええ　えええ
　いうえ　いうえ
　あかさ　あかさ
　えおか　えおか

クエリ：Q_連番
　氏名
　あああ　あああ
　いいい　いいい
　ううう　ううう
　えええ　えええ
　いうえ　いうえ
　あかさ　あかさ
　えおか　えおか

　Q_連番では、T_連番をそのままフィールドに入れていますので、
同じ並びで表示となります。この同じ並びとなったものに、下記のように自動で連番を
ふりたいと考えています。

クエリ：Q_連番
　氏名　　　　　　　　連番
　あああ　あああ　　　1
　いいい　いいい　　　2
　ううう　ううう　　　3
　えええ　えええ　　　4
　いうえ　いうえ　　　5
　あかさ　あかさ　　　6
　えおか　えおか　　　7

　インターネットで色々検索しましたが分かりませんでしたので、ご教示をお願いいたします。

回答No.5

山田 太郎（@f_a_007） · ベストアンサー

Q、クエリに連番を付与するには？
A、その為には、連番を付与する関数を利用します。

《連番を付与する関数》

　一番安直なやり方は、広域変数をカウントすること。でも、そのやり方には、致命的な弱点があります。それは、広域変数を初期化しないと、クエリの連番がカウントされ続けるということです。で、そこで、当該レコードが出現するまでのレコード数を数えるという関数を用意します。その関数では、クエリが読み込むレコードを最初から読み直して、現レコードと一致するか否かを判断します。不一致であれば、次のレコードを読んで更に判断します。

　Access のテーブルに登録するデータは、《ある二つの列の値の組み合わせはユニークである》という原則に則って管理されます。例えば、顧客台帳ですと、主キーと顧客の姓の組み合わせはユニークです。ですから、上述のような判断が成立します。

　なお、主キーでソートすれば、DCount()関数で連番を付与できることは、ネット上の解説の通りです。が、「主キーでソートしない」という条件下では、このやり方は通用しません。よって、当該レコードが出現するまでのレコード数を数えるという関数を用意することになります。

Public Function SetSequence(ByVal strQuerySQL As String, _
　　　　　　　　　　　　ByVal strColName_1 As String, _
　　　　　　　　　　　　ByVal strColName_2 As String, _
　　　　　　　　　　　　ByVal varValue_1 As Variant, _
　　　　　　　　　　　　ByVal varValue_2 As Variant) As Long
On Error GoTo Err_SetSequence
　　Dim isFound As Boolean
　　Dim R　　　 As Integer
　　Dim N　　　 As Integer
　　Dim rst　　 As ADODB.Recordset

　　Set cnn = CurrentProject.Connection
　　Set rst = New ADODB.Recordset
　　With rst
　　　　.Open strQuerySQL, _
　　　　　　　CurrentProject.Connection, _
　　　　　　　adOpenStatic, _
　　　　　　　adLockReadOnly
　　　　isFound = False
　　　　If Not .BOF Then
　　　　　　N = .RecordCount - 1
　　　　　　.MoveFirst
　　　　　　For R = 0 To N
　　　　　　　　If .Fields(strColName_1).Value = varValue_1 And .Fields(strColName_2).Value = varValue_2 Then
　　　　　　　　　　isFound = True
　　　　　　　　　　Exit For
　　　　　　　　End If
　　　　　　　　.MoveNext
　　　　　　Next R
　　　　End If
　　End With
Exit_SetSequence:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　SetSequence = IIf(isFound, R + 1, -1)
　　Exit Function
Err_SetSequence:
　　MsgBox "SQL 文の実行時にエラーが発生しました。(SetSequence)" & Chr(13) & Chr(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr(13) & _
　　　　　　"・SQL Text=" & strQuerySQL, _
　　　　　　vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_SetSequence
End Function

　SetSeruence()では、クエリと同一のSQL文、一致判定をする列の名前、その名前に対応する比較データの５つを引数として渡しています。一致したら、isFoundに真を代入して比較のFor-Nextを抜けています。戻り値には、For-Next のカウンターをセットしています。

【クエリのSQL文】

SELECT SetSequence("SELECT ID, 列１ FROM TEST Order By ID","ID","列１",[ID],[列１]) AS 連番, 列１
FROM Test
ORDER BY ID;

SELECT SetSequence("SELECT ID, 列１ FROM TEST Order By列１","ID","列１",[ID],[列１]) AS 連番, 列１
FROM Test
ORDER BY 列１;

《なぜ、Order by 節を書いているのか？》

理由：クエリは表示するレコードの順番は、一定固定ではないから。

SP、クエリに連番を付与することはあり得ない！

　そもそも、アプリケーションの開発においてクエリを利用することはありません。王道は、SQL文を生で書くこと。また、実際には、ユーザがクエリを目にすることもない。目にするのはフォームとレポート。そこでの連番の付与に、かかるSetSequence()を利用することは禁忌。なぜなら、ネットワークの通信量が増え、もって、実行速度に否定的な影響が出るからです。ですから、クエリに連番を付与する関数なんてのは、単なる興味本位の腕試しみたいなもんですよ。

お礼

f_a_007さん、お世話になっております。
大変ご丁寧なご説明と、モジュールをお示しいただきまして、本当にありがとうございます。
これから試してみたいと思います。

回答No.4

chayamati

こんばんは
クエリのレコードソースのテーブルにオートナンバー型のフィールドを追加するだけです

回答No.3

山田 太郎（@f_a_007）

訂正：

が、この場合、

AAA,1
AAA,1
BBB,3
CCC,4

と・・・

回答No.2

山田 太郎（@f_a_007）

Q、クエリに連番を付与するには？
A、各列の出現位置を連番の値として付与することになります。

が、この場合、

AAA,1
AAA,2
BBB,3
CCC,4

という連番が付与されることもあることに注意する必要があります。なぜなら、"AAA"の出現位置は、1番目でも2番目でもあるからです。

　まあ、一般的には、そのような心配は無用。なぜなら、各テーブルは一意の列[ID]を持っています。ですから、101と"AAA"が出現するのは1番目だと知ることができます。106と"AAA"が5番目ということも知ることができます。

101,AAA,1
102,AAA,2
104,BBB,3
105,CCC,4
106,AAA,5

　まあ、回答を書く前に、質問ってそういうことなのか？その確認をしたい。添付図は、そういう手法で連番を付与したものです。

お礼

補足です。
f_a_007さんの添付図のようになればと考えています。

補足

f_a_007さん、ご回答ありがとうございます。
質問の趣旨は、テーブルから抽出したクエリを作成する場合もありますので、テーブルでオートナンバーを使わずにしたいと考えています。

回答No.1

bardfish

[連番]という項目(列)はどのテーブル(表)にあるのでしょうか？

クエリーで作った項目(列)だとしたら目的を達せられません。

仮になんとかなったとしても
　いうえ　いうう　　　5
　いうえ　いうえ　　　6
　あかさ　あかさ　　　7
となってしまい「あかさ　あかさ」の連番が変わってしまいます。

クエリーというのはクエリー自体はデータを持ちません。
実データを持っているテーブルを再配置して列の並びを変えたり同一レコード内の列と列を連結したり計算結果を仮に代入したりして表示させるだけです。

やるならば
T_連番
　氏名　<文字列>
　連番　<オートカウント>
というふうにするしかないでしょう。
※<>内はデータ型です。
オートカウントはテーブルにレコードを追加するごとに自動で連番が振られるのでソートさせてから連番を振りたいというのであれば目的が違います。

入力済みのT_連番に氏名で昇順のソートを掛けてから連番を振りたいというのであればモジュールでプログラムを作成したほうが・・・私ならそのほうが簡単です(^^;

お礼

bardfishさん、ご回答ありがとうございます。
クエリーで作った項目だとできないわけですね。
了解しました。