convert row to column

[access23]

i have a table contain customer name and address like this:
name address
john doe street
john doe city
john doe state
john doe zip

how do i make the table look like this:

name address1 address2 address3 ...
john doe street city state ...

 

[jrbarnett]

Hi,

If you go into the crosstab query wizard, give it this table, put the name column vertically and the address column as the data it should work.

John

 

[access23]

Thank for the tip, i tried the crosstab query but it did not give me the result as i want above. Do you have any other suggestions?

new to access

 

[WildHare]

This may sound like a smart-a** response, but your best bet is to dump the table and start over correctly. If you have three or four or five records for EVERY PERSON in your table, (which is what your example appears to show) that's a bad design to start with.

The only other way is to design a CORRECT table, then use an APPEND query to put the initial records in, and then a couple of UPDATE queries to populate the other fields, based on a name match.

Jim


Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[GingerR]

if you want to try this hack-job:

you have to make another column/field in your table, and put in it

Street
City
Zip
City
Street
City
Zip

blah
for every row.
then do a crosstab query with this new field as ColumnHeading and Name as Row Heading.

 

[access23]

To WildHare (MIS)
Can you tell me how to run update queries to populate the others fields base on name match? Please
thanks,

 

[access23]

To GingerR (MIS)

I tried and it's worked. the only problem is i have over 10,000 records to go through. It's great to know the trick. Thanks alot.

 

[WildHare]

OK, but let's make sure we're both on the same page first.
Your original table has a record like this:

Name: Address:
John Doe 123 Main Street

and then another record like this
Name: Address:
John Doe Podunk

and then another one like this

Name: Address
John Doe Iowa

e.g., four or so records for every person. Correct?

And your NEW table has a record like this:

Name Street City State Zip
John Doe 123 Main St Podunk Iowa 00000

Right?





Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[access23]

yes

 

[WildHare]

Hi Access23 - I have gotten ext. busy all of a sudden - but I will try to look at your situ. later this week if you don't get a solution ahead of time. Sorry for the delay.

JMH

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[access23]

Hello, WildHare No problem. Whenever you have time. thanks!