Concatenation Help

[access09]

I have table in an Access db that looks like below:

There are two fields - Cover number and address

Cover Number: Address:
0012345 8 Big House
0012345 Boston
0012345 USA
1123400 22 Small House
1123400 Chicago
1123400 USA
0055443 4 Medium House
0055443 Paris
0055443 France
......
.....
....

What I want to do is create a query or alter the table so that the output leaves me with no repetition of the cover number and the address all on one line between the two fields. I.E. The address was spread out over multiple rows but I just want to have it on one row. Just like below:

Cover Number: Address:
0012345 8 Big House Boston USA
1123400 22 Small House Chicago USA
0055443 4 Medium House Paris France

Does anybody know of a way I could approach this?

 

[DCrake]

How did the data get like that in the first place?

 

[access09]

That is how the data from extracted from the database. The field had a character limitation so it was spread out over several rows.

 

[DCrake]

Can you not simply repeat the exercise using a revised field width to manange the incoming data. Where is the sourcer data coming from originally?

David

 

[access09]

This is the way the address was typed into the old database. I have to work with the data the way it is.

 

[DCrake]

Public Function ParseAddresses()

Dim RsOld As DAO.Recordset
Dim RsNew As DAO.Recordset
Dim StrCode As String
Dim StrAddress As String

Set RsOld = CurrentDb.OpenRecordset("[B]TblSource[/B]")
Set RsNew = CurrentDb.OpenRecordset("[B]TblTarget[/B]")



Do Until RsOld.EOF
   StrCode = RsOld("[B]Cover Number[/B]")
   StrAddress = StrAdddress & " " & RsOld("[B]Address[/B]")
   RsOld.MoveNext
   If StrCode <> RsOld("[B]Cover Number[/B]") Then
      RsNew.AddNew
      RsNew("[B]Cover Number[/B]") = StrCode
      RsNew("[B]Address[/B]") = StrAddress
      RsNew.Update
      StrAddress = ""
   End If
Loop
RsOld.Close
RsNew.Close
Set RsOld = Nothing
Set RsNew = Nothing

End Function

Remember to use your own names in the bold items.

Save this function to a module and call from the immediate window.

This will get you what you want however addresses are really best split into different address line fields.


David

 

[Atomic Shrimp]

Assuming:

-- There are only ever three (no more, no fewer) lines for each address
-- The records arrive in the right order
-- When you import the table, you allow Access to add an autonumber primary key

This query will do it:

SELECT [YourTableName].[Address] & ", " & [YourTableName_1].[Address] & ", " & [YourTableName_2].[Address] AS Expr4
FROM (YourTableName
INNER JOIN YourTableName AS YourTableName_1 ON YourTableName.[cover number] = YourTableName_1.[cover number]) 
INNER JOIN YourTableName AS YourTableName_2 ON YourTableName.[cover number] = YourTableName_2.[cover number]
WHERE (((CLng([YourTableName].[ID]+2)/3)=Int(CLng([YourTableName].[ID]+2)/3)) 
AND ((CLng([YourTableName_1].[ID]+1)/3)=Int(CLng([YourTableName_1].[ID]+1)/3)) 
AND ((CLng([YourTableName_2].[ID])/3)=Int(CLng([YourTableName_2].[ID])/3)));

(in this example, the imported table is called 'YourTableName', ID is the autonumber PK access added)

How it works:

It uses the same table three times, joining the table and its two aliases by the non-unique Cover Number column. It selects the last line of the address from every row where ID is divisible by 3, it selects the middle line from every row where ID is 1 short of being divisible by 3 and the first line from every row where ID is 2 short of being divisible by 3.

DCrake's method is safer than mine - particularly if there is any chance of groups of other than 3 related records.

 

[access09]

Thanks that looks great. How do I call it in the immediate window though? Where is the immediate window in MS Access 07?

Thanks A. Shrimp but the number of address lines does vary. From one line to 6 lines.

 

[DCrake]

Press Ctrl+G to open up the immediate window

Then type in
?ParseAddresses

 

[access09]

Thanks. When I run it it throws up an error. See red below.

Public Function ParseAddresses()

Dim RsOld As DAO.Recordset
Dim RsNew As DAO.Recordset
Dim StrCode As String
Dim StrAddress As String

Set RsOld = CurrentDb.OpenRecordset("TblSource")
Set RsNew = CurrentDb.OpenRecordset("TblTarget")



Do Until RsOld.EOF
StrCode = RsOld("Cover Number")
StrAddress = StrAdddress & " " & RsOld("Address")
RsOld.MoveNext
If StrCode <> RsOld("Cover Number") Then
RsNew.AddNew
RsNew("Cover Number") = StrCode
RsNew("Address") = StrAddress
RsNew.Update
StrAddress = ""
End If
Loop
RsOld.Close
RsNew.Close
Set RsOld = Nothing
Set RsNew = Nothing

End Function

Its says 'no current record found'.

When I go into the risk table only the last line of an the address displays for a cover number.

Any ideas?

 

[DCrake]

That happens when you reach the end of the table. If you insert the following above that line

If RsOld.EOF Then Exit Do

This was all air code and untested. Donlt know why only the last part of the address is being saved. Can you supply copies of said tables for testing?

David