アクセスで条件に合うもののみに連番を振る方法は？

質問

下記のようにテーブル１にデータがあります。

テーブル１
ID　参加番号　級　判定
1 　　1　　　2　　◎
2 　　2　　　1　　〇
3 　　3　　　2
4 　　4　　　1　　◎
5 　　5　　　3　　◎
6 　　6　　　2　　〇
7 　　7　　　1　
8 　　8　　　3　　〇
9 　　9　　　2　　〇

このデータを追加クエリを使ってテーブル2に追加をします。
その時に級：昇順・参加番号：昇順で並べ替え、同時に判定が〇・◎のものに級別に証書番号として連番を振りたいと考えています。

テーブル２
ID　参加番号　級　判定　証書番号
1　　　2　　　1　　〇　　　1
2　　　4　　　1　　◎　　　2
3　　　7　　　1　
4　　　1　　　2　　◎　　　1
7　　　3　　　2
5　　　6　　　2　　〇　　　2
6　　　9　　　2　　〇　　　3
8　　　5　　　3　　◎　　　1
9　　　8　　　3　　〇　　　2

連番を振る場合Dcountを使えばよいみたいですが、級別だけを条件とした場合は連番が振れるのですが、「判定が〇・◎のもの」という条件の付け方がわかりません。

どなたかアドバイスをお願いします。

回答No.6

piroin654 · ベストアンサー

遅くなりました。データの設定が良くなかったので手間取りましたが、
以下でどうでしょうか。

INSERT INTO テーブル2 ( 参加番号, 級, 判定, 証書番号 )
SELECT テーブル1.参加番号, テーブル1.級, テーブル1.判定, IIf([判定]="〇" Or [判定]="◎",DCount("判定","テーブル1","Not IsNull([判定]) And Not [判定]='欠席' And 参加番号 <=" & [参加番号] & "and 級=" & [級])) AS 証書番号
FROM テーブル1
ORDER BY テーブル1.級, IIf([判定]="〇" Or [判定]="◎",DCount("判定","テーブル1","Not IsNull([判定]) And Not [判定]='欠席' And 参加番号 <=" & [参加番号] & "and 級=" & [級]));

お礼

度々のアドバイス、ありがとうございます。
お教えいただいた内容を参考にして目的を達成することが出来ました。
ありがとうございました。

回答No.5

piroin654

＞(2)「判定」フィールドに「〇・◎」が入力されているかを判定する場合

のSQL文ならば補足されたような希望のデータが抽出されると思いますが
どうでしょうか？こちらでは希望されるデータが抽出されていますが。

補足

(2)の方法だと下記のようになってしまいます。
ID　参加番号　級　判定　証書番号
：
4　　　1　　　2　　◎　　　1
7　　　3　　　2　　欠席　　
5　　　6　　　2　　〇　　　3
6　　　9　　　2　　〇　　　4

確かに欠席のデータの証書番号は表示されないのですが、
番号はカウントされてしまいます。

欠席のデータの番号は表示されないのはもちろん、番号付けも飛ばしたいのですが・・・

回答No.4

piroin654

No2です。判定のフィールドが抜けていました。
以下です。訂正しておきます。

(1)「判定」フィールドにデータがないものと判定する場合

INSERT INTO テーブル2 ( 参加番号, 級, 判定, 証書番号 )
SELECT テーブル1.参加番号, テーブル1.級, テーブル1.判定, IIf(Not IsNull([判定]),DCount("判定","テーブル1","参加番号 <=" & [参加番号] & "and 級=" & [級])) AS 証書番号
FROM テーブル1
GROUP BY テーブル1.参加番号, テーブル1.級, テーブル1.判定, IIf(Not IsNull([判定]),DCount("判定","テーブル1","参加番号 <=" & [参加番号] & "and 級=" & [級]))
ORDER BY テーブル1.級;

(2)「判定」フィールドに「〇・◎」が入力されているかを判定する場合

INSERT INTO テーブル2 ( 参加番号, 級, 判定, 証書番号 )
SELECT テーブル1.参加番号, テーブル1.級, テーブル1.判定, IIf([判定]="〇" Or [判定]="◎",DCount("判定","テーブル1","参加番号 <=" & [参加番号] & "and 級=" & [級])) AS 証書番号
FROM テーブル1
GROUP BY テーブル1.参加番号, テーブル1.級, テーブル1.判定, IIf([判定]="〇" Or [判定]="◎",DCount("判定","テーブル1","参加番号 <=" & [参加番号] & "and 級=" & [級]))
ORDER BY テーブル1.級;

補足

アドバイス有難うございます。

早速試してみたのですが、私の最初の質問で記載したテーブルデータで情報が不足していました。

最初に示したデータでは「判定」の内容が、「◎・〇・空白」しか記載していませんでしたが、それプラス「欠席」というデータもありました。

したがって、本来は「◎・〇」のみに連番を振りたいのですが、ご提示頂いたSQLでは「欠席」にも番号が振られてしまい下記のようになってしまいました。

