In Access, How do I Concatinate items in the same column?

[Guest]

I have an Oracle dB that i am connected to. There is a Table named
[remarks_Detail] . This is a sample of what the data looks like.

Remarks_ID Seq_No Remarks_txt

Tim001 1 Conc
Tim001 2 This
Tim001 3 Column
Joe001 1 New Sentence
Joe001 2 When ID Changes


I made the data simple, the remarks_txt column wraps to the next row when
the field reaches 255 characters. Keeping the same Remarks_Id but the Seq_No
goes increases. There are instances of only one seq_no per Remark_ID and
there are instances where the seq_No reaches 15.

This data from a diary entry program. We want to use access for a reporting
tool since the reports in the program are generic and cannot be modified.

 

[JohnFol]

I think you are only able to do this in code. On the OnFormat of the
report, you would need to open a recordset for the current remarks_ID and
loop round it building up a string from the remarks_txt value. Then, set an
unbound control on the report to be that string.

 

[John Vinson]

On Mon, 7 Mar 2005 22:09:02 -0800, "inspector tim" <inspector

I have an Oracle dB that i am connected to. There is a Table named
[remarks_Detail] . This is a sample of what the data looks like.

Remarks_ID Seq_No Remarks_txt

Tim001 1 Conc
Tim001 2 This
Tim001 3 Column
Joe001 1 New Sentence
Joe001 2 When ID Changes

As JohnFol says, you must use VBA code to do this. There's good sample
code you can use for free at

http://www.mvps.org/access/modules/mdl0004.htm

I'm puzzled that an Oracle database would be set up in this (*very*
old-fashioned!) way - Oracle has had long text fields for decades! But
if that's what you're stuck with, the mvps.org code should take care
of it for you. Post back if you need help with it.

John W. Vinson[MVP]

 

[Guest]

You might try another way. You will need two queries as
below. In DESIGN click on menu VIEW and then SQL View.
Paste the below SQL statements after you have edited them
to your table and field names. The second one needs to
contain the maximum number of Seq_No you have.

TRANSFORM First(Table1.Remarks_txt) AS FirstOfRemarks_txt
SELECT Table1.Remarks_ID
FROM Table1
GROUP BY Table1.Remarks_ID
PIVOT Table1.Seq_No;

SELECT Table1_Crosstab.Remarks_ID, Trim([1] & " " & [2]
& " " & [3]) AS [Text]
FROM Table1_Crosstab;