I am trying to convert address data 1

[DavidMark]

I am trying to convert address data within one table to a single record entry per address. The tables below show what I need to do:

ID001 Address Line 1
Address Line 2
Address Line 3
ID002 Address Line 1
Address Line 2
Address Line 3
ID003 Address Line 1
Address Line 2
Address Line 3

Convert to:-

ID001 Address Line1 Address Line2 Address Line3
ID002 Address Line1 Address Line2 Address Line3
ID003 Address Line1 Address Line2 Address Line3

Any ideas? Thanks in advance!

Dave

 

[OldTom]

Hi Dave

Try this

INSERT INTO TblB ( TblBId, Address )
SELECT TblA.TblAId, [TblA]![Addr1] & "XXX " & [TblA]![Addr2] & "XXX" & [TblA]![Addr3] AS Expr1
FROM TblA;

and replace "XXX" with whatever separator you want between the address lines.
e.g. vbcrlf
or
chr$(13) & chr$(10)
for a new line

Tom

 

[DavidMark]

Thanks for the reply Tom. I should have mentioned that the address lines on the original table are on different record lines and they are under the same field.

Dave

 

[OldTom]

I'm not quite sure what you mean by that Dave.

If you post the original table design structure and the data then I'll have amother look at it for you

Tom