Não me lembro de ter uma consulta simples que possa transformar os dados da maneira que você deseja. A primeira coisa que me veio à mente foi uma consulta de tabela de referência cruzada, no entanto isso não é algo que você pode fazer usando a estrutura de dados que você tem. Eu gostaria de dar a você uma solução de VBA, pois acho que você está indo no caminho certo para reprojetar os dados da sua tabela.
Para esta solução funcionar, crie primeiro a segunda tabela InvoicesT
, com os seguintes campos.
InvoicesT
'''''''''
Field Name | Data Type
--------------------+------------------
invoiceID | Autonumber
UserID | Number
InvoiceCategory | Text
Em seguida, copie o seguinte sub para um módulo padrão.
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
Agora, depois de salvar o módulo com um nome como mod_TransferTables
e compilá-lo. Vá para a sua janela imediata (CTRL + G). Em seguida, simplesmente digite transferData
e pressione ENTER
, isso pode demorar um pouco com base no número de registros na tabela MainT
. Quando isso estiver completo. Você deve ter todos os dados na nova tabela.
Espero que isso ajude!