Solved

changing "vertical" data to "horizontal" data

Posted on 2012-04-11
15
514 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 101

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 101

Expert Comment

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

mlmcc
0
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

 

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
 
LVL 101

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 101

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 101

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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 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