Need help with code to flatten table

[lfarina]

Hello,

I have to denormalize/flatten a table and I can't get things to work correctly. I have a table called PERSON which contains contact names and titles for certain companies. There are three columns in the table - Company_ID (which is the primary key), Fulnm (which is the contact name), and Title (the title for the contact name). There are multiple contacts per company_id, our customers want us to denormalize/flatten the data so that all contacts belonging to one company_id are in one record/row. I need the column headings to be Person01, Title01, Person02, Title02, etc. I have the code somewhat close to what I need but all I am getting is duplicates right now. I would really appreciate any help given. Below is the code I am working with:

Option Compare Database
Option Explicit

Sub DenormalizeTable()
    'this is the main subroutine which calls the others
    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(PERSON.COMPANY_ID) AS FieldCount " _
    & "FROM PERSON " _
    & "GROUP BY PERSON.Company_ID " _
    & "ORDER BY Count(PERSON.COMPANY_ID) ASC;"
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

'   Create the table and a field
Set tblNew = db.CreateTableDef("PERSONNEL")
Set fld = tblNew.CreateField("COMPANY_ID" & IndexNumber, dbDouble)
Set fld = tblNew.CreateField("PERSON01" & IndexNumber, dbText)
Set fld = tblNew.CreateField("TITLE01" & IndexNumber, dbText)
Set fld = tblNew.CreateField("PERSON02" & IndexNumber, dbText)
Set fld = tblNew.CreateField("TITLE02" & IndexNumber, dbText)
tblNew.Fields.Append fld

For IndexNumber = 1 To FieldCount
  
'       Set field properties here if you want ie.
'       fld.Required = True
'
'       Append field to Fields collection
    tblNew.Fields.Append fld
Next IndexNumber
'   Append table to TableDef collection
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 Double, previousCompany_ID As Double

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

DoCmd.SetWarnings False
DoCmd.RunSQL ("Delete * from PERSONNEL")
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!PERSON01 = rs1!FULNM
        rs2!TITLE01 = rs1!TITLE
        rs2!PERSON02 = rs1!FULNM
        rs2!TITLE02 = rs1!TITLE
        rs2.Update
    Else
        FieldCount = FieldCount + 1
        rs2.MoveLast
        rs2.Edit
        rs2!Company_ID = rs1!Company_ID
        rs2!PERSON01 = rs1!FULNM
        rs2!TITLE01 = rs1!TITLE
        rs2!PERSON02 = rs1!FULNM
        rs2!TITLE02 = rs1!TITLE
        rs2.Update
    End If
    previousCompany_ID = currentCompany_ID
    rs1.MoveNext
Loop

End Sub

 

[MichaelRed]

A sugestion would be to post also what specific issues / problems you have with this approach.

Another would be to get the set of records to string out )e.g. denotmalize to the single record) differently:

Creat a unique query for JUST the comnpany_ID's. Use this as a parameter set for a second query which retrieves all of the records for the company_Id. Loop through this later query, appending the single record with the fields representing the individuals.

A last resort M I G H T be to attempt to excplain / teach / rationalize the power of a relational db to the end use. After all, there is at least ONE reasonable example to illustrate the issue in MS (Out look or OutLook Express),





MichaelRed

 

[cmmrfrds]

You should be able to accomplish with an SQL Statement(s). It will require SQL queries that can be combined or separated - your choice

An example using separate queries.
First create an arbitrary row number.
select
A.companyid as acompanyid,
A.personid,
count(*) as rownum
from Persons as A, Persons as B
where A.companyid = B.companyid
and A.personid <= B.personid
Group by A.companyid, A.personid
Typically, in Access this can be saved as a query.
For example. rowids.
Next Join this query to the Persons table and use IIF function to build the columns.

select
A.companyid as acompanyid,
A.personid,
(iff(rownum=1,A.personid,NULL)) as person01,
(iff(rownum=1,A.title,NULL)) as title01,
(iff(rownum=2,A.personid,NULL)) as person02,
(iff(rownum=2,A.title,NULL)) as title02,
(iff(rownum=3,A.personid,NULL)) as person03,
(iff(rownum=3,A.title,NULL)) as title03
from Persons as A
Inner Join rowids as B
ON A.companyid = B.companyid
and A.personid = B.personid

You could save this as a query. For example flatten,

Then, run a select into query.
Select into myflattentable from flatten.

This is untested code, but I have used this technique to flatten out data.

 

[cmmrfrds]

This part needs a little work to condense down to 1 record per company.

select
A.companyid as acompanyid,
max(iff(rownum=1,A.personid,NULL)) as person01,
max(iff(rownum=1,A.title,NULL)) as title01,
max(iff(rownum=2,A.personid,NULL)) as person02,
max(iff(rownum=2,A.title,NULL)) as title02,
max(iff(rownum=3,A.personid,NULL)) as person03,
max(iff(rownum=3,A.title,NULL)) as title03
from Persons as A
Inner Join rowids as B
ON A.companyid = B.companyid
and A.personid = B.personid
Group By A.companyid

 

[grande]

Why don't you create a query similiar to this

SELECT * FROM [All tables];

From there, you can get rid of everything you don't need.

-------------------------
Just call me Captain Awesome.