Multiple Records with UniqID into one record

[peymand]

Hi,

Could you show me how to convert records (in the example below)with the same HHID into one long record?

HHId Person FName SName Age
10 1 Fred Smith 75
10 2 John Smith 35
11 1 Mark Jones 46
12 1 Joe Bloggs 54
12 1 Nancy Bloggs 51
12 1 Sarah Bloggs 14
12 1 Sandra Bloggs 24

 

[Crowley16]

how do you mean 1 record?

--------------------
Procrastinate Now!

 

[peymand]

I meant a HHold level record..... fro example:

HHId Person1 FName1 SName1 Age1 Person2 FName2 SName2 Age2

Thanks

 

[Crowley16]

you can't do this easily, unless you know beforehand the number of persons to put on 1 hhid...

you could try to fake this on a report by messing with the text directions and column/row settings I suppose...

--------------------
Procrastinate Now!

 

[PHV]

Have a look here:

http://www.tek-tips.com/faqs.cfm?fid=4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[peymand]

Thank you for coming back to me. You are right to suggest that I'll need to know the total number of persons in each household to have it easy, however, just in case I missed anything, this is a clarification,

I'm hoping to create a Household Table using a query which processes my existing table of 400K person-level records (up to 14 per household)

I spotted one mistake in my earlier example, so copied it again here correctly.

HHId Person FName SName Age
10 1 Fred Smith 75
10 2 John Smith 35
11 1 Mark Jones 46
12 1 Joe Bloggs 54
12 2 Nancy Bloggs 51
12 3 Sarah Bloggs 14
12 4 Sandra Bloggs 24

Kind regards

 

[lespaul]

again, why do you want to denormalize your table?

Is this for a report or are you going to use the "new" table in your database for some reason?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[peymand]

Yes, It is for a word mailmerge. The household addresses are in a separate file that also contain HHId.

 

[lespaul]

so really what you want is to take information from two or more tables and merge it into a word document.

So you have this information in a table:

HHId Person FName SName Age
10 1 Fred Smith 75
10 2 John Smith 35
11 1 Mark Jones 46
12 1 Joe Bloggs 54
12 1 Nancy Bloggs 51
12 1 Sarah Bloggs 14
12 1 Sandra Bloggs 24

what is the other information you need?

How do you want the information displayed in the word document?




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design