I do not recall having one simple query that can transform the data like the way you want it. The first thing that came to my mind was a Crosstab Query, however this is not something you can do using the data structure you have. I would like to give you a VBA solution, as I feel you are going on the right track in redesigning your table data.
For this solution to work, first create the second table InvoicesT, with the following fields.
InvoicesT ````````` Field Name | Data Type --------------------+------------------ invoiceID | Autonumber UserID | Number InvoiceCategory | Text Next, you copy the following Sub into a Standard Module.
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 Now once you have saved the Module with a name like mod_TransferTables and compiled it. Go to your immediate window (CTRL + G). Then simply type transferData and hit ENTER, it might take a while based on the number of records in the MainT table. Once this is complete. You should be having all the data in the new table.
Hope this helps !