Access (VBA)

Access VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Access 2003)
アクセス グループ番号の採番について
投稿日時: 20/03/12 13:54:16
投稿者: なおてぃん

 お世話になります。
 テーブルAを元にクエリで、B(テーブルもしくはクエリ)の賃貸区分に0,1,2,3・・・と連番を振りたいです。条件は、耕作区分が3の時で、使用者番号別で、土地所有者番号別に1、2、3と振りたいです。耕作区分が3でない時は0と振ります。採番順は、所有者番号順です。クエリでできなければ、SQL、VBAでも構わないです。宜しくお願いします。
 
〇テーブルA
所有者 土地  使用者 使用者 地番  連番  耕作
番号    所有者    番号                区分
1    A    6    F    716-7    1    2
1    A    6    F    716-10    2    2
2    B    2    B    1613-1    3    1
2    B    2    B    1613-2    4    1
2    B    2    B    1985-4    5    1
3    C    3    C    1985-8    6    1
3    C    3    C    1985-13    7    1
4    D    3    C    2340-3    8    3
4    D    3    C    2340-20    9    3
4    D    3    C    2368-1    10    3
3    C    3    C    2368-2    11    1
3    C    3    C    2368-3    12    1
4    D    3    C    4678-1    13    3
5    E    3    C    2067-20    14    3
5    E    3    C    2067-21    15    3
5    E    3    C    2067-22    16    3
5    E    2    B    2067-23    17    3
5    E    2    B    2067-24    18    3
5    E    2    B    2067-25    19    3
 
〇B(テーブルもしくはクエリ)
所有者 土地  使用者 使用者 地番  連番  耕作  賃貸
番号    所有者    番号                区分    区分
2    B    2    B    1613-1    3    1    0
2    B    2    B    1613-2    4    1    0
2    B    2    B    1985-4    5    1    0
5    E    2    B    2067-23    17    3    1
5    E    2    B    2067-24    18    3    1
5    E    2    B    2067-25    19    3    1
3    C    3    C    1985-8    6    1    0
3    C    3    C    1985-13    7    1    0
3    C    3    C    2368-2    11    1    0
3    C    3    C    2368-3    12    1    0
4    D    3    C    2340-3    8    3    1
4    D    3    C    2340-20    9    3    1
4    D    3    C    2368-1    10    3    1
4    D    3    C    4678-1    13    3    1
5    E    3    C    2067-20    14    3    2
5    E    3    C    2067-21    15    3    2
5    E    3    C    2067-22    16    3    2
1    A    6    F    716-7    1    2    0
1    A    6    F    716-10    2    2    0
 

回答
投稿日時: 20/03/13 08:07:06
投稿者: mayu.

引用:
クエリでできなければ、SQL、VBAでも構わないです。

記述した SQL に名前を付けて保存する →「 クエリ 」になります。
 
なお、デザインビューで【 作成できるか・グラフィカルな表示が可能か 】どうかは
表現したい結果セットの内容 と エンジニアのスキル次第 であると申し上げておきます。
 
■ SQL
SELECT t.*, q.賃貸区分
FROM テーブルA t
INNER JOIN
(
    SELECT A.所有者番号, A.使用者番号, Count(1) As 賃貸区分
    FROM ( SELECT DISTINCT 所有者番号, 使用者番号 FROM テーブルA WHERE 耕作区分 = 3 ) A
       , ( SELECT DISTINCT 所有者番号, 使用者番号 FROM テーブルA WHERE 耕作区分 = 3 ) B
    WHERE A.所有者番号  = B.所有者番号
      AND A.使用者番号 >= B.使用者番号
    GROUP BY A.所有者番号, A.使用者番号
    UNION ALL
    SELECT DISTINCT 所有者番号, 使用者番号, 0 FROM テーブルA WHERE 耕作区分 <> 3
) q
ON t.所有者番号 = q.所有者番号 AND
   t.使用者番号 = q.使用者番号
ORDER BY t.所有者番号, t.使用者番号 ;

投稿日時: 20/03/13 14:29:24
投稿者: なおてぃん

 大変ありがとうございます。
 教えて頂いた内容で、行った結果。下記の通り成功しました。しかし、例題のテーブルではなく、実際に使用するテーブル(254レコード)で行うと、下記のように、連番できた使用者もいますが、そうでない場合もありました。宜しくお願いします。
 
〇テーブルAの結果
 
