By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,712 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Denormalize Data?

P: n/a
Hello,

I have a table that contains contact names, their titles, and
company_IDs that are used as the Primary Key. There are multiple
contact names and titles per each company_ID. I need to have them all
listed on one row per company_id. Right now it looks like this:

Company_ID Person Title
12345 Bob Smith VP
12345 Pam Rollins President

I need it to be like this:

Company_ID Person01 Title01 Person02 Title02
12345 Bob Smith VP Pam Rollins President

I got it to work using only the contact name (Person) by doing a
make-table query and using Pers: "Person" &
Format(GroupIncrement([company_id]),"00"). GroupIncrement being the
following routine:

Option Compare Database
Option Explicit

Dim lngCompany_ID As Long
Dim lngGroupIncrement As Long

Public Function GroupIncrement(Company_ID As Long) As Long
If Company_ID = lngCompany_ID Then
lngGroupIncrement = lngGroupIncrement + 1
Else
lngGroupIncrement = 1
lngCompany_ID = Company_ID
End If
GroupIncrement = lngGroupIncrement

End Function

This gives me the Person01, 02, 03, etc. part that I want, but I can't
seem to figure out how to add the Title01 to it. Any ideas? I would
appreciate any advice.

Nov 17 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
One of the ways I've found to do this is to use a temporary table. You have
to have enough fields in the table to handle the greatest number of records
per company in the current table. You would then run a query on the current
table, sorting on the Company_ID field to group them together. Using DAO,
you would loop through a recordset based on the query. The first record for
a company would go into Person01 and Title01 of the temporary table. The
second record would go into Person02 and Title02, etc. When the Company_ID
field changes values, you would start again at Person01.

As long as you name your field names in the temporary table as you have
above, you can use a counter in a loop to give you the field name. The first
record you add after encountering a new Company_ID will be and AddNew
operation, subsequent records for the same Company_ID will be an Edit
operation.

--
Wayne Morgan
MS Access MVP
"Farina" <lf*****@csgis.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Hello,

I have a table that contains contact names, their titles, and
company_IDs that are used as the Primary Key. There are multiple
contact names and titles per each company_ID. I need to have them all
listed on one row per company_id. Right now it looks like this:

Company_ID Person Title
12345 Bob Smith VP
12345 Pam Rollins President

I need it to be like this:

Company_ID Person01 Title01 Person02 Title02
12345 Bob Smith VP Pam Rollins President

I got it to work using only the contact name (Person) by doing a
make-table query and using Pers: "Person" &
Format(GroupIncrement([company_id]),"00"). GroupIncrement being the
following routine:

Option Compare Database
Option Explicit

Dim lngCompany_ID As Long
Dim lngGroupIncrement As Long

Public Function GroupIncrement(Company_ID As Long) As Long
If Company_ID = lngCompany_ID Then
lngGroupIncrement = lngGroupIncrement + 1
Else
lngGroupIncrement = 1
lngCompany_ID = Company_ID
End If
GroupIncrement = lngGroupIncrement

End Function

This gives me the Person01, 02, 03, etc. part that I want, but I can't
seem to figure out how to add the Title01 to it. Any ideas? I would
appreciate any advice.

Nov 18 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.