Concatenate Text Field

[DBarker]

I have been trying to work this with code but I have been
getting very frustrated. This is the situation: I am
pulling multiple fields in from a live db into access.
Now there can be the same ID multiple times because it has
different journal (text) fields so I can not get around
that. So when it comes into access I want to create a
table from the data that compares the ID and possibly
another field and imports all the new records and
concatenates the date of journal and the journals text
into the table. Can anyone help with this?

 

[DBarker]

I have tried the append query but it adds new records
because the ID field is the primary and can not have
duplicates.

-----Original Message-----
The first thing you'll need to do is Link (not import)

the live DB table into your access database.

Then make an append (or maketable) query that does your

concatenation. Run the query, and you have your table.

 

[John Spencer (MVP)]

How about an append query that adds any new ID fields to your current table and
then an update query to concatenate any Journal values into your memo field?
There would be some problems with this if you accidentally tried to import the
same data twice.

First query to add any ID records that are not in the MainTable

INSERT INTO MainTable (ID )
SELECT ImportTable.ID
FROM MainTable RIGHT JOIN ImportTable
ON MainTable.ID = ImportTable.ID
WHERE MainTable.ID Is Null

SecondQuery to update the main table

UPDATE MainTable INNER JOIN ImportTable
ON MainTable.ID = ImportTable.ID
SET MainTable.Journal =
(MainTable.Journal + chr(13) + Chr(10)) & ImportTable.Journal
WHERE ImportTable.Journal Is Not Null

If there is a date field for the importTable, you could store that as a last
update field in the maintable. You could check that in your where clause and
keep from updating any record where the ImportDate is older than the Main table
field date. Something like

WHERE ImportTable.Journal Is Not Null
AND ImportTable.Importdate > MainTable.ImportDate

 

[DBarker]

I made two tables to test this out with only two fields
the primary and the journals and put the query below
together it says that it is updating 3 records but it
does not add anything to the Tableto table.?????

UPDATE Tableto INNER JOIN tablefrom ON Tableto.Case =
tablefrom.Case SET Tableto.Journal = (Tableto.Journal +
chr(13) + Chr (10)) & ":" & tablefrom.Journal
WHERE tablefrom.Journal Is Not Null;

-----Original Message-----
How about an append query that adds any new ID fields to your current table and
then an update query to concatenate any Journal values into your memo field?
There would be some problems with this if you

accidentally tried to import the

 

[John Spencer (MVP)]

Hmm, it should be.

Do you realize that the Chr(13) + Chr(10) is adding a line feed into the data?
That will require you to expand the queries row height to see the data on the
additional lines. If you are adding just a colon as a separator then change the
Set statement to

SET Tableto.Journal = (TableTo.Journal + ":") & TableFrom.Journal

This and my previous example use the fact that Access concatenates string values
with two different operators. And they work differently with nulls (empty fields).

Example:

Null + " is a string" will return Null

Null & " is a string" will return " is a string"

Null + ":" & " is a string" will return " is a string"

Null & ":" & " is a string" will return ": is a string"

 

[DBarker]

I created two simple tables to see if I can get this to
work a to table and a from table with two fields a case ID
and journal field. I made the case ids duplicates in both
tables to test this. I added the code you just posted and
it still did not work.



UPDATE tblTo INNER JOIN tblFrom ON tblTo.Case =
tblFrom.Case SET tblTo.Journal = (tblTo.Journal + ";") &
tblFrom.Journal
WHERE tblFrom.Journal Is Not Null;

 

[DBarker]

When I run the query it says it will update # rows and
looks like it works but when I open the table the data has
not changed !

 

[John Spencer (MVP)]

Ok, I built two tables, poputlated them with sample data, and then ran the
following query.

UPDATE tblto INNER JOIN tblFrom
ON tblTo.Case = tblFrom.Case
SET tblTo.Journal = [tblTo].[Journal]+": " & [tblFROM].[Journal]
WHERE (((tblFROM.Journal) Is Not Null));

It worked without any problem for me. Note I did test this is Access 97 only.
It is possible that CASE is being seen as a reserved word in your version of
Access. Try surrounding it with square brackets as above.

If this still fails for you, try posting back. When you do, please tell me what
Version of Access you are using and let me know if you are willing to send me a
zipped copy of the two test tables and your query. Perhaps I can spot something there.

 

[Guest]

I copied and pasted exactly what you had and made a new
query and it gives me a message that 6 records will be
updated and then it looks like it works, but when you open
the table nothing has changed. I am using Access 97.

Any help would be greatly appreciated.

Debbie

-----Original Message-----
Ok, I built two tables, poputlated them with sample data, and then ran the
following query.

UPDATE tblto INNER JOIN tblFrom
ON tblTo.Case = tblFrom.Case
SET tblTo.Journal = [tblTo].[Journal]+": " & [tblFROM]. [Journal]
WHERE (((tblFROM.Journal) Is Not Null));

It worked without any problem for me. Note I did test this is Access 97 only.
It is possible that CASE is being seen as a reserved word in your version of
Access. Try surrounding it with square brackets as above.

If this still fails for you, try posting back. When you do, please tell me what
Version of Access you are using and let me know if you are willing to send me a
zipped copy of the two test tables and your query.

Perhaps I can spot something there.