October 20, 2009 at 10:14 am
Hi, how would I write a query where I concatenate the data from each row into one string
ie,
col1
row1: 'a'
row2: 'b'
row3: 'c'
desired result: 'abc'
thank you in advance.
October 20, 2009 at 10:33 am
Hi, if I were to do this in MSSQL I would use the following script, would this work in Access?
CREATE TABLE Colors
(
Color VARCHAR(32)
)
GO
SET NOCOUNT ON
INSERT Colors SELECT 'red'
INSERT Colors SELECT 'orange'
INSERT Colors SELECT 'blue'
INSERT Colors SELECT 'green'
GO
DECLARE @colors VARCHAR(1024)
SELECT
@colors = COALESCE(@colors + '', '') + Color
FROM
Colors
SELECT Colors = @colors
GO
DROP TABLE Colors
GO
October 27, 2009 at 1:12 pm
Marcus Farrugia (10/20/2009)
Hi, how would I write a query where I concatenate the data from each row into one stringie,
col1
row1: 'a'
row2: 'b'
row3: 'c'
desired result: 'abc'
thank you in advance.
Do you have an example of the table structure and data we can look at?
I have an example of a Concatenate function that I can modify if you can give me some more information.
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
October 28, 2009 at 5:08 am
In Access, write your query like this:
SELECT [Field1] & [Field2] & [Field3] AS Result FROM [Table]
Have a nice day!
October 28, 2009 at 6:43 am
Do you have an example of the table structure and data we can look at?
Hi Doug, don't really have a table structure, it would be from one column - varchar datatype...so
pk co1
1 a
2 b
3 c
desired result: abc
October 28, 2009 at 6:45 am
In Access, write your query like this:
SELECT [Field1] & [Field2] & [Field3] AS Result FROM [Table]
Hi rf44...by [Field1] & [Field2] & [Field3] do you mean column headings?
I'm actually looking to concatenate records in one column...
October 28, 2009 at 10:51 am
Marcus Farrugia (10/28/2009)
Do you have an example of the table structure and data we can look at?
Hi Doug, don't really have a table structure, it would be from one column - varchar datatype...so
pk co1
1 a
2 b
3 c
desired result: abc
Are you saying that you have a table with a field in each record
that
has
data
like
this
and you want to display the data in the field like this?
thathasdatalikethis
or
you have a table of records and you want to concatenate ALL the records into one String?
Here is a function that will handle the case where you want to concatenate all records into one long string:
Add this to a code module.
You may have to add a reference to Microsoft DAO 3.6 Object Library
After adding to module, open immediate window and enter "? ConcatColumn()" without the quotes.
''' Code here
Function ConcatColumn() As String
'Takes an SSN and looks up the start and end dates
'Concatenates them and returns a string
Dim sOut As String 'Output string
Dim sSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
sSQL = "Select [Co1] From YourTableNameHere Order by [pk]"
Set db = CurrentDb
Set rst = db.openrecordset(sSQL, dbOpenSnapshot)
sOut = ""
With rst
Do While Not .BOF And Not .EOF
sOut = sOut & ![Co1]
.MoveNext
Loop
End With
ConcatColumn = sOut
Set rst = Nothing
Set db = Nothing
End Function
'' Code ends here
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
November 5, 2009 at 1:45 pm
Were you able to solve your problem?
I have a sample .MDB that I got online some time ago that shows how to concatenate rows of data.
If you want I can email it to you.
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
November 17, 2009 at 9:36 am
Thanks DougGifford,
I don't if it helped the other guy, but I know it helped me. It was just what I was looking for.
So Thanks very much. I logged in just to reply that it helped and to ask a couple of extra questions if I may?
1 - How do you output the result into a field, say a record of who was emailed (when concatenating email addresses) and also pass the result into an email Bcc: as recipients from within Access in order to send an email to multiple recipients?
You see I don't know how to get access to use the result as an entry into another field or cell of a form.
2 - How do you get this line
sSQL = "Select [Co1] From YourTableNameHere Order by [pk]"
to accept a variables for "[Co1]" and "YourTableNameHere" and "[pk]"?
P.S. I wrote sOut = sOut & ![Email_Address] & "; " - so that the output is comma seperated.
Regards,
Masoom
November 17, 2009 at 11:52 am
Helen Feddema has written several books on Access Automation and she has a great web site with her articles and code samples.
Try going to this link and then search for EMail.
Also search for her article on Concatenating.
http://www.helenfeddema.com/access.htm
Hope this helps.
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
November 17, 2009 at 11:58 am
Another great web site with "How To" examples is: http://www.rogersaccesslibrary.com/index.htm
Also check out the "Other Developer Libraries" link on his home page.
If you have questions on How To do something in ACCESS, these (and Helen's) sites are great places for answers.
Good luck
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
November 19, 2009 at 4:45 am
Thanks, I'll check it out.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply