combining text into one record?

[Gustavson]

Using Access 97 and working with a query...
Is there a way to combine text from multiple records into one record?

For example:

Records in Field: Combined records in another Field:
nw corner nw corner 1st floor in cabinet
1st floor
in cabinet

Thanx for your feedback
gustavson

 

[UncleJack]

Depends on what records you want to combine and a host of other variables that you didn't specify. Generally you can concatenate records by simply joining them thusly:

[Field1] & " " & [Field2]

The square brackets tell Access to take the contents of the field enclosed in brackets. Use the ampersand in expressions to indicate joins rather than the + mark which should only be used to indicate addition. In the above example, Field1 is joined to Field2 with a space between the records. So if [Field1] holds the value "desk" and [Field2] holds the value "near the wall" the resulting expression will be "desk near the wall"

Uncle Jack

 

[smedvid]

From what I see, it appears that you have 3 seperate rows of data that you wish to combine into a single row. Off the top of my head I can not think of a query that alone can accomplish what u want. Perhaps a self-join and union may be an approach, but my recommendation is to write a simple loop to read through the data table and load a temporary report table. htwh

Steve Medvid
"IT Consultant & Web Master"

http://users.erols.com/medvid/

 

[Gustavson]

smedvid - yes you are correct. I have three records in a field which consists of (nw corner, 1st floor, in cabinet), and I would like to be able to combine these records into one record in another field (nw corner 1st floor in cabinet). I have no idea how this is done and it sounds like it can't be done. I will look into the loop thing.
thankyou

UncleJack - I like your feedback but the only thing is I'm not joining records from two seperate fields but from multiple records in one field...
Thankyou

Thanx for the info
gustavson

 

[UncleJack]

I don't think you can do it with a query since I can't think of a way to limit a query's scope to just three lines. However, you might be able to do it by using DAO. The following should read in three locations from the input line number and place the concatenated value in a different field of the third record and assumes the data is in three sequential records:

Call the routine by specifying the starting record number in the call, e.g. Join(127)

Sub Join (StartLine as integer)
Dim DB as Database
Dim RS as Recordset
Dim F1 as string, F2 as String, F3 as String
Set DB = CurrentDB
Set RS = DB.OpenRecordset("DataTable&quot

With RS
.Index = "PrimaryKey" 'Assuming the line number is primary
.Seek "=", StartLine 'Move pointer to starting line
F1 = !Location 'Name of field in table
.MoveNext
F2 = !Location
.MoveNext
F3 = !Location
.Edit
!CombinedField = F1 & " " & F2 & " " & F3
.Update
.Close
End With

Be careful however, since DAO will often refer to lines in the table in their native format (order in which entered) and not in their indexed and visible form. Sometimes the two are not the same and can result in difficult debugging. If this happens you might have to change the .MoveNext to another seek operation where you increment the line number directly.

Uncle Jack