所有者 土地  耕作者 使用者 地番  連番  耕作  賃貸
番号    所有者    番号                区分    区分
9    I    1    A    25-2    241    3    1
10    J    2    B    2340-3    143    3    1
10    J    2    B    4678-1    226    3    1
10    J    2    B    2368-1    148    3    1
10    J    2    B    2340-20    146    3    1
9    I    2    B    28-2    243    3    2
9    I    2    B    25-1    240    3    2
9    I    2    B    28-1    242    3    2
9    I    3    C    67-5    248    3    3
9    I    3    C    67-6    249    3    3
9    I    4    D    67-1    244    3    4
9    I    4    D    67-3    246    3    4
9    I    4    D    67-4    247    3    4
10    J    5    E    3539-3    217    3    2
10    J    5    E    3539-2    216    3    2
10    J    6    F    1708-1    43    3    3
10    J    6    F    1708-5    46    3    3
10    J    6    F    1710-2    54    3    3
10    J    6    F    1710-3    55    3    3
10    J    6    F    1710-4    56    3    3
10    J    6    F    1710-8    59    3    3
9    I    6    F    67-7    250    3    5
9    I    7    G    67-11    254    3    6
9    I    7    G    67-8    251    3    6
9    I    7    G    67-9    252    3    6
9    I    7    G    67-10    253    3    6
9    I    8    H    67-2    245    3    7
 
〇実際のテーブルで行った結果(耕作区分3のみ抽出)
所有者 土地  使用者 使用者 地番  連番  耕作  賃貸
番号    所有者    番号                区分    区分
9    I    1    A    25-2    241    3    1
10    J    2    B    2340-3    143    3    1
10    J    2    B    4678-1    226    3    1
10    J    2    B    2368-1    148    3    1
10    J    2    B    2340-20    146    3    1
9    I    2    B    28-2    243    3    2
9    I    2    B    25-1    240    3    2
9    I    2    B    28-1    242    3    2
9    I    3    C    67-5    248    3    3
9    I    3    C    67-6    249    3    3
9    I    4    D    67-1    244    3    4
9    I    4    D    67-3    246    3    4
9    I    4    D    67-4    247    3    4
10    J    5    E    3539-3    217    3    2
10    J    5    E    3539-2    216    3    2
10    J    6    F    1708-1    43    3    3
10    J    6    F    1708-5    46    3    3
10    J    6    F    1710-2    54    3    3
10    J    6    F    1710-3    55    3    3
10    J    6    F    1710-4    56    3    3
10    J    6    F    1710-8    59    3    3
9    I    6    F    67-7    250    3    5
9    I    7    G    67-11    254    3    6
9    I    7    G    67-8    251    3    6
9    I    7    G    67-9    252    3    6
9    I    7    G    67-10    253    3    6
9    I    8    H    67-2    245    3    7
 
 
 
 
 
 

投稿日時: 20/03/13 14:40:52
投稿者: なおてぃん

ごめんなさい。どちらも同じ結果を記載しました。
 
テーブルAでの成功結果はこちらです。
 
所有者 土地  使用者 使用者 地番  連番  耕作  賃貸
番号    所有者    番号                区分    区分
2    B    2    B    1613-1    3    1    0
2    B    2    B    1985-4    5    1    0
2    B    2    B    1613-2    4    1    0
5    E    2    B    2067-25    19    3    1
5    E    2    B    2067-24    18    3    1
5    E    2    B    2067-23    17    3    1
3    C    3    C    2368-3    12    1    0
3    C    3    C    1985-8    6    1    0
3    C    3    C    1985-13    7    1    0
3    C    3    C    2368-2    11    1    0
4    D    3    C    4678-1    13    3    1
4    D    3    C    2340-3    8    3    1
4    D    3    C    2368-1    10    3    1
4    D    3    C    2340-20    9    3    1
5    E    3    C    2067-20    14    3    2
5    E    3    C    2067-22    16    3    2
5    E    3    C    2067-21    15    3    2
1    A    6    F    716-10    2    2    0
1    A    6    F    716-7    1    2    0
 

回答
投稿日時: 20/03/13 17:49:00
投稿者: mayu.

引用:
しかし、例題のテーブルではなく、実際に使用するテーブル(254レコード)で行うと、
下記のように、連番できた使用者もいますが、そうでない場合もありました。

「 そうでない場合 」を具体的に説明いただけますか。
  
