Merging multiple rows into one with multiple columns

  • Thread starter Thread starter Zattack
  • Start date Start date
Z

Zattack

I have two tables of data for example sake. One has addresses the other has
names. Both have key fields that are used to join the data from the two
tables. The relationship is 1 to many in that multiple names can exist for
each address. If I run a simple query to get names for each address, I will
recieve duplicate rows of the address data for each name. What I want to do
is have one row for each address that then lists multiple columns for each
name, however many that may be. Any suggestions?
 
The code makes sense in a simple terms (I understand it somewhat but am by
no means a programmer), provided you have only one field for each table.
However what if I were to make it more complex and wanted to have more than
one set of fields related to both adresses (say street name, city, state and
zip) and to names (say first and last name) be the fields listed as the end
result. Would I have to first concatenate the names, run the denomalizer
into the new table and then query the new table with all the address data?
Ultimate goal:

Addresses Table
ID
Street
City
State
Zip

Names Table
ID
First Name
Last Name

Result Data set
ID
Street
City
State
Zip
First Name1
Last Name1
First Name2
Last Name2
Etc

Is there a way to tweak that code to do this and perhaps I missed that? Is
there another suggestion?

Thanks for your help.

Zack
 
IIRC, the code in the sample database tranverse the Source Table (using
Recordsets) and then write denormalised Records to the destination Table.
Most likely, the code can be modified to include a second Field in the
denormalising process.
 
Back
Top