Access (VBA)

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

 
(Windows 10 Pro : Access 2016)
列を分割して積み上げたテーブルを作成したい
投稿日時: 21/03/03 01:01:16
投稿者: mugiro7

テーブル1とテーブル2のkeyで紐付けし、積み上げて変更フラグをつけたテーブル3を作りたい。
教科の値は、フィールド名から取得したい。
同様のパターンで処理したい複数のテーブルがあり、パターン毎に教科は異なり、教科の列は増減します。
よろしくお願いします。
 
テーブル1
key 国語 数学 英語
1001 81 91 71
1002 55 65 45
 
テーブル2
key 国語 数学 英語
1001 80 90 70
1002 82 65 45
 
テーブル3
key 教科 1T 2T 変F
1001 国語 81 80 1
1001 数学 91 90 1
1001 英語 71 70 1
1002 国語 55 82 1
1002 数学 65 65 0
1002 英語 45 45 0

回答
投稿日時: 21/03/03 10:05:05
投稿者: Suzu

そもそも、フィールド数が増減し、その様な管理を行いたいのであれば
 
テーブル1、テーブル2 の構造自体に問題があります。
 
初めから、テーブル3形式にした方が良いです。

投稿日時: 21/03/03 11:23:12
投稿者: mugiro7

回答ありがとうございます。
うまく説明できず、すみません。
テーブル1と2は、既存テーブルで、変更ゆできません。
 
同様の処理をしたいテーブルの組み合わせが他にもあるという意味で、
テーブル1、2の列のは同一で、増減はありません。
実行したい事は、
テーブル1が前回の値、テーブル2が今回の値で、教科ごとの値を並べて比較し、変更フラグをつけたいのです。
どうぞ、よろしくお願いいたします。

回答
投稿日時: 21/03/03 12:01:43
投稿者: Suzu

引用:
テーブル1と2は、既存テーブルで、変更ゆできません。

引用:
テーブル1、2の列のは同一で、増減はありません。

 
 
テーブル3 は 既にあり、1T,2T の 値要求 は いいえ だとします。
 
1. 追加するデータの KEYをテーブル3に追加します。
INSERT INTO テーブル3 (KEY)
SELECT KEY FROM
(
SELECT KEY FROM テーブル1
UNION
SELECT KEY FROM テーブル2
)
 
 
次に、テーブル1 の各教科のデータを更新クエリを用いて テーブル3 に対し更新
 
テーブル1 - 国語分
UPDATE Table3 INNER JOIN Table1 ON Table3.KEY = Table1.KEY
SET Table3.教科 = "国語", Table3.[1T] = [Table1].[国語];
 
テーブル1 - 数学分
UPDATE Table3 INNER JOIN Table1 ON Table3.KEY = Table1.KEY
SET Table3.教科 = "数学", Table3.[1T] = [Table1].[数学];
 
テーブル1 - 英語分
UPDATE Table3 INNER JOIN Table1 ON Table3.KEY = Table1.KEY
SET Table3.教科 = "英語", Table3.[1T] = [Table1].[英語];
 
 
同じく テーブル2 に対しても実行
 
テーブル2 - 国語分
UPDATE Table3 INNER JOIN Table2 ON Table3.KEY = Table2.KEY
SET Table3.教科 = "国語", Table3.[2T] = [Table2].[国語];
 
テーブル2 - 数学分
UPDATE Table3 INNER JOIN Table2 ON Table3.KEY = Table2.KEY
SET Table3.教科 = "数学", Table3.[2T] = [Table2].[数学];
 
テーブル2 - 英語分
UPDATE Table3 INNER JOIN Table2 ON Table3.KEY = Table2.KEY
SET Table3.教科 = "英語", Table3.[2T] = [Table2].[英語];
 
 
の様な手順でしょうか。

回答
投稿日時: 21/03/03 15:57:31
投稿者: sk

引用:
テーブル1とテーブル2のkeyで紐付けし、積み上げて
変更フラグをつけたテーブル3を作りたい。
教科の値は、フィールド名から取得したい。
同様のパターンで処理したい複数のテーブルがあり、
パターン毎に教科は異なり、教科の列は増減します。

