Appending Memo Field

  • Thread starter Thread starter DBarker
  • Start date Start date
D

DBarker

I have a table that has multiple note fields for the same
case. I am trying to get the note fields which are memo
fields to concatenate into one field. I tried a Union
query but they don't work with Memo fields. I tried
appending but that did not work. Any help would be
appreciated.

Debbie
 
Dear Debbie:

Appending is probably the way to go. Would you please post the SQL of
your attempt to do this. Perhaps someone will be able to see a
problem with this and figure out why it failed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
INSERT INTO [STi Table] ( Journals )
SELECT STi.Journals
FROM STi LEFT JOIN [STi Table] ON STi.CaseID = [STi
Table].[Case#];

Above is the code from the append query. I have multiple
cases in the STi table and I want to append the journals
to the STi Table where the case #'s are the same.

Thanks,
Debbie
 
Dear Debbie:

When you initially said, "I am trying to get the note fields which are
memo fields to concatenate into one field" I thought that meant you
were using the concatenation operator '&' to concatenate them. An
append query doesn't do anything like concatenating strings.

If you have two columns, ColumnA and ColumnB, whose string values you
wish to concatenate into a new column, ColumnX, you would do something
like:

UPDATE STiJournals
SET ColumnX = ColumnA & ColumnB

If ColumnA and/or ColumnB might be NULL, the '+' operator may be
better than '&' for this.

An append query is used to append rows to a table, not to append
information between the columns in the table. This doesn't sound to
me like what you describe.

Sorry if I've not correctly interpreted your problem. Please let me
know.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


INSERT INTO [STi Table] ( Journals )
SELECT STi.Journals
FROM STi LEFT JOIN [STi Table] ON STi.CaseID = [STi
Table].[Case#];

Above is the code from the append query. I have multiple
cases in the STi table and I want to append the journals
to the STi Table where the case #'s are the same.

Thanks,
Debbie
-----Original Message-----
Dear Debbie:

Appending is probably the way to go. Would you please post the SQL of
your attempt to do this. Perhaps someone will be able to see a
problem with this and figure out why it failed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts




.
Click to expand...
Click to expand...
 
First this is what i have. Actually I created a query to
find the multiple fields within the table. So what I am
looking to do, is where the case# in one table is equal
to the case # in the other table I need to append the
Journal field into the STi Table.

Does that make sense?
-----Original Message-----
Dear Debbie:

When you initially said, "I am trying to get the note fields which are
memo fields to concatenate into one field" I thought that meant you
were using the concatenation operator '&' to concatenate them. An
append query doesn't do anything like concatenating strings.

If you have two columns, ColumnA and ColumnB, whose string values you
wish to concatenate into a new column, ColumnX, you would do something
like:

UPDATE STiJournals
SET ColumnX = ColumnA & ColumnB

If ColumnA and/or ColumnB might be NULL, the '+' operator may be
better than '&' for this.

An append query is used to append rows to a table, not to append
information between the columns in the table. This doesn't sound to
me like what you describe.

Sorry if I've not correctly interpreted your problem. Please let me
know.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


INSERT INTO [STi Table] ( Journals )
SELECT STi.Journals
FROM STi LEFT JOIN [STi Table] ON STi.CaseID = [STi
Table].[Case#];

Above is the code from the append query. I have multiple
cases in the STi table and I want to append the journals
to the STi Table where the case #'s are the same.

Thanks,
Debbie
-----Original Message-----
Dear Debbie:

Appending is probably the way to go. Would you please post the SQL of
your attempt to do this. Perhaps someone will be able to see a
problem with this and figure out why it failed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 1 Mar 2004 13:43:15 -0800, "DBarker"

I have a table that has multiple note fields for the same
case. I am trying to get the note fields which are memo
fields to concatenate into one field. I tried a Union
query but they don't work with Memo fields. I tried
appending but that did not work. Any help would be
appreciated.

Debbie

.
Click to expand...
Click to expand...

.
Click to expand...
 
Dear Debbie:

A bit confusing, I'm afraid, but I think I have it now.

Your use of "multiple fields within the table" give me a picture of
there being several columns (fields) containing the information you
want to concatenate. However, it now sounds as though you have only
one column (field) in which this data resides, but you have different
values in different rows, and you want to concatenate them.

This is not at all trivial.

The first thing I would recommend doing is to create a new table to
receive the results. This will guarantee the preservation of the
existing data until the process of getting the changes made has been
completed.

Next, some analysis of the existing data is in order. One aspect of
this is to determine some order in which the concatenated column will
be build from its current component parts. Obviously, for each
resulting row, one of the existing components will be placed in the
new memo column first, then another will be appended, then another,
until all have been inserted. Whether you like it or not, you are
going to have to define this order to be able to accomplish the task.
So, your task would be to define some attribute(s) of the existing
rows that uniquely identify them within each group, a group being a
set of rows with the same case#.

In separate action queries, we are going to insert all the "first"
instances of a case#, then update those rows for the "second"
instances of the same case#s, then the "third" and so on.

From this, you can see that another product of our analysis will be to
determine the maximum number of existing rows you have for the same
case#.

A final product of the analysis will be to determine whether the
longest resulting memo will exceed the maximum allowed memo length.
This would cause the whole thing to eventually fail.

I'll end this response here, without beginning the details of the task
before us. I want you to understand this much, and agree to it,
before we continue with the details of each step.

Does this now sound like what you have intended?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


First this is what i have. Actually I created a query to
find the multiple fields within the table. So what I am
looking to do, is where the case# in one table is equal
to the case # in the other table I need to append the
Journal field into the STi Table.

Does that make sense?
-----Original Message-----
Dear Debbie:

When you initially said, "I am trying to get the note fields which are
memo fields to concatenate into one field" I thought that meant you
were using the concatenation operator '&' to concatenate them. An
append query doesn't do anything like concatenating strings.

If you have two columns, ColumnA and ColumnB, whose string values you
wish to concatenate into a new column, ColumnX, you would do something
like:

UPDATE STiJournals
SET ColumnX = ColumnA & ColumnB

If ColumnA and/or ColumnB might be NULL, the '+' operator may be
better than '&' for this.

An append query is used to append rows to a table, not to append
information between the columns in the table. This doesn't sound to
me like what you describe.

Sorry if I've not correctly interpreted your problem. Please let me
know.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


INSERT INTO [STi Table] ( Journals )
SELECT STi.Journals
FROM STi LEFT JOIN [STi Table] ON STi.CaseID = [STi
Table].[Case#];

Above is the code from the append query. I have multiple
cases in the STi table and I want to append the journals
to the STi Table where the case #'s are the same.

Thanks,
Debbie
-----Original Message-----
Dear Debbie:

Appending is probably the way to go. Would you please
post the SQL of
your attempt to do this. Perhaps someone will be able
to see a
problem with this and figure out why it failed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 1 Mar 2004 13:43:15 -0800, "DBarker"

I have a table that has multiple note fields for the
same
case. I am trying to get the note fields which are
memo
fields to concatenate into one field. I tried a Union
query but they don't work with Memo fields. I tried
appending but that did not work. Any help would be
appreciated.

Debbie

.
Click to expand...

.
Click to expand...
Click to expand...
 
This is the exact scenario. I am not sure that the order
of the journals is that important at this point. If
after I have this setup to work I can change the order
later if I find it is an issue. So you are saying to do
a make table from the duplicates query that I created
correct?

Thanks,
Debbie
-----Original Message-----
Dear Debbie:

A bit confusing, I'm afraid, but I think I have it now.

Your use of "multiple fields within the table" give me a picture of
there being several columns (fields) containing the information you
want to concatenate. However, it now sounds as though you have only
one column (field) in which this data resides, but you have different
values in different rows, and you want to concatenate them.

This is not at all trivial.

The first thing I would recommend doing is to create a new table to
receive the results. This will guarantee the preservation of the
existing data until the process of getting the changes made has been
completed.

Next, some analysis of the existing data is in order. One aspect of
this is to determine some order in which the concatenated column will
be build from its current component parts. Obviously, for each
resulting row, one of the existing components will be placed in the
new memo column first, then another will be appended, then another,
until all have been inserted. Whether you like it or not, you are
going to have to define this order to be able to accomplish the task.
So, your task would be to define some attribute(s) of the existing
rows that uniquely identify them within each group, a group being a
set of rows with the same case#.

In separate action queries, we are going to insert all the "first"
instances of a case#, then update those rows for the "second"
instances of the same case#s, then the "third" and so on.

From this, you can see that another product of our analysis will be to
determine the maximum number of existing rows you have for the same
case#.

A final product of the analysis will be to determine whether the
longest resulting memo will exceed the maximum allowed memo length.
This would cause the whole thing to eventually fail.

I'll end this response here, without beginning the details of the task
before us. I want you to understand this much, and agree to it,
before we continue with the details of each step.

Does this now sound like what you have intended?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


First this is what i have. Actually I created a query to
find the multiple fields within the table. So what I am
looking to do, is where the case# in one table is equal
to the case # in the other table I need to append the
Journal field into the STi Table.

Does that make sense?
-----Original Message-----
Dear Debbie:

When you initially said, "I am trying to get the note fields which are
memo fields to concatenate into one field" I thought that meant you
were using the concatenation operator '&' to
Click to expand...
concatenate
them. An
append query doesn't do anything like concatenating strings.

If you have two columns, ColumnA and ColumnB, whose string values you
wish to concatenate into a new column, ColumnX, you would do something
like:

UPDATE STiJournals
SET ColumnX = ColumnA & ColumnB

If ColumnA and/or ColumnB might be NULL, the '+' operator may be
better than '&' for this.

An append query is used to append rows to a table, not to append
information between the columns in the table. This doesn't sound to
me like what you describe.

Sorry if I've not correctly interpreted your problem. Please let me
know.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 2 Mar 2004 07:14:11 -0800, "DBarker"

INSERT INTO [STi Table] ( Journals )
SELECT STi.Journals
FROM STi LEFT JOIN [STi Table] ON STi.CaseID = [STi
Table].[Case#];

Above is the code from the append query. I have multiple
cases in the STi table and I want to append the journals
to the STi Table where the case #'s are the same.

Thanks,
Debbie
-----Original Message-----
Dear Debbie:

Appending is probably the way to go. Would you please
post the SQL of
your attempt to do this. Perhaps someone will be able
to see a
problem with this and figure out why it failed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 1 Mar 2004 13:43:15 -0800, "DBarker"

I have a table that has multiple note fields for the
same
case. I am trying to get the note fields which are
memo
fields to concatenate into one field. I tried a Union
query but they don't work with Memo fields. I tried
appending but that did not work. Any help would be
appreciated.

Debbie

.


.
Click to expand...
Click to expand...

.
Click to expand...
 
Dear Debbie:

The order is essential to the ability to perform the task, whether you
care about the order in the finished product. The logic of the
process will force us to provide such an order. It is impossible to
just leave this undefined.

No, do not use a make-table. You have too little control. Perhaps
you can just make a copy of your existing table, without data, and
give it a new name.

But all this is doing things in the wrong order.

First, make sure you understand my proposal.

Next, find the unique key that can define which is the "first" record
of each set, which is the second, etc. Write a query if necessary to
make sure there are no duplicates of this key.

Next, perform the other analysis, determining how many total rows will
be combined. Look at the other columns of the table, besides the
case#, the sequencing column(s) (which cannot be included in the
destination table except in an aggregate) and the memo. How are you
proposing to combine these columns?

Then make sure the longest concatenated memo will not exceed the limit
for memo length.

Then we can begin the mechanical preparations, like creating a table.
Otherwise, if we determine it cannot be done at all, you will have
wasted time.

Make sense at all?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


This is the exact scenario. I am not sure that the order
of the journals is that important at this point. If
after I have this setup to work I can change the order
later if I find it is an issue. So you are saying to do
a make table from the duplicates query that I created
correct?

Thanks,
Debbie
-----Original Message-----
Dear Debbie:

A bit confusing, I'm afraid, but I think I have it now.

Your use of "multiple fields within the table" give me a picture of
there being several columns (fields) containing the information you
want to concatenate. However, it now sounds as though you have only
one column (field) in which this data resides, but you have different
values in different rows, and you want to concatenate them.

This is not at all trivial.

The first thing I would recommend doing is to create a new table to
receive the results. This will guarantee the preservation of the
existing data until the process of getting the changes made has been
completed.

Next, some analysis of the existing data is in order. One aspect of
this is to determine some order in which the concatenated column will
be build from its current component parts. Obviously, for each
resulting row, one of the existing components will be placed in the
new memo column first, then another will be appended, then another,
until all have been inserted. Whether you like it or not, you are
going to have to define this order to be able to accomplish the task.
So, your task would be to define some attribute(s) of the existing
rows that uniquely identify them within each group, a group being a
set of rows with the same case#.

In separate action queries, we are going to insert all the "first"
instances of a case#, then update those rows for the "second"
instances of the same case#s, then the "third" and so on.

From this, you can see that another product of our analysis will be to
determine the maximum number of existing rows you have for the same
case#.

A final product of the analysis will be to determine whether the
longest resulting memo will exceed the maximum allowed memo length.
This would cause the whole thing to eventually fail.

I'll end this response here, without beginning the details of the task
before us. I want you to understand this much, and agree to it,
before we continue with the details of each step.

Does this now sound like what you have intended?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


First this is what i have. Actually I created a query to
find the multiple fields within the table. So what I am
looking to do, is where the case# in one table is equal
to the case # in the other table I need to append the
Journal field into the STi Table.

Does that make sense?
-----Original Message-----
Dear Debbie:

When you initially said, "I am trying to get the note
fields which are
memo fields to concatenate into one field" I thought
that meant you
were using the concatenation operator '&' to concatenate
them. An
append query doesn't do anything like concatenating
strings.

If you have two columns, ColumnA and ColumnB, whose
string values you
wish to concatenate into a new column, ColumnX, you
would do something
like:

UPDATE STiJournals
SET ColumnX = ColumnA & ColumnB

If ColumnA and/or ColumnB might be NULL, the '+'
operator may be
better than '&' for this.

An append query is used to append rows to a table, not
to append
information between the columns in the table. This
doesn't sound to
me like what you describe.

Sorry if I've not correctly interpreted your problem.
Please let me
know.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 2 Mar 2004 07:14:11 -0800, "DBarker"

INSERT INTO [STi Table] ( Journals )
SELECT STi.Journals
FROM STi LEFT JOIN [STi Table] ON STi.CaseID = [STi
Table].[Case#];

Above is the code from the append query. I have
multiple
cases in the STi table and I want to append the
journals
to the STi Table where the case #'s are the same.

Thanks,
Debbie
-----Original Message-----
Dear Debbie:

Appending is probably the way to go. Would you please
post the SQL of
your attempt to do this. Perhaps someone will be able
to see a
problem with this and figure out why it failed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 1 Mar 2004 13:43:15 -0800, "DBarker"

I have a table that has multiple note fields for the
same
case. I am trying to get the note fields which are
memo
fields to concatenate into one field. I tried a
Union
query but they don't work with Memo fields. I tried
appending but that did not work. Any help would be
appreciated.

Debbie

.


.
Click to expand...

.
Click to expand...
Click to expand...
 
Below is what I have been working on, I am not sure how
close this is to what you are thinking.

I already have a table that contains just the caseID and
the Journals and I want to compare the STi Table Case#
with the Multiple Journals Table Case ID and append the
journals

Does that make sense. Am I on the right track? I guess
I could insert an auto number field in the Multipe
Journals table to identify the first and second and third
records. Also, I do not think there is a possibility of
exceeding the limit in the memo field for these journals.
------------------------------------------------------
Public Sub Combine()

Dim dbs As Database
Dim FromTbl As TableDef
Dim ToTbl As TableDef
Dim FromFld As Variant
Dim ToFld As Variant

Set dbs = CurrentDb
Set FromTbl = Multiple_Journals_Table1
Set ToTbl = STi_Table
Set FromFld = Multiple_Journals_Table1.Journals
Set ToFld = STi_Table.Journals

If Multiple_Journals_Table1.CaseID =
STi_Table.Journals.Case# Then
ToFld = JTo & JFrom
Else
.MoveNext

If EOF Then
End
End If
End If


End Sub
-----Original Message-----
Dear Debbie:

The order is essential to the ability to perform the task, whether you
care about the order in the finished product. The logic of the
process will force us to provide such an order. It is impossible to
just leave this undefined.

No, do not use a make-table. You have too little control. Perhaps
you can just make a copy of your existing table, without data, and
give it a new name.

But all this is doing things in the wrong order.

First, make sure you understand my proposal.

Next, find the unique key that can define which is the "first" record
of each set, which is the second, etc. Write a query if necessary to
make sure there are no duplicates of this key.

Next, perform the other analysis, determining how many total rows will
be combined. Look at the other columns of the table, besides the
case#, the sequencing column(s) (which cannot be included in the
destination table except in an aggregate) and the memo. How are you
proposing to combine these columns?

Then make sure the longest concatenated memo will not exceed the limit
for memo length.

Then we can begin the mechanical preparations, like creating a table.
Otherwise, if we determine it cannot be done at all, you will have
wasted time.

Make sense at all?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


This is the exact scenario. I am not sure that the order
of the journals is that important at this point. If
after I have this setup to work I can change the order
later if I find it is an issue. So you are saying to do
a make table from the duplicates query that I created
correct?

Thanks,
Debbie
-----Original Message-----
Dear Debbie:

A bit confusing, I'm afraid, but I think I have it now.

Your use of "multiple fields within the table" give me
Click to expand...
a
picture of
there being several columns (fields) containing the information you
want to concatenate. However, it now sounds as though you have only
one column (field) in which this data resides, but you have different
values in different rows, and you want to concatenate them.

This is not at all trivial.

The first thing I would recommend doing is to create a new table to
receive the results. This will guarantee the preservation of the
existing data until the process of getting the changes made has been
completed.

Next, some analysis of the existing data is in order. One aspect of
this is to determine some order in which the concatenated column will
be build from its current component parts. Obviously, for each
resulting row, one of the existing components will be placed in the
new memo column first, then another will be appended, then another,
until all have been inserted. Whether you like it or not, you are
going to have to define this order to be able to accomplish the task.
So, your task would be to define some attribute(s) of the existing
rows that uniquely identify them within each group, a group being a
set of rows with the same case#.

In separate action queries, we are going to insert all the "first"
instances of a case#, then update those rows for the "second"
instances of the same case#s, then the "third" and so on.

From this, you can see that another product of our analysis will be to
determine the maximum number of existing rows you have for the same
case#.

A final product of the analysis will be to determine whether the
longest resulting memo will exceed the maximum allowed memo length.
This would cause the whole thing to eventually fail.

I'll end this response here, without beginning the details of the task
before us. I want you to understand this much, and agree to it,
before we continue with the details of each step.

Does this now sound like what you have intended?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 2 Mar 2004 07:45:15 -0800, "DBarker"

First this is what i have. Actually I created a
Click to expand...
query
to
find the multiple fields within the table. So what I am
looking to do, is where the case# in one table is equal
to the case # in the other table I need to append the
Journal field into the STi Table.

Does that make sense?
-----Original Message-----
Dear Debbie:

When you initially said, "I am trying to get the note
fields which are
memo fields to concatenate into one field" I thought
that meant you
were using the concatenation operator '&' to concatenate
them. An
append query doesn't do anything like concatenating
strings.

If you have two columns, ColumnA and ColumnB, whose
string values you
wish to concatenate into a new column, ColumnX, you
would do something
like:

UPDATE STiJournals
SET ColumnX = ColumnA & ColumnB

If ColumnA and/or ColumnB might be NULL, the '+'
operator may be
better than '&' for this.

An append query is used to append rows to a table, not
to append
information between the columns in the table. This
doesn't sound to
me like what you describe.

Sorry if I've not correctly interpreted your problem.
Please let me
know.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 2 Mar 2004 07:14:11 -0800, "DBarker"

INSERT INTO [STi Table] ( Journals )
SELECT STi.Journals
FROM STi LEFT JOIN [STi Table] ON STi.CaseID = [STi
Table].[Case#];

Above is the code from the append query. I have
multiple
cases in the STi table and I want to append the
journals
to the STi Table where the case #'s are the same.

Thanks,
Debbie
-----Original Message-----
Dear Debbie:

Appending is probably the way to go. Would you please
post the SQL of
your attempt to do this. Perhaps someone will be able
to see a
problem with this and figure out why it failed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 1 Mar 2004 13:43:15 -0800, "DBarker"

I have a table that has multiple note fields for the
same
case. I am trying to get the note fields which are
memo
fields to concatenate into one field. I tried a
Union
query but they don't work with Memo fields. I tried
appending but that did not work. Any help would be
appreciated.

Debbie

.


.


.
Click to expand...
Click to expand...

.
Click to expand...
 
Dear Debbie:

I was working toward a query based solution. You are moving toward a
VBA / recordset approach. I quit doing this kind of thing several
years ago, so I won't try to guide you through this. It does,
however, eliminate the question of ordering the rows by imposing an
arbitrary order on them.

I suggest you use '+' instead of '&' because of how it handles nulls.

I expect you are on a track that should be workable.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Below is what I have been working on, I am not sure how
close this is to what you are thinking.

I already have a table that contains just the caseID and
the Journals and I want to compare the STi Table Case#
with the Multiple Journals Table Case ID and append the
journals

Does that make sense. Am I on the right track? I guess
I could insert an auto number field in the Multipe
Journals table to identify the first and second and third
records. Also, I do not think there is a possibility of
exceeding the limit in the memo field for these journals.
------------------------------------------------------
Public Sub Combine()

Dim dbs As Database
Dim FromTbl As TableDef
Dim ToTbl As TableDef
Dim FromFld As Variant
Dim ToFld As Variant

Set dbs = CurrentDb
Set FromTbl = Multiple_Journals_Table1
Set ToTbl = STi_Table
Set FromFld = Multiple_Journals_Table1.Journals
Set ToFld = STi_Table.Journals

If Multiple_Journals_Table1.CaseID =
STi_Table.Journals.Case# Then
ToFld = JTo & JFrom
Else
.MoveNext

If EOF Then
End
End If
End If


End Sub
-----Original Message-----
Dear Debbie:

The order is essential to the ability to perform the task, whether you
care about the order in the finished product. The logic of the
process will force us to provide such an order. It is impossible to
just leave this undefined.

No, do not use a make-table. You have too little control. Perhaps
you can just make a copy of your existing table, without data, and
give it a new name.

But all this is doing things in the wrong order.

First, make sure you understand my proposal.

Next, find the unique key that can define which is the "first" record
of each set, which is the second, etc. Write a query if necessary to
make sure there are no duplicates of this key.

Next, perform the other analysis, determining how many total rows will
be combined. Look at the other columns of the table, besides the
case#, the sequencing column(s) (which cannot be included in the
destination table except in an aggregate) and the memo. How are you
proposing to combine these columns?

Then make sure the longest concatenated memo will not exceed the limit
for memo length.

Then we can begin the mechanical preparations, like creating a table.
Otherwise, if we determine it cannot be done at all, you will have
wasted time.

Make sense at all?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


This is the exact scenario. I am not sure that the order
of the journals is that important at this point. If
after I have this setup to work I can change the order
later if I find it is an issue. So you are saying to do
a make table from the duplicates query that I created
correct?

Thanks,
Debbie
-----Original Message-----
Dear Debbie:

A bit confusing, I'm afraid, but I think I have it now.

Your use of "multiple fields within the table" give me a
picture of
there being several columns (fields) containing the
information you
want to concatenate. However, it now sounds as though
you have only
one column (field) in which this data resides, but you
have different
values in different rows, and you want to concatenate
them.

This is not at all trivial.

The first thing I would recommend doing is to create a
new table to
receive the results. This will guarantee the
preservation of the
existing data until the process of getting the changes
made has been
completed.

Next, some analysis of the existing data is in order.
One aspect of
this is to determine some order in which the
concatenated column will
be build from its current component parts. Obviously,
for each
resulting row, one of the existing components will be
placed in the
new memo column first, then another will be appended,
then another,
until all have been inserted. Whether you like it or
not, you are
going to have to define this order to be able to
accomplish the task.
So, your task would be to define some attribute(s) of
the existing
rows that uniquely identify them within each group, a
group being a
set of rows with the same case#.

In separate action queries, we are going to insert all
the "first"
instances of a case#, then update those rows for
the "second"
instances of the same case#s, then the "third" and so on.

From this, you can see that another product of our
analysis will be to
determine the maximum number of existing rows you have
for the same
case#.

A final product of the analysis will be to determine
whether the
longest resulting memo will exceed the maximum allowed
memo length.
This would cause the whole thing to eventually fail.

I'll end this response here, without beginning the
details of the task
before us. I want you to understand this much, and
agree to it,
before we continue with the details of each step.

Does this now sound like what you have intended?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 2 Mar 2004 07:45:15 -0800, "DBarker"

First this is what i have. Actually I created a query
to
find the multiple fields within the table. So what I
am
looking to do, is where the case# in one table is equal
to the case # in the other table I need to append the
Journal field into the STi Table.

Does that make sense?
-----Original Message-----
Dear Debbie:

When you initially said, "I am trying to get the note
fields which are
memo fields to concatenate into one field" I thought
that meant you
were using the concatenation operator '&' to
concatenate
them. An
append query doesn't do anything like concatenating
strings.

If you have two columns, ColumnA and ColumnB, whose
string values you
wish to concatenate into a new column, ColumnX, you
would do something
like:

UPDATE STiJournals
SET ColumnX = ColumnA & ColumnB

If ColumnA and/or ColumnB might be NULL, the '+'
operator may be
better than '&' for this.

An append query is used to append rows to a table, not
to append
information between the columns in the table. This
doesn't sound to
me like what you describe.

Sorry if I've not correctly interpreted your problem.
Please let me
know.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 2 Mar 2004 07:14:11 -0800, "DBarker"

INSERT INTO [STi Table] ( Journals )
SELECT STi.Journals
FROM STi LEFT JOIN [STi Table] ON STi.CaseID = [STi
Table].[Case#];

Above is the code from the append query. I have
multiple
cases in the STi table and I want to append the
journals
to the STi Table where the case #'s are the same.

Thanks,
Debbie
-----Original Message-----
Dear Debbie:

Appending is probably the way to go. Would you
please
post the SQL of
your attempt to do this. Perhaps someone will be
able
to see a
problem with this and figure out why it failed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 1 Mar 2004 13:43:15 -0800, "DBarker"

I have a table that has multiple note fields for
the
same
case. I am trying to get the note fields which are
memo
fields to concatenate into one field. I tried a
Union
query but they don't work with Memo fields. I
tried
appending but that did not work. Any help would be
appreciated.

Debbie

.


.


.
Click to expand...

.
Click to expand...
Click to expand...
 
Thanks Tom, I went into the Programming newsgroup and am
getting help there. Thanks again for your time.
-----Original Message-----
Dear Debbie:

I was working toward a query based solution. You are moving toward a
VBA / recordset approach. I quit doing this kind of thing several
years ago, so I won't try to guide you through this. It does,
however, eliminate the question of ordering the rows by imposing an
arbitrary order on them.

I suggest you use '+' instead of '&' because of how it handles nulls.

I expect you are on a track that should be workable.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Below is what I have been working on, I am not sure how
close this is to what you are thinking.

I already have a table that contains just the caseID and
the Journals and I want to compare the STi Table Case#
with the Multiple Journals Table Case ID and append the
journals

Does that make sense. Am I on the right track? I guess
I could insert an auto number field in the Multipe
Journals table to identify the first and second and third
records. Also, I do not think there is a possibility of
exceeding the limit in the memo field for these journals.
------------------------------------------------------
Public Sub Combine()

Dim dbs As Database
Dim FromTbl As TableDef
Dim ToTbl As TableDef
Dim FromFld As Variant
Dim ToFld As Variant

Set dbs = CurrentDb
Set FromTbl = Multiple_Journals_Table1
Set ToTbl = STi_Table
Set FromFld = Multiple_Journals_Table1.Journals
Set ToFld = STi_Table.Journals

If Multiple_Journals_Table1.CaseID =
STi_Table.Journals.Case# Then
ToFld = JTo & JFrom
Else
.MoveNext

If EOF Then
End
End If
End If


End Sub
-----Original Message-----
Dear Debbie:

The order is essential to the ability to perform the task, whether you
care about the order in the finished product. The
Click to expand...
logic
of the
process will force us to provide such an order. It is impossible to
just leave this undefined.

No, do not use a make-table. You have too little control. Perhaps
you can just make a copy of your existing table,
Click to expand...
without
data, and
give it a new name.

But all this is doing things in the wrong order.

First, make sure you understand my proposal.

Next, find the unique key that can define which is the "first" record
of each set, which is the second, etc. Write a query
Click to expand...
if
necessary to
make sure there are no duplicates of this key.

Next, perform the other analysis, determining how many total rows will
be combined. Look at the other columns of the table, besides the
case#, the sequencing column(s) (which cannot be included in the
destination table except in an aggregate) and the
Click to expand...
memo.
How are you
proposing to combine these columns?

Then make sure the longest concatenated memo will not exceed the limit
for memo length.

Then we can begin the mechanical preparations, like creating a table.
Otherwise, if we determine it cannot be done at all,
Click to expand...
you
will have
wasted time.

Make sense at all?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 2 Mar 2004 08:19:33 -0800, "DBarker"

This is the exact scenario. I am not sure that the order
of the journals is that important at this point. If
after I have this setup to work I can change the order
later if I find it is an issue. So you are saying to do
a make table from the duplicates query that I created
correct?

Thanks,
Debbie
-----Original Message-----
Dear Debbie:

A bit confusing, I'm afraid, but I think I have it now.

Your use of "multiple fields within the table" give
Click to expand...
me
a
picture of
there being several columns (fields) containing the
information you
want to concatenate. However, it now sounds as though
you have only
one column (field) in which this data resides, but you
have different
values in different rows, and you want to concatenate
them.

This is not at all trivial.

The first thing I would recommend doing is to create a
new table to
receive the results. This will guarantee the
preservation of the
existing data until the process of getting the changes
made has been
completed.

Next, some analysis of the existing data is in order.
One aspect of
this is to determine some order in which the
concatenated column will
be build from its current component parts. Obviously,
for each
resulting row, one of the existing components will be
placed in the
new memo column first, then another will be appended,
then another,
until all have been inserted. Whether you like it or
not, you are
going to have to define this order to be able to
accomplish the task.
So, your task would be to define some attribute(s) of
the existing
rows that uniquely identify them within each group, a
group being a
set of rows with the same case#.

In separate action queries, we are going to insert all
the "first"
instances of a case#, then update those rows for
the "second"
instances of the same case#s, then the "third" and
Click to expand...
so
on.
From this, you can see that another product of our
analysis will be to
determine the maximum number of existing rows you have
for the same
case#.

A final product of the analysis will be to determine
whether the
longest resulting memo will exceed the maximum allowed
memo length.
This would cause the whole thing to eventually fail.

I'll end this response here, without beginning the
details of the task
before us. I want you to understand this much, and
agree to it,
before we continue with the details of each step.

Does this now sound like what you have intended?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 2 Mar 2004 07:45:15 -0800, "DBarker"

First this is what i have. Actually I created a query
to
find the multiple fields within the table. So what I
am
looking to do, is where the case# in one table is equal
to the case # in the other table I need to append the
Journal field into the STi Table.

Does that make sense?
-----Original Message-----
Dear Debbie:

When you initially said, "I am trying to get the note
fields which are
memo fields to concatenate into one field" I thought
that meant you
were using the concatenation operator '&' to
concatenate
them. An
append query doesn't do anything like concatenating
strings.

If you have two columns, ColumnA and ColumnB, whose
string values you
wish to concatenate into a new column, ColumnX, you
would do something
like:

UPDATE STiJournals
SET ColumnX = ColumnA & ColumnB

If ColumnA and/or ColumnB might be NULL, the '+'
operator may be
better than '&' for this.

An append query is used to append rows to a table, not
to append
information between the columns in the table. This
doesn't sound to
me like what you describe.

Sorry if I've not correctly interpreted your problem.
Please let me
know.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 2 Mar 2004 07:14:11 -0800, "DBarker"

INSERT INTO [STi Table] ( Journals )
SELECT STi.Journals
FROM STi LEFT JOIN [STi Table] ON STi.CaseID = [STi
Table].[Case#];

Above is the code from the append query. I have
multiple
cases in the STi table and I want to append the
journals
to the STi Table where the case #'s are the same.

Thanks,
Debbie
-----Original Message-----
Dear Debbie:

Appending is probably the way to go. Would you
please
post the SQL of
your attempt to do this. Perhaps someone will be
able
to see a
problem with this and figure out why it failed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 1 Mar 2004 13:43:15 -0800, "DBarker"

I have a table that has multiple note fields for
the
same
case. I am trying to get the note fields which are
memo
fields to concatenate into one field. I tried a
Union
query but they don't work with Memo fields. I
tried
appending but that did not work. Any help
Click to expand...
would
be
appreciated.

Debbie

.


.


.


.
Click to expand...
Click to expand...

.
Click to expand...
 
Back
Top