possibly a strange request for help

[Not Me]

Hi, I'm trying to create a field based on the concatenation of one field but
in different records (which can be grouped together)

an example may help!

id date info
1 3 middle
1 2 first
1 4 last
2 2 left
2 3 right

ok, so I want to create a field 'special' like this (in a different table if
that matters)

id special
1 first middle last
2 left right

so the info field is concatenated (based on date too, just to make life that
more difficult) into 'special'

Is this possible? I have a background in programming so could probably throw
some VB together to sort this, but it would be a whole lot simpler if
someone could advise on a bit of SQL to acheive the same result.

Many thanks,
Chris

[MacDermott]

AFAIK, there is no way to do this with SQL.
VB Coding would be the way.

HTH
- Turtle

"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bv**********@ucsnew1.ncl.ac.uk...

Hi, I'm trying to create a field based on the concatenation of one field but in different records (which can be grouped together)

an example may help!

id date info
1 3 middle
1 2 first
1 4 last
2 2 left
2 3 right

ok, so I want to create a field 'special' like this (in a different table if that matters)

id special
1 first middle last
2 left right

so the info field is concatenated (based on date too, just to make life that more difficult) into 'special'

Is this possible? I have a background in programming so could probably throw some VB together to sort this, but it would be a whole lot simpler if
someone could advise on a bit of SQL to acheive the same result.

Many thanks,
Chris

[DFS]

Not Me,

There's a very good chance you CAN do it in SQL, and easily, but I need a
better example of the output you want, using real dates and real values in
the info column.
"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bv**********@ucsnew1.ncl.ac.uk...

Hi, I'm trying to create a field based on the concatenation of one field but in different records (which can be grouped together)

an example may help!

id date info
1 3 middle
1 2 first
1 4 last
2 2 left
2 3 right

ok, so I want to create a field 'special' like this (in a different table if that matters)

id special
1 first middle last
2 left right

so the info field is concatenated (based on date too, just to make life that more difficult) into 'special'

Is this possible? I have a background in programming so could probably throw some VB together to sort this, but it would be a whole lot simpler if
someone could advise on a bit of SQL to acheive the same result.

Many thanks,
Chris

[Salad]

Not Me wrote:

Hi, I'm trying to create a field based on the concatenation of one field but
in different records (which can be grouped together)

an example may help!

id date info
1 3 middle
1 2 first
1 4 last
2 2 left
2 3 right

ok, so I want to create a field 'special' like this (in a different table if
that matters)

id special
1 first middle last
2 left right

so the info field is concatenated (based on date too, just to make life that
more difficult) into 'special'

Is this possible? I have a background in programming so could probably throw
some VB together to sort this, but it would be a whole lot simpler if
someone could advise on a bit of SQL to acheive the same result.

Many thanks,
Chris

First, create a function. Let's assume the table with the current data is
called Table1, and uses your field names.

Function ConCatString(lngID As Long) As String
Dim strSQL As String
Dim rst As DAO.Recordset

strSQL = "Select Info From Table1 Where ID = " & lngID & _
" And Table1.Info is not null Order By ID, Date"
Set rst = Currentdb.Openrecordset(strSQL,dbopensnapshot)
rst.MoveFirst
If rst.RecordCount > 0 then
Do while not rst.EOF
If Not IsNull rst!Info then _
ConCatString = ConCatString & rst!Info & space(1)
rst.MoveNext
Loop
Endif
rst.Close
set rst = Nothing
End Function

In the query builder, make this an expression. Ex: Name colon Value where
colon is a :
FullInfo : ConCatString([id])

This will return the full info string to the query. It excludes all records
where Info is blank (Null).

[Not Me]

"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bv**********@ucsnew1.ncl.ac.uk...

Hi, I'm trying to create a field based on the concatenation of one field but in different records (which can be grouped together)

Thanks to all for their help - I've got it sorted now :)

Cheers,
Chris