ID　参加番号　級　判定　証書番号
：
：
4　　　1　　　2　　◎　　　1
7　　　3　　　2　　欠席　　2
5　　　6　　　2　　〇　　　3
6　　　9　　　2　　〇　　　4
これを
ID　参加番号　級　判定　証書番号
：
：
4　　　1　　　2　　◎　　　1
7　　　3　　　2　　欠席　　
5　　　6　　　2　　〇　　　2
6　　　9　　　2　　〇　　　3

というように連番を振るにはどのようにしたらよいのでしょうか？

引き続きアドバイスをお願いします。

回答No.3

山田 太郎（@f_a_007）

70歳にもなると、どうにも質問を理解するのに手間取ります。【回答2/2】を書いている途中で訳が分からなくなりました。何が分からないのか？それは、番号を付与する順番です。以下、【回答2/2】の中途までと、疑問について述べます。

【回答2/2】DBSelect()を利用して[ID]リストを取得する。

まず、DBSelect()について紹介します。次は、イミディエイトウインドウでテストした様子のコピペです。

【イミディエイトウインドウ】

? DBSelect("SELECT * FROM テーブル1",,Chr(13))
1;1;2;◎
2;2;1;○
3;3;2;
4;4;1;◎
5;5;3;◎
6;6;2;○
7;7;1;
8;8;3;○
9;9;2;○

　行区切り子は、第3引数で指定します。この場合は、改行コードを指定していますので、行単位で改行したデータが戻ってきます。

? DBSelect("SELECT ID FROM テーブル1")
1;2;3;4;5;6;7;8;9

　行区切り子を指定していませんので、デフォルトの";"で連結して取得したデータを戻しています。

? DBSelect("SELECT ID,参加番号,級,判定 FROM テーブル1
　　　　　　　　　　WHERE 級=1 AND Len(判定 & '') ORDER BY 判定 DESC",,Chr(13))
4;4;1;◎
2;2;1;○
? DBSelect("SELECT ID,参加番号,級,判定 FROM テーブル1
　　　　　　　　　　WHERE 級=1 AND Len(判定 & '') ORDER BY 判定",,Chr(13))

2;2;1;○　<---ID=2は１番目だから[証書番号]=1
4;4;1;◎　<---ID=4は１番目だから[証書番号]=2

? DBSelect("SELECT ID FROM テーブル1 WHERE 級=1 AND Len(判定 & '') ORDER BY 判定")
2;4
↑
必要なのは、この[ID]のリスト。この並びが、各[ID]の[証書番号]になります。

【疑問点１】

上記テストでも判るように、○◎か◎○で番号を付与するのか？それで、付与する番号は違う。また、参加番号も加味するのか？

補足されたし！

補足

アドバイス有難うございます。
ご提示して頂いた内容ですが、私には少々難しく理解が出来ません。
せっかくアドバイスを頂いたのに申し訳ありませんが、他の方法を検討してみます。

回答No.2

piroin654

＞「判定が〇・◎のもの」という条件の付け方

これは直接「〇・◎」か、あるいはデータが入力されていないかのどちらかで
判定すればいいかと。

テーブル2のIDはオートナンバーにしておきます。

新規のクエリを表示し、デザインビューでSQLビューを表示し、
以下のSQL文をSQLビューに貼り付け、保存します。
クエリを実行するとIDは自動的にレコード数ほど番号が振られます。

(1)「判定」フィールドにデータがないものと判定する場合

INSERT INTO テーブル2 ( 参加番号, 級, 証書番号 )
SELECT テーブル1.参加番号, テーブル1.級, IIf(Not IsNull([判定]),DCount("判定","テーブル1","参加番号 <=" & [参加番号] & "and 級=" & [級])) AS 証書番号
FROM テーブル1
GROUP BY テーブル1.参加番号, テーブル1.級, IIf(Not IsNull([判定]),DCount("判定","テーブル1","参加番号 <=" & [参加番号] & "and 級=" & [級]))
ORDER BY テーブル1.級;

(2)「判定」フィールドに「〇・◎」が入力されているかを判定する場合

INSERT INTO テーブル2 ( 参加番号, 級, 証書番号 )
SELECT [テーブル1].[参加番号], [テーブル1].[級], IIf([判定]="〇" Or [判定]="◎",DCount("判定","テーブル1","参加番号 <=" & [参加番号] & "and 級=" & [級])) AS 証書番号
FROM テーブル1
GROUP BY [テーブル1].[参加番号], [テーブル1].[級], IIf([判定]="〇" Or [判定]="◎",DCount("判定","テーブル1","参加番号 <=" & [参加番号] & "and 級=" & [級]))
ORDER BY [テーブル1].[級];

以上です。わからないところがあれば補足してください。

回答No.1

山田 太郎（@f_a_007）

【回答1/2】

Q、アクセスで条件に合うもののみに連番を振る方法は？
A、同じ条件に一致するIDリストを求めて、何番目が自分かを判定するとOK！

ですから、[テーブル2]の作成は無用です。さて、問題は、SQL文中で如何にして《条件に一致するIDリスト》を取得するかがテーマ。で、それは、【回答2/2】で述べます。先ずは、

＞SELECT XXX FROM【テーブル1】

というクエリの実行結果を紹介います。