Solved

changing "vertical" data to "horizontal" data

Posted on 2012-04-11
15
507 Views
Last Modified: 2012-07-07
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
Comment
Question by:Nero83
15 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 37834922
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
 

Author Comment

by:Nero83
ID: 37835008
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 37835077
Is there a limit to the names at an address?

mlmcc
0
 

Author Comment

by:Nero83
ID: 37835373
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37835485
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
 

Author Comment

by:Nero83
ID: 37835614
@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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 100

Expert Comment

by:mlmcc
ID: 37835644
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
 

Author Comment

by:Nero83
ID: 37835664
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
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 37835852
Check this report

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

Expert Comment

by:Helen_Feddema
ID: 37838808
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
 

Author Comment

by:Nero83
ID: 37838910
@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
 

Author Comment

by:Nero83
ID: 38149863
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 38149864
Workarounds can be accepted as the solution or perhaps we should continue this discussion and reach a real solution

mlmcc
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now