By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

possibly a strange request for help

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

P: n/a
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).
Nov 12 '05 #4

P: n/a
"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
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.