☰

Access で 1 つのテーブル内のフィールドを別のテーブルの 1 つのフィールドに結合する

tag-icon • tag-icon vba microsoft-access-2007
Access で 1 つのテーブル内のフィールドを別のテーブルの 1 つのフィールドに結合する

ユーザー ID と 10 列のデータが水平に並べられた 1 つのテーブルから複数のフィールドを結合して、ユーザー ID に対応するフィールドと 10 個の垂直フィールドに対応するフィールドを持つ 2 つのフィールドに垂直に表示しようとしています。2 番目のテーブルでは、ユーザー名は元のテーブルの 10 個の水平フィールドに入力された回数だけ繰り返されます。

私の例は基本的に次のとおりです。

MainT
`````
User ID
otherfields
Invoice1
:
Invoice10

新しいテーブルに追加したいもの:

InvoicesT
`````````
User ID
Invoices

MainT からデータを取得する方法を知る必要があります:

User    |   Othr Fld    |   Invoice1    |   Invoice2    |   Invoice3
--------+---------------+---------------+---------------+-------------
1       |   JF          |   Paid        |   Past Due    |  Civil Court
2       |   JN          |   Paid        |   Paid        |  Past Due
3       |   MO          |   Past Due    |   Paid        |  Paid

私が規定した方法で InvoicesT に入力します。

User    |   Invoice
--------+--------------
1       |   Paid
1       |   Past Due 
1       |   Civil Court
2       |   Paid
2       |   Past Due

等

答え1

データを希望どおりに変換できる単純なクエリがあったかどうかは覚えていません。最初に思いついたのはクロス集計クエリですが、これは現在のデータ構造では実行できません。テーブル データの再設計は正しい方向に進んでいると思いますので、VBA ソリューションをお伝えしたいと思います。

このソリューションを機能させるには、まず次のフィールドを持つ 2 番目のテーブルを作成しますInvoicesT。

InvoicesT
`````````
Field Name          |   Data Type
--------------------+------------------
invoiceID           |   Autonumber 
UserID              |   Number
InvoiceCategory     |   Text

次に、次の Sub を標準モジュールにコピーします。

Public Sub transferData()
    Dim oldTbl As DAO.Recordset, newTbl As DAO.Recordset
    Dim dbObj As DAO.Database
    Dim fCtr As Integer

    Set dbObj = CurrentDB()

    'Open the MainT table to read the information.
    Set oldTbl = dbObj.OpenRecordSet("SELECT [User ID], Invoice1, Invoice2, Invoice3, Invoice4, " & _
                                     "Invoice5, Invoice6, Invoice7, Invoice8, Invoice9, Invoice10 FROM MainT")

    'Open the InvoicesT to write new information into.
    Set newTbl = dbObj.OpenRecordSet("InvoicesT")

    'Loop through the list of all entries in MainT
    Do While Not oldTbl.EOF
        'This will loop through the fields to create one entry for each field.
        'You can change the number 10 to any number depending on the Invoice fields in the table. 
        For fCtr = 1 To 10
            'I have put an IF condition to make sure there are not many empty records for a UserID
            'If the Invoice(n) is not available for a particular user, then it does not create an entry. 
            If Len(oldTbl.Fields("Invoice" & fCtr) & vbNullString) > 0 Then
                With newTbl
                    .AddNew
                    .Fields("UserID") = oldTbl.Fields(0)
                    .Fields("InvoiceCategory") = oldTbl.Fields("Invoice" & fCtr)
                    .Update
                End With
            End If
        Next
        'Go to the next record when one user is done with
        oldTbl.MoveNext
    Loop

    'Clean up
    Set newTbl = Nothing
    Set oldTbl = Nothing
    Set dbObj = Nothing
End Sub

モジュールを のような名前で保存しmod_TransferTables、コンパイルしたら、イミディエイト ウィンドウ (CTRL + G) に移動します。次に、 と入力してtransferDataを押します。テーブルENTER内のレコードの数によっては、時間がかかる場合がありますMainT。これが完了すると、新しいテーブルにすべてのデータが含まれているはずです。

お役に立てれば ！

関連情報