changing "vertical" data to "horizontal" data

I have a recurring problem that is difficult for me to explain, but here it is:
As an example, I have a database with one table that contains house addresses and another table that contains owners' names, usually more than one per address.
I want to convert this to one table, with the address in one field and the owners' names in subsequent fields, all in one record.
In the past, I've managed to do this (in most cases), but it involves a lot of manipulation of the data and setting up multiple queries, etc.  It often takes a long time.
Is there a common/easier way to do this that anyone knows of?  I typically use MS Access, Crystal Reports, and/or Excel to do my data gathering.
Hope this makes sense.
Thanks, Experts!!!
Nero83Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
YOu need to build a query that gatherrs the data you want from the tables.  That view can then be used to update or create the new table.

If you had 2 tables like
Names and Addresses

Names
   PersonKey
   FirstName
   LastName

Addresses
   AddressKey
   fkPersonKey
   AddressLine1
   AddressLine2
   AddressLine3
   City
   State
   Zip

A query like

SELECT Names.FirstName, Names.LastName, Addresses.AddressLine1, Addresses.AddressLine2, Addresses.AddressLine3, Address.City, Address.State, Address.Zip
FROM Names INNER JOIN Addrresses
ON Names.PersonKey = Address.fkPersonKey

If a Name can be exist without an address then use LEFT OUTER JOIN

mlmcc
0
Nero83Author Commented:
Understood, thanks.
However, in my situation, the Names table has an address key.  There are multiple names per address, and I am looking to have each of those names in it's own field called, say, Name1, Name2...

Addkey      Address
12345        999 Main St.

fkAddkey   name
12345         joe smith
12345         mary smith

and i want it to come out:

key        address              name1          name2
12345   999 Main St.       joe smith      mary smith

does this make sense?  In some cases I don't even have the names numbered in their table to differentiate them in any way.
0
mlmccCommented:
Is there a limit to the names at an address?

mlmcc
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Nero83Author Commented:
Well, it can be limtless in terms of the database but in reality it rarely exceeds six, and it's acceptable for me to artifically limit it to, say, four names.
0
Jeffrey CoachmanMIS LiasonCommented:
If you "Convert" this to the one row format you are specifying, you will have an un-normalized structure.

Is this conversion for reporting or some other specific purpose?
Perhaps you could explain why you need this, in case the same result can be obtained in a different way?

The data should really remain in the current structure IMHO
0
Nero83Author Commented:
@boag2000 this is usually for delivering data for mailings/reporting.  I'm not sure what un-normalized means, but it's not for further database use or anything - I send off this data and never use it again.
0
mlmccCommented:
What format do you need it in?
A CSV, text file, a printed report, Excel?

It would be relatively easy in a report like Crystal

mlmcc
0
Nero83Author Commented:
My current need is going to excel but it would be nicest for me to have a method in Crystal so I could run it anytime and export it...I use Crystal often.  Any method would be great!
0
mlmccCommented:
Check this report

mlmcc
Q-27671572.rpt
Q-27671572.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Helen FeddemaCommented:
See my Access Archon articles on creating a denormalized table :

http://www.helenfeddema.com/Files/accarch43.zip
http://www.helenfeddema.com/Files/accarch125.zip
0
Nero83Author Commented:
@mlmcc: Thanks!  That's an interesting solution.  
I was looking for the names being put to their own fields, but I suppose I can use the formulas you provided to insert delimeters that I can export and reimport to separate into fields...?
0
Nero83Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Nero83's comment #37838910

for the following reason:

it was an ok workaround, but not a solution exactly.  appreciated the effort, though.
0
mlmccCommented:
Workarounds can be accepted as the solution or perhaps we should continue this discussion and reach a real solution

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.