どういう理由で、どのような結果にならないといけないのか
という「 筋道 」や「 ロジック 」を提示いただかないと
実行結果の掲載が意味をなさず、誤認や曲解を誘発する可能性が高くなります。
  
現状から推測するなら、私となおてぃんさんで
解釈が異なっている可能性があるのは
グループ連番のキー項目に対する 優先順位 とかでしょうか。
  
私の解釈は
-------------------------------------------------------
  第1キー: 所有者番号  第2キー: 使用者番号 
-------------------------------------------------------
ですが、実際は
-------------------------------------------------------
  第1キー: 使用者番号  第2キー: 所有者番号
-------------------------------------------------------
であるとか。

投稿日時: 20/03/13 19:32:57
投稿者: なおてぃん

わかりずらく、すいません。
実際に使用するテーブル(254レコード)で行うと下記の通りになったのですが、◎賃貸区分(採番したい番号)のように1,2,3・・・と番号を振りたいです。
グループ連番のキー項目に対する 優先順位は
-------------------------------------------------------
  第1キー: 使用者番号  第2キー: 所有者番号
-------------------------------------------------------
という事だと思います。
 耕作区分が3の時に、使用者番号の中で、所有者番号別に所有者番号の若い番号から1,2,3・・・とグループ番号をこのように振りたいです。
       使用者番号  所有者番号 賃貸区分
    2 9 1
                   2 10 2
                   6 9 1
                   6 10 2
 
〇実際のテーブルで行った結果(耕作区分3のみ抽出) ◎
所有者 土地  使用者 使用者 地番  連番  耕作  賃貸    賃貸
番号 所有者 番号 区分 区分    区分(採番したい番号)
9 I 1 A 25-2 241 3 1     1
9 I 2 B 28-2 243 3 2 1
9 I 2 B 25-1 240 3 2 1
9 I 2 B 28-1 242 3 2 1
10 J 2 B 2340-3 143 3 1 2
10 J 2 B 4678-1 226 3 1 2
10 J 2 B 2368-1 148 3 1 2
10 J 2 B 2340-20 146 3 1 2
9 I 3 C 67-5 248 3 3 1
9 I 3 C 67-6 249 3 3 1
9 I 4 D 67-1 244 3 4 1
9 I 4 D 67-3 246 3 4 1
9 I 4 D 67-4 247 3 4 1
10 J 5 E 3539-3 217 3 2 1
10 J 5 E 3539-2 216 3 2 1
9 I 6 F 67-7 250 3 5 1
10 J 6 F 1708-1 43 3 3 2
10 J 6 F 1708-5 46 3 3 2
10 J 6 F 1710-2 54 3 3 2
10 J 6 F 1710-3 55 3 3 2
10 J 6 F 1710-4 56 3 3 2
10 J 6 F 1710-8 59 3 3 2
9 I 7 G 67-11 254 3 6 1
9 I 7 G 67-8 251 3 6 1
9 I 7 G 67-9 252 3 6 1
9 I 7 G 67-10 253 3 6 1
9 I 8 H 67-2 245 3 7 1

回答
投稿日時: 20/03/13 19:41:41
投稿者: mayu.

引用:
グループ連番のキー項目に対する 優先順位は
-------------------------------------------------------
  第1キー: 使用者番号  第2キー: 所有者番号
-------------------------------------------------------
という事だと思います。

このロジックで採番するのでしたら、以下の SQL でどうでしょうか。
 
SELECT t.*, IIf( t.耕作区分 <> 3, 0, Nz( q.連番, 0 ) ) As 賃貸区分
FROM テーブルA t
LEFT JOIN
(
    SELECT A.使用者番号, A.所有者番号, Count(1) As 連番
    FROM ( SELECT DISTINCT 使用者番号, 所有者番号 FROM テーブルA WHERE 耕作区分 = 3 ) A
       , ( SELECT DISTINCT 使用者番号, 所有者番号 FROM テーブルA WHERE 耕作区分 = 3 ) B
    WHERE A.使用者番号  = B.使用者番号
      AND A.所有者番号 >= B.所有者番号
    GROUP BY A.使用者番号, A.所有者番号
) q
ON t.使用者番号 = q.使用者番号 AND 
   t.所有者番号 = q.所有者番号
ORDER BY t.使用者番号, t.所有者番号 ;

投稿日時: 20/03/16 15:15:02
投稿者: なおてぃん

うまく、いきました。
大変、ありがとうございます。