• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 527
  • Last Modified:

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!!!
0
Nero83
Asked:
Nero83
1 Solution
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 CoachmanCommented:
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
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now