Need to flatten data

[lfarina]

Hello,

I have a table that contains contact names, their titles, and company_IDs th
at 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 tried doing different query types but can't get anything to work. Is this something I should be using VB code for? I would appreciate any advice.

 

[Pat Hartman]

What is your objective? Are you trying to do this for a report? Do you need to export a flat file? How would you decide how many occurances to flatten?

 

[workmad3]

Its something I need to do as well. I'm going to look at using nested SQL statements. If I get something that works, I will post it up later today.

 

[workmad3]

Bah, access has resisited all my efforts to do this A nested SQL statment can only return one value, a lookup just doesnt work. Any attempt I make at programatically flattening the data only does so for the first row on a datasheet and then copies it down for the rest of it.

Its getting annoying. Currently just using the main contact name in the view to give them at least some idea as to who they could be looking for.

 

[lfarina]

What I have to do is import dbf files, massage the data, and then export them out again as an xls or dbf. I have a Main table that is imported that contains the company_id, address, city, etc. There is one record per company_id in this table. Then I import another table called Person which contains only the company_id, contact name, and contact title. There are several records per company_id in this table. There could be upwards of 100 contact names for one company_id. I then combine both of these tables into a new table using the company_id as the Primary Key. Most of our customers request that we flatten the personnel into the Main table so there are no duplicate company_ids and all the data for a company is in one record. That's why I have to figure this out. Right now my company uses an old FoxPro in-house written program to flatten the personnel file. I am trying to convert us to Access and Excel for work order purposes. I hope I didn't give too much detail.

 

[workmad3]

I think I can see how to solve it for your problem there. Although in that situation, I would be tempted to call the customers some very bad names, as they are asking for data that is completely unnormalized and is prone to all sorts of update errors. However, I digress:

here is a basic pseudo code algorithm for what you are looking for(i think)

Get list of all company ids

for each company id{
write company id into excel sheet
write company details into excel sheet
get all contact names for company id
write each contact name into excel sheet
}

Depending on which technology you use to get information from the db, and transfer to excel changes the implementation.

Hope that helps a bit though

 

[lfarina]

Thanks workmad3. I don't really need to automate the exporting part. I just need to make a new table so I can combine it with the Main table and then export that out. So I guess I don't need to write anything into an execl sheet, I guess?

 

[workmad3]

No, the only problem then is how many fields to make in the main table in order to hold all your data. That was why I was suggesting skipping the saving to the db part and going straight to a flat file format for the flattened data.

If you need to keep it all in the database, then add something like the following onto the front of the code:

execute a query like the following(not entirely sure if it will let it go exactly as it is here): SELECT MAX(SELECT DISTINCT COUNT(companyid) FROM ContactNames)

construct a make table query that creates a table, probably something like this:

query as string, i as integer
query = "CREATE TABLE flatcontacts (companyid"
for i = 1 to rs.fields("max")
query = query & ", contact" & i
next i
query = query & ")"
execute query

which will create a table with enough space for each of the contacts for all of the companies. Then, all you essentially do is transpose the data from the vertical view with many companyids to a horizontal one that fits in the above table. Merging the tables then is easy, as you have all the names next to one id.

Still dislike this, as it is un normailizing the data, but its helping me think about my problem.

 

[Pat Hartman]

Keep in mind that the maximum number of columns for a table is 255. For 100 contacts with their titles, you are already at 200 columns and that is assuming you have already flattened last, first, middle, etc into a single column.

If I had to do this, I wouldn't take the chance of doing it with tables. Since I have to write code to do this anyway, I would write the code to build the spreadsheet directly.

Just a hint - run a query that figures out the maximimum number of contacts for any company. Use that number to populate the column headings.