(標準モジュール)
-----------------------------------------------------------------
Sub Test()
 
    Const Table1 As String = "テーブル1"
    Const Table2 As String = "テーブル2"
    Const CompareResultTable As String = "テーブル3"
 
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim rs As DAO.Recordset
    Dim strSQL As String
     
    Set db = CurrentDb
     
    strSQL = "DELETE * FROM [" & CompareResultTable & "];"
    Debug.Print strSQL
    db.Execute strSQL, dbFailOnError
     
    strSQL = ""
     
    Set tdf = db.TableDefs(Table1)
    For Each fld In tdf.Fields
        Select Case fld.Name
            Case "key"
                '何もしない
            Case Else
                strSQL = strSQL & _
                         "UNION " & _
                         "SELECT [" & tdf.Name & "].[key]" & _
                         ", '" & fld.Name & "' AS [教科] " & _
                         "FROM [" & tdf.Name & "]"
        End Select
    Next
    Set tdf = Nothing
     
    Set tdf = db.TableDefs(Table2)
    For Each fld In tdf.Fields
        Select Case fld.Name
            Case "key"
                '何もしない
            Case Else
                strSQL = strSQL & _
                         "UNION " & _
                         "SELECT [" & tdf.Name & "].[key]" & _
                         ", '" & fld.Name & "' AS [教科]" & _
                         " FROM [" & tdf.Name & "]"
        End Select
    Next
     
    Set tdf = Nothing
     
    strSQL = Mid(strSQL, 7)
 
    strSQL = "INSERT INTO [" & CompareResultTable & "] ([key], [教科])" & _
             " SELECT uq.[key], uq.[教科]" & _
             " FROM (" & strSQL & ") uq;"
     
    Debug.Print strSQL
    db.Execute strSQL, dbFailOnError
 
    strSQL = "SELECT [教科] " & _
             " FROM [" & CompareResultTable & "]" & _
             " GROUP BY [教科];"
    Debug.Print strSQL
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
     
    Do Until rs.EOF
             
        strSQL = "UPDATE [" & CompareResultTable & "] t0" & _
                 " INNER JOIN [" & Table1 & "] t1" & _
                 " ON t0.[key] = t1.[key]" & _
                 " SET t0.[1T] = t1.[" & rs![教科].Value & "]" & _
                 " WHERE t0.[教科] = '" & rs![教科].Value & "';"
        Debug.Print strSQL
        db.Execute strSQL, dbFailOnError
         
        strSQL = "UPDATE [" & CompareResultTable & "] t0" & _
                 " INNER JOIN [" & Table2 & "] t1" & _
                 " ON t0.[key] = t1.[key]" & _
                 " SET t0.[2T] = t1.[" & rs![教科].Value & "]" & _
                 " WHERE t0.[教科] = '" & rs![教科].Value & "';"
        Debug.Print strSQL
        db.Execute strSQL, dbFailOnError
         
        rs.MoveNext
    Loop
     
    Set rs = Nothing
     
    strSQL = "UPDATE [" & CompareResultTable & "]" & _
             " SET [変F] = 0" & _
             " WHERE [1T] = [2T];"
    Debug.Print strSQL
    db.Execute strSQL, dbFailOnError
     
    strSQL = "UPDATE [" & CompareResultTable & "]" & _
             " SET [変F] = 1" & _
             " WHERE [1T] <> [2T];"
    Debug.Print strSQL
    db.Execute strSQL, dbFailOnError
     
    Set db = Nothing
     
End Sub
-----------------------------------------------------------------
 
以上のようなことが出来ればよい、ということでしょうか。

回答
投稿日時: 21/03/03 16:13:21
投稿者: sk

追記:

引用:
テーブル3
key 教科 1T 2T 変F
1001 国語 81 80 1
1001 数学 91 90 1
1001 英語 71 70 1
1002 国語 55 82 1
1002 数学 65 65 0
1002 英語 45 45 0

テーブルの正規化の観点から考えると、
次のような構造のテーブルにレコードを変換、蓄積してから
上記のような結果を返すクエリを作成するようになさった方が
使い勝手が良さそうに思いますが。
 
--------------------------------------------------
テストID	学生ID	教科	得点
--------------------------------------------------
T2021001	1001	英語	71
T2021001	1001	国語	81
T2021001	1001	数学	91
T2021001	1002	英語	45
T2021001	1002	国語	55
T2021001	1002	数学	65
T2021002	1001	英語	70
T2021002	1001	国語	80
T2021002	1001	数学	90
T2021002	1002	英語	45
T2021002	1002	国語	82
T2021002	1002	数学	65
--------------------------------------------------

投稿日時: 21/03/06 08:52:35
投稿者: mugiro7

skさま
早速の回答に感謝しております。
希望通りの結果が確認できました。
 
ですが、実際の業務で使用しているDBで実行すると、
下記の、db.Execute strSQL, dbFailOnError のところで、「クエリが複雑すぎます」とメッセージが表示されてしまいます。
 
strSQL = Mid(strSQL, 7)
strSQL = "INSERT INTO [" & CompareResultTable & "] ([key], [教科])" & _
         " SELECT uq.[key], uq.[教科]" & _
         " FROM (" & strSQL & ") uq;"
Debug.Print strSQL
db.Execute strSQL, dbFailOnError
 
strSQLの文字数制限を超えているのか、列が16個あるところを、3個に減らし実行すると、結果取得できました。
 
また、ご指摘のとおり、レコード変換、蓄積までのテーブルを作成したいほうがよい考え直しました。
しかし、未熟者でうまく作成できません。
 
テーブル1、テーブル2から、テーブル3を作成する方法を教えてください。
 
テーブル1
key 国語 数学 英語
1001 81 91 71
1002 55 65 45
  
テーブル2
key 国語 数学 英語
1001 80 90 70
1002 82 65 45
 
テーブル3
テストID    学生ID    教科    得点
--------------------------------------------------
T2021001    1001    英語    71
T2021001    1001    国語    81
T2021001    1001    数学    91
T2021001    1002    英語    45
T2021001    1002    国語    55
T2021001    1002    数学    65
T2021002    1001    英語    70
T2021002    1001    国語    80
T2021002    1001    数学    90
T2021002    1002    英語    45
T2021002    1002    国語    82
T2021002    1002    数学    65
--------------------------------------------------
どうぞ、よろしくお願いいたします。

投稿日時: 21/03/07 20:07:48
投稿者: mugiro7

skさま
テストデータで、列数、文字数を増やしても、正常に結果が作成できました。
職場のDBで「クエリが複雑すぎます」と出てしまう原因は、別にありそうです。
レコード変換、蓄積までのテーブルを作成する点は、いただいたコードの一部を実行することで
作成できることを理解しました。
ありがとうございました。

投稿日時: 21/03/08 17:06:13
投稿者: mugiro7

skさま
間違えた部分は不明ですが、エラーが解消できました。
ありがとうございました。