Hello,

Due to customer requests I need to learn how to denormalize a table. Below is an example of the current table (Table1 - Wholesaler Info):

Company_id Ind_id WholType WholName WholCity WholState
12345 9 Equipment Jack's Wholsale Tampa FL
12345 9 Food Fred's Wholsale New York NY
12345 9 Supplies Jack's Wholsale Tampa FL
12345 9 Full Line Bob's Wholesale Reno NV

What I need is for the Wholesaler data WholType, WholName, WholCity and WholState to be "flattened" into one row per Company_id. There are only 4 possible Wholsale Types. What I need resembles this:

Company_id Ind_id WhlType1 WhlName1 WhlCity1 WhlSt1 WhlType2 WhlName2 WhlCity2 WhlSt2 etc. All the way upto WhlType4.

I have some code that I was working with but I am lost on how to make it fit what I need. Below is the code:

Option Compare Database
Option Explicit

Sub DenormalizeTable()
CreateDenormalizedTable (MaxNumberOfFields)
Denormalize
End Sub
Function MaxNumberOfFields()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim NumberOfFields As Integer

Set db = CurrentDb
strSQL = "SELECT TOP 1 Count(Table1.WholType) AS FieldCount " _
& "FROM Table1 " _
& "GROUP BY Table1.Company_ID " _
& "ORDER BY Count(Table1.WholType) DESC;"
Set rs = db.OpenRecordset(strSQL)
MaxNumberOfFields = rs!FieldCount
End Function
Sub CreateDenormalizedTable(FieldCount As Integer)
On Error GoTo Err_CreateDenormalizedTable

Dim db As DAO.Database
Dim tblNew As DAO.TableDef
Dim fld As Field
Dim IndexNumber As Integer
Set db = CurrentDb

Set tblNew = db.CreateTableDef("Table2")
Set fld = tblNew.CreateField("Company_Id", dbInteger)
Set fld = tblNew.CreateField("Ind_Id", dbInteger)
Set fld = tblNew.CreateField("WhlType1", dbText, 100)
Set fld = tblNew.CreateField("WhlName1", dbText, 100)
Set fld = tblNew.CreateField("WhlCity1", dbText, 100)
Set fld = tblNew.CreateField("WhlSt1", dbText, 2)
Set fld = tblNew.CreateField("WhlType2", dbText, 100)
Set fld = tblNew.CreateField("WhlName2", dbText, 100)
Set fld = tblNew.CreateField("WhlCity2", dbText, 100)
Set fld = tblNew.CreateField("WhlSt2", dbText, 2)
Set fld = tblNew.CreateField("WhlType3", dbText, 100)
Set fld = tblNew.CreateField("WhlName3", dbText, 100)
Set fld = tblNew.CreateField("WhlCity3", dbText, 100)
Set fld = tblNew.CreateField("WhlSt3", dbText, 2)
Set fld = tblNew.CreateField("WhlType4", dbText, 100)
Set fld = tblNew.CreateField("WhlName4", dbText, 100)
Set fld = tblNew.CreateField("WhlCity4", dbText, 100)
Set fld = tblNew.CreateField("WhlSt4", dbText, 2)
tblNew.Fields.Append fld

For IndexNumber = 1 To FieldCount
Set fld = tblNew.CreateField("Company_Id" & IndexNumber, dbInteger)
tblNew.Fields.Append fld
Next IndexNumber
db.TableDefs.Append tblNew

Exit_CreateDenormalizedTable:
Exit Sub

Err_CreateDenormalizedTable:
If Err.Number = 3265 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_CreateDenormalizedTable
End If
End Sub
Sub Denormalize()

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim FieldCount As Integer
Dim currentCompany_Id As Long, previousCompany_Id As Long

Set db = CurrentDb
Set rs1 = db.OpenRecordset("Table1") 'table with old format
Set rs2 = db.OpenRecordset("Table2") 'table with new format

DoCmd.SetWarnings False
DoCmd.RunSQL ("Delete * from Table2")
DoCmd.SetWarnings True

FieldCount = 1
rs1.MoveFirst

Do While Not rs1.EOF
currentCompany_Id = rs1!Company_Id
If currentCompany_Id <> previousCompany_Id Then
FieldCount = 1
rs2.AddNew
rs2!Company_Id = rs1!Company_Id
rs2.Update
Else
FieldCount = FieldCount + 1
rs2.MoveLast
rs2.Edit
rs2!Company_Id = rs1!Company_Id
rs2.Update
End If
previousCompany_Id = currentCompany_Id
rs1.MoveNext
Loop

End Sub

I would appreciate any help anyone can give. Thanks in advance.