[Lee]

I am working with Word 2003 and Access 2003 and I am using mailmerge to
complete some letters. I have multiple datasources that I use for different
letters. I would like to use some of the "columns" in one database with some
other "columns" in another data base. I am currently cut an pasting columns
and inserting them into an existing database, however, when I finish that,
the datasource will not allow me edit the mailmerge recipients, to enter the
data to show up in the mailmerge letter. The only way that I can enter the
data is to go to the Access database and enter the information and it will
then show up in the mailmerge letter. Otherwise, I have to re-develop the
whole datasource for the particular entries in need in the letter, is there
an eaier way?

[Doug Robbins - Word MVP]

Use a Select query in Access to combine the required columns from the
individual tables.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Lee" wrote in message
...
I am working with Word 2003 and Access 2003 and I am using mailmerge to
complete some letters. I have multiple datasources that I use for
different
letters. I would like to use some of the "columns" in one database with
some
other "columns" in another data base. I am currently cut an pasting
columns
and inserting them into an existing database, however, when I finish that,
the datasource will not allow me edit the mailmerge recipients, to enter
the
data to show up in the mailmerge letter. The only way that I can enter
the
data is to go to the Access database and enter the information and it will
then show up in the mailmerge letter. Otherwise, I have to re-develop the
whole datasource for the particular entries in need in the letter, is
there
an eaier way?

[Peter Jamieson]

When you create your datasources via Word 2003, Word creates an Access .mdb
(and calls it an "Office Address List" that has a very specific structure -
for example, it has a single table and a single query.

When Word uses a .mdb as a data source, Word only lets you use the Edit
button in Mail Merge Recipients if Word thinks the .mdb is also an OAL. But
if you add a table (or maybe two tables) to the .mdb, Word will stop
recognising it as an OAL. The trouble is that even if you delete those extra
tables, Word may still not recognise the .mdb as an OAL. In other words, it
is not clear exactly what you have to do to a .mdb to ensure that Word
recognises it as an OAL. However, if you have added tables or queries as
part of your process of copying columns, I suggest the first thing you do is
back up your .mdbs and delete the objects you added. Then I'd probably also
run the one remaining query from within Access. It may not be enough, but
worth a try.

Peter Jamieson

"Lee" wrote in message
...
I am working with Word 2003 and Access 2003 and I am using mailmerge to
complete some letters. I have multiple datasources that I use for
different
letters. I would like to use some of the "columns" in one database with
some
other "columns" in another data base. I am currently cut an pasting
columns
and inserting them into an existing database, however, when I finish that,
the datasource will not allow me edit the mailmerge recipients, to enter
the
data to show up in the mailmerge letter. The only way that I can enter
the
data is to go to the Access database and enter the information and it will
then show up in the mailmerge letter. Otherwise, I have to re-develop the
whole datasource for the particular entries in need in the letter, is
there
an eaier way?

[Lee]

I tried to run a query and you are correct, Word did not recognize it as an
OAL, so I could not edit the demographics (name, street number, etc.) It
appears to me that the only option I have is to edit the Mail Merge
Recipients and rename the columns and to meet the specifics for the letter
and save the datasource for each category of letters I write. Any other
suggestions?

thanks,
Lee

"Peter Jamieson" wrote:

When you create your datasources via Word 2003, Word creates an Access .mdb
(and calls it an "Office Address List" that has a very specific structure -
for example, it has a single table and a single query.

When Word uses a .mdb as a data source, Word only lets you use the Edit
button in Mail Merge Recipients if Word thinks the .mdb is also an OAL. But
if you add a table (or maybe two tables) to the .mdb, Word will stop
recognising it as an OAL. The trouble is that even if you delete those extra
tables, Word may still not recognise the .mdb as an OAL. In other words, it
is not clear exactly what you have to do to a .mdb to ensure that Word
recognises it as an OAL. However, if you have added tables or queries as
part of your process of copying columns, I suggest the first thing you do is
back up your .mdbs and delete the objects you added. Then I'd probably also
run the one remaining query from within Access. It may not be enough, but
worth a try.

Peter Jamieson

"Lee" wrote in message
...
I am working with Word 2003 and Access 2003 and I am using mailmerge to
complete some letters. I have multiple datasources that I use for
different
letters. I would like to use some of the "columns" in one database with
some
other "columns" in another data base. I am currently cut an pasting
columns
and inserting them into an existing database, however, when I finish that,
the datasource will not allow me edit the mailmerge recipients, to enter
the
data to show up in the mailmerge letter. The only way that I can enter
the
data is to go to the Access database and enter the information and it will
then show up in the mailmerge letter. Otherwise, I have to re-develop the
whole datasource for the particular entries in need in the letter, is
there
an eaier way?

[Peter Jamieson]

After you created the database you wanted, did you try getting rid of all
tables and queries except the following:
a. a table called Office_Address_List
b. a query called Office Address List that executes the following SQL:

SELECT * FROm [Office_Address_List];

? If you did, and you still can't edit in Word, then try the following if
you can:

Create a new database in Access

Open the database you want in another instance of Access

Open the database window in both databases.

Open the list of tables in both databases

Drag and drop the Office_Address_List table to the new database

Open the list of queries in both database

Drag and drop the Office Address List query to the new database.

Close both databases.

Try attaching to the new database.

I've only just tried this and it seems to work, at leaast in part. If it
does, I expect there's an easier way to do it but at least there's /a/ way.

Otherwise, I don't know of another way except "the long way around" of the
kind you describe.

Peter Jamieson

"Lee" wrote in message
...
I tried to run a query and you are correct, Word did not recognize it as an
OAL, so I could not edit the demographics (name, street number, etc.) It
appears to me that the only option I have is to edit the Mail Merge
Recipients and rename the columns and to meet the specifics for the letter
and save the datasource for each category of letters I write. Any other
suggestions?

thanks,
Lee

"Peter Jamieson" wrote:

When you create your datasources via Word 2003, Word creates an Access
.mdb
(and calls it an "Office Address List" that has a very specific
structure -
for example, it has a single table and a single query.

When Word uses a .mdb as a data source, Word only lets you use the Edit
button in Mail Merge Recipients if Word thinks the .mdb is also an OAL.
But
if you add a table (or maybe two tables) to the .mdb, Word will stop
recognising it as an OAL. The trouble is that even if you delete those
extra
tables, Word may still not recognise the .mdb as an OAL. In other words,
it
is not clear exactly what you have to do to a .mdb to ensure that Word
recognises it as an OAL. However, if you have added tables or queries as
part of your process of copying columns, I suggest the first thing you do
is
back up your .mdbs and delete the objects you added. Then I'd probably
also
run the one remaining query from within Access. It may not be enough, but
worth a try.

Peter Jamieson

"Lee" wrote in message
...
I am working with Word 2003 and Access 2003 and I am using mailmerge to
complete some letters. I have multiple datasources that I use for
different
letters. I would like to use some of the "columns" in one database
with
some
other "columns" in another data base. I am currently cut an pasting
columns
and inserting them into an existing database, however, when I finish
that,
the datasource will not allow me edit the mailmerge recipients, to
enter
the
data to show up in the mailmerge letter. The only way that I can enter
the
data is to go to the Access database and enter the information and it
will
then show up in the mailmerge letter. Otherwise, I have to re-develop
the
whole datasource for the particular entries in need in the letter, is
there
an eaier way?

[Lee]

Thank you for writing back. I am not sure I understand how to combine
different parts of the database into a new database. For example, one
database has diagnostic information using certain tests, another database
uses other specific tests and lists diagnostic information. I would like to
combine both databases listing both tests, So, when I try to move the Office
Address List from one database into another database, it asks "Paste table
as...", so I give it the name Office Address List2, same for the query. When
I go to attach the new database to the mailmerge letter, it does allow me to
edit (yeah!), but it takes the original Office Address List (without the
"2"). What am I doing wrong?

"Peter Jamieson" wrote:

After you created the database you wanted, did you try getting rid of all
tables and queries except the following:
a. a table called Office_Address_List
b. a query called Office Address List that executes the following SQL:

SELECT * FROm [Office_Address_List];

? If you did, and you still can't edit in Word, then try the following if
you can:

Create a new database in Access

Open the database you want in another instance of Access

Open the database window in both databases.

Open the list of tables in both databases

Drag and drop the Office_Address_List table to the new database

Open the list of queries in both database

Drag and drop the Office Address List query to the new database.

Close both databases.

Try attaching to the new database.

I've only just tried this and it seems to work, at leaast in part. If it
does, I expect there's an easier way to do it but at least there's /a/ way.

Otherwise, I don't know of another way except "the long way around" of the
kind you describe.

Peter Jamieson

"Lee" wrote in message
...
I tried to run a query and you are correct, Word did not recognize it as an
OAL, so I could not edit the demographics (name, street number, etc.) It
appears to me that the only option I have is to edit the Mail Merge
Recipients and rename the columns and to meet the specifics for the letter
and save the datasource for each category of letters I write. Any other
suggestions?

thanks,
Lee

"Peter Jamieson" wrote:

When you create your datasources via Word 2003, Word creates an Access
.mdb
(and calls it an "Office Address List" that has a very specific
structure -
for example, it has a single table and a single query.

When Word uses a .mdb as a data source, Word only lets you use the Edit
button in Mail Merge Recipients if Word thinks the .mdb is also an OAL.
But
if you add a table (or maybe two tables) to the .mdb, Word will stop
recognising it as an OAL. The trouble is that even if you delete those
extra
tables, Word may still not recognise the .mdb as an OAL. In other words,
it
is not clear exactly what you have to do to a .mdb to ensure that Word
recognises it as an OAL. However, if you have added tables or queries as
part of your process of copying columns, I suggest the first thing you do
is
back up your .mdbs and delete the objects you added. Then I'd probably
also
run the one remaining query from within Access. It may not be enough, but
worth a try.

Peter Jamieson

"Lee" wrote in message
...
I am working with Word 2003 and Access 2003 and I am using mailmerge to
complete some letters. I have multiple datasources that I use for
different
letters. I would like to use some of the "columns" in one database
with
some
other "columns" in another data base. I am currently cut an pasting
columns
and inserting them into an existing database, however, when I finish
that,
the datasource will not allow me edit the mailmerge recipients, to
enter
the
data to show up in the mailmerge letter. The only way that I can enter
the
data is to go to the Access database and enter the information and it
will
then show up in the mailmerge letter. Otherwise, I have to re-develop
the
whole datasource for the particular entries in need in the letter, is
there
an eaier way?

[Peter Jamieson]

How to combine data depends on exactly what data you have and what the end
result needs to be. Generally speaking you can "phyisically" combine them
(which is what you seem to be doing at the moment) or you can "logically"
combine them using a query, as Doug Robbins suggested. What's more, since
you can use the results of the "logical combination" to create a new table,
thus doing a "physical combine" there are good reasons to follow the route
he suggested.

The reasons that I've gone along your existing "physically combining" route
are
a. the additional requirement to be able to edit the combined data from
Word mailmerge in the usual way.
b. it sounded as if you'd already worked out how to do the combination and
that the only problem was how to use the results in Word.

However, you may find that not being able to do (a) is a price worth paying
for the additional power that using queries can give you, and if you haven't
actually worked out how to do (b) then I can only provide some hints.

If you have two OALs (Office address lists) with identical data fields, oe
with 5 records and the other with 10, and you just want to make a new table
with all 15 records. In that case you ought to be able to open the two
tables, verify that all the columns are in the same sequence, then select
the records in one table and copy them into the other one.

If you have two OALs that decsribe the same things (or people) but contain
different information, the to combine them physically you would need
a. to ensure that the two tables had the same number of records
b. to sort them so that the info for the first object is in record one in
both tables, and so on.
c. to copy/paste the columns ffrom one table to another.

However, in this case it probably would be easier to define a query and use
the results to create a new table.

So, when I try to move the Office
Address List from one database into another database, it asks "Paste table
as...", so I give it the name Office Address List2, same for the query.
When
I go to attach the new database to the mailmerge letter, it does allow me
to
edit (yeah!), but it takes the original Office Address List (without the
"2"). What am I doing wrong?

In other words, you're not really doing the right thing above (as far as I
can see - all you're doing is ensuring the two tables are in the same
database, which does not in itself get you anywhere.

It's difficult to know what to suggest at this point, but maybe you could
think about the above and try to work out what applies to your situation.
Let me know if I've misunderstood any key points.

Peter Jamieson
"Lee" wrote in message
...
Thank you for writing back. I am not sure I understand how to combine
different parts of the database into a new database. For example, one
database has diagnostic information using certain tests, another database
uses other specific tests and lists diagnostic information. I would like
to
combine both databases listing both tests, So, when I try to move the
Office
Address List from one database into another database, it asks "Paste table
as...", so I give it the name Office Address List2, same for the query.
When
I go to attach the new database to the mailmerge letter, it does allow me
to
edit (yeah!), but it takes the original Office Address List (without the
"2"). What am I doing wrong?

"Peter Jamieson" wrote:

After you created the database you wanted, did you try getting rid of all
tables and queries except the following:
a. a table called Office_Address_List
b. a query called Office Address List that executes the following SQL:

SELECT * FROm [Office_Address_List];

? If you did, and you still can't edit in Word, then try the following if
you can:

Create a new database in Access

Open the database you want in another instance of Access

Open the database window in both databases.

Open the list of tables in both databases

Drag and drop the Office_Address_List table to the new database

Open the list of queries in both database

Drag and drop the Office Address List query to the new database.

Close both databases.

Try attaching to the new database.

I've only just tried this and it seems to work, at leaast in part. If it
does, I expect there's an easier way to do it but at least there's /a/
way.

Otherwise, I don't know of another way except "the long way around" of
the
kind you describe.

Peter Jamieson

"Lee" wrote in message
...
I tried to run a query and you are correct, Word did not recognize it as
an
OAL, so I could not edit the demographics (name, street number, etc.)
It
appears to me that the only option I have is to edit the Mail Merge
Recipients and rename the columns and to meet the specifics for the
letter
and save the datasource for each category of letters I write. Any
other
suggestions?

thanks,
Lee

"Peter Jamieson" wrote:

When you create your datasources via Word 2003, Word creates an Access
.mdb
(and calls it an "Office Address List" that has a very specific
structure -
for example, it has a single table and a single query.

When Word uses a .mdb as a data source, Word only lets you use the
Edit
button in Mail Merge Recipients if Word thinks the .mdb is also an
OAL.
But
if you add a table (or maybe two tables) to the .mdb, Word will stop
recognising it as an OAL. The trouble is that even if you delete those
extra
tables, Word may still not recognise the .mdb as an OAL. In other
words,
it
is not clear exactly what you have to do to a .mdb to ensure that Word
recognises it as an OAL. However, if you have added tables or queries
as
part of your process of copying columns, I suggest the first thing you
do
is
back up your .mdbs and delete the objects you added. Then I'd probably
also
run the one remaining query from within Access. It may not be enough,
but
worth a try.

Peter Jamieson

"Lee" wrote in message
...
I am working with Word 2003 and Access 2003 and I am using mailmerge
to
complete some letters. I have multiple datasources that I use for
different
letters. I would like to use some of the "columns" in one database
with
some
other "columns" in another data base. I am currently cut an pasting
columns
and inserting them into an existing database, however, when I finish
that,
the datasource will not allow me edit the mailmerge recipients, to
enter
the
data to show up in the mailmerge letter. The only way that I can
enter
the
data is to go to the Access database and enter the information and
it
will
then show up in the mailmerge letter. Otherwise, I have to
re-develop
the
whole datasource for the particular entries in need in the letter,
is
there
an eaier way?

[Lee]

I tried to send this earlier, but ran into difficulty. If the first reply
was received, please disregard.

I have a mailmerge letter that contains Test A with mail recipients entries
that are "verbal IQ" field, "nonverbal IQ" field, etc., with their respective
scores. In that database, I also have academic fields and scores listed in
the mail recipient list, such as "spelling" field, "writing fluency" field.
In another database, I have Test B with "Verbal Comprehension" field,
"Processing Speed" field and their respective scores, and an emotionality
fields, "Aggression" field, "Withdrawal" field in the mail recipient list. I
would like to send a letter using mailmerge that will combine the fields in
Test A with fields that have the emotionality entries - the fields are in
different databases. How do I make a query to combine both fields and Word
will recognize it al OAL? The field names are the same for the demographics
(student name, address) but the other field names are different (Verbal
IQ,[database with Test A and academics] Withdrawal, Aggression [database with
Test B and emotionality) fields. The goal is to bring up the mail recipient
list that will have the fields I need to complete the letter and state what
test was taken and what their scores were.

Lee

"Peter Jamieson" wrote:

How to combine data depends on exactly what data you have and what the end
result needs to be. Generally speaking you can "phyisically" combine them
(which is what you seem to be doing at the moment) or you can "logically"
combine them using a query, as Doug Robbins suggested. What's more, since
you can use the results of the "logical combination" to create a new table,
thus doing a "physical combine" there are good reasons to follow the route
he suggested.

The reasons that I've gone along your existing "physically combining" route
are
a. the additional requirement to be able to edit the combined data from
Word mailmerge in the usual way.
b. it sounded as if you'd already worked out how to do the combination and
that the only problem was how to use the results in Word.

However, you may find that not being able to do (a) is a price worth paying
for the additional power that using queries can give you, and if you haven't
actually worked out how to do (b) then I can only provide some hints.

If you have two OALs (Office address lists) with identical data fields, oe
with 5 records and the other with 10, and you just want to make a new table
with all 15 records. In that case you ought to be able to open the two
tables, verify that all the columns are in the same sequence, then select
the records in one table and copy them into the other one.

If you have two OALs that decsribe the same things (or people) but contain
different information, the to combine them physically you would need
a. to ensure that the two tables had the same number of records
b. to sort them so that the info for the first object is in record one in
both tables, and so on.
c. to copy/paste the columns ffrom one table to another.

However, in this case it probably would be easier to define a query and use
the results to create a new table.

So, when I try to move the Office
Address List from one database into another database, it asks "Paste table
as...", so I give it the name Office Address List2, same for the query.
When
I go to attach the new database to the mailmerge letter, it does allow me
to
edit (yeah!), but it takes the original Office Address List (without the
"2"). What am I doing wrong?

In other words, you're not really doing the right thing above (as far as I
can see - all you're doing is ensuring the two tables are in the same
database, which does not in itself get you anywhere.

It's difficult to know what to suggest at this point, but maybe you could
think about the above and try to work out what applies to your situation.
Let me know if I've misunderstood any key points.

Peter Jamieson
"Lee" wrote in message
...
Thank you for writing back. I am not sure I understand how to combine
different parts of the database into a new database. For example, one
database has diagnostic information using certain tests, another database
uses other specific tests and lists diagnostic information. I would like
to
combine both databases listing both tests, So, when I try to move the
Office
Address List from one database into another database, it asks "Paste table
as...", so I give it the name Office Address List2, same for the query.
When
I go to attach the new database to the mailmerge letter, it does allow me
to
edit (yeah!), but it takes the original Office Address List (without the
"2"). What am I doing wrong?

"Peter Jamieson" wrote:

After you created the database you wanted, did you try getting rid of all
tables and queries except the following:
a. a table called Office_Address_List
b. a query called Office Address List that executes the following SQL:

SELECT * FROm [Office_Address_List];

? If you did, and you still can't edit in Word, then try the following if
you can:

Create a new database in Access

Open the database you want in another instance of Access

Open the database window in both databases.

Open the list of tables in both databases

Drag and drop the Office_Address_List table to the new database

Open the list of queries in both database

Drag and drop the Office Address List query to the new database.

Close both databases.

Try attaching to the new database.

I've only just tried this and it seems to work, at leaast in part. If it
does, I expect there's an easier way to do it but at least there's /a/
way.

Otherwise, I don't know of another way except "the long way around" of
the
kind you describe.

Peter Jamieson

"Lee" wrote in message
...
I tried to run a query and you are correct, Word did not recognize it as
an
OAL, so I could not edit the demographics (name, street number, etc.)
It
appears to me that the only option I have is to edit the Mail Merge
Recipients and rename the columns and to meet the specifics for the
letter
and save the datasource for each category of letters I write. Any
other
suggestions?

thanks,
Lee

"Peter Jamieson" wrote:

When you create your datasources via Word 2003, Word creates an Access
.mdb
(and calls it an "Office Address List" that has a very specific
structure -
for example, it has a single table and a single query.

When Word uses a .mdb as a data source, Word only lets you use the
Edit
button in Mail Merge Recipients if Word thinks the .mdb is also an
OAL.
But
if you add a table (or maybe two tables) to the .mdb, Word will stop
recognising it as an OAL. The trouble is that even if you delete those
extra
tables, Word may still not recognise the .mdb as an OAL. In other
words,
it
is not clear exactly what you have to do to a .mdb to ensure that Word
recognises it as an OAL. However, if you have added tables or queries
as
part of your process of copying columns, I suggest the first thing you
do
is
back up your .mdbs and delete the objects you added. Then I'd probably
also
run the one remaining query from within Access. It may not be enough,
but
worth a try.

Peter Jamieson

"Lee" wrote in message
...
I am working with Word 2003 and Access 2003 and I am using mailmerge
to
complete some letters. I have multiple datasources that I use for
different
letters. I would like to use some of the "columns" in one database
with
some
other "columns" in another data base. I am currently cut an pasting
columns
and inserting them into an existing database, however, when I finish
that,
the datasource will not allow me edit the mailmerge recipients, to
enter
the
data to show up in the mailmerge letter. The only way that I can
enter
the
data is to go to the Access database and enter the information and
it
will
then show up in the mailmerge letter. Otherwise, I have to
re-develop
the
whole datasource for the particular entries in need in the letter,
is
there
an eaier way?

[Lee]

how do I do that and Word will recognize it as OAL? How do I link two tables
when there is not a match of data? The table I have has matching field
names, but the data in those fields are different. For example, both tables
have a "name" field but the names in that field are different, so the query
doesn't link the tables. I even tried to type the same name in both tables,
ran the query and that worked, but Word did not recognize it as an OAL, and
the edit button was greyed out when I tried to attach it to the mailmerge
document.

"Doug Robbins - Word MVP" wrote:

Use a Select query in Access to combine the required columns from the
individual tables.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Lee" wrote in message
...
I am working with Word 2003 and Access 2003 and I am using mailmerge to
complete some letters. I have multiple datasources that I use for
different
letters. I would like to use some of the "columns" in one database with
some
other "columns" in another data base. I am currently cut an pasting
columns
and inserting them into an existing database, however, when I finish that,
the datasource will not allow me edit the mailmerge recipients, to enter
the
data to show up in the mailmerge letter. The only way that I can enter
the
data is to go to the Access database and enter the information and it will
then show up in the mailmerge letter. Otherwise, I have to re-develop the
whole datasource for the particular entries in need in the letter, is
there
an eaier way?

[Peter Jamieson]

In that case, try the following (I'm not going to go along the Query route
because it's harder to describe):
a. copy the two tables into one database. rename them as "a" and "b".
b. open each table and if their records are not in the same sequence, use
the Record|Sort menu to sort both tables into the same sequence (perhaps you
have an e-mail address in each table)
c. for each column in table a that you want to add to table b,
- select a column in table b then use Insert|Column to insert a new
blank column
- select the column you want to copy from table a, Edit|Copy, select the
new blank column in table b, Edit|Paste
- if necessary, select the field name at the top of the column and
change it to the one you want.
d. when you're done you need to create an OAL. For example
- create a new OAL in Word (just put one record in it
- open that OAL in Access and delete the table Office_Address_List
- copy table b into that OAL and rename it Office_Address_List

I think that will work if I have understood how your data is structured. I
expect there's a simpler way to do it but you should really explore Access
yourself and/or ask around in an Access-oriented group. If the data isn't
structured the way I think then it would be difficult to describe how to do
this without knowing a great deal more.

Peter Jamieson

"Lee" wrote in message
...
I tried to send this earlier, but ran into difficulty. If the first reply
was received, please disregard.

I have a mailmerge letter that contains Test A with mail recipients
entries
that are "verbal IQ" field, "nonverbal IQ" field, etc., with their
respective
scores. In that database, I also have academic fields and scores listed
in
the mail recipient list, such as "spelling" field, "writing fluency"
field.
In another database, I have Test B with "Verbal Comprehension" field,
"Processing Speed" field and their respective scores, and an emotionality
fields, "Aggression" field, "Withdrawal" field in the mail recipient list.
I
would like to send a letter using mailmerge that will combine the fields
in
Test A with fields that have the emotionality entries - the fields are in
different databases. How do I make a query to combine both fields and
Word
will recognize it al OAL? The field names are the same for the
demographics
(student name, address) but the other field names are different (Verbal
IQ,[database with Test A and academics] Withdrawal, Aggression [database
with
Test B and emotionality) fields. The goal is to bring up the mail
recipient
list that will have the fields I need to complete the letter and state
what
test was taken and what their scores were.

Lee

"Peter Jamieson" wrote:

How to combine data depends on exactly what data you have and what the
end
result needs to be. Generally speaking you can "phyisically" combine them
(which is what you seem to be doing at the moment) or you can "logically"
combine them using a query, as Doug Robbins suggested. What's more,
since
you can use the results of the "logical combination" to create a new
table,
thus doing a "physical combine" there are good reasons to follow the
route
he suggested.

The reasons that I've gone along your existing "physically combining"
route
are
a. the additional requirement to be able to edit the combined data from
Word mailmerge in the usual way.
b. it sounded as if you'd already worked out how to do the combination
and
that the only problem was how to use the results in Word.

However, you may find that not being able to do (a) is a price worth
paying
for the additional power that using queries can give you, and if you
haven't
actually worked out how to do (b) then I can only provide some hints.

If you have two OALs (Office address lists) with identical data fields,
oe
with 5 records and the other with 10, and you just want to make a new
table
with all 15 records. In that case you ought to be able to open the two
tables, verify that all the columns are in the same sequence, then select
the records in one table and copy them into the other one.

If you have two OALs that decsribe the same things (or people) but
contain
different information, the to combine them physically you would need
a. to ensure that the two tables had the same number of records
b. to sort them so that the info for the first object is in record one
in
both tables, and so on.
c. to copy/paste the columns ffrom one table to another.

However, in this case it probably would be easier to define a query and
use
the results to create a new table.

So, when I try to move the Office
Address List from one database into another database, it asks "Paste
table
as...", so I give it the name Office Address List2, same for the query.
When
I go to attach the new database to the mailmerge letter, it does allow
me
to
edit (yeah!), but it takes the original Office Address List (without
the
"2"). What am I doing wrong?

In other words, you're not really doing the right thing above (as far as
I
can see - all you're doing is ensuring the two tables are in the same
database, which does not in itself get you anywhere.

It's difficult to know what to suggest at this point, but maybe you could
think about the above and try to work out what applies to your situation.
Let me know if I've misunderstood any key points.

Peter Jamieson
"Lee" wrote in message
...
Thank you for writing back. I am not sure I understand how to combine
different parts of the database into a new database. For example, one
database has diagnostic information using certain tests, another
database
uses other specific tests and lists diagnostic information. I would
like
to
combine both databases listing both tests, So, when I try to move the
Office
Address List from one database into another database, it asks "Paste
table
as...", so I give it the name Office Address List2, same for the query.
When
I go to attach the new database to the mailmerge letter, it does allow
me
to
edit (yeah!), but it takes the original Office Address List (without
the
"2"). What am I doing wrong?

"Peter Jamieson" wrote:

After you created the database you wanted, did you try getting rid of
all
tables and queries except the following:
a. a table called Office_Address_List
b. a query called Office Address List that executes the following
SQL:

SELECT * FROm [Office_Address_List];

? If you did, and you still can't edit in Word, then try the following
if
you can:

Create a new database in Access

Open the database you want in another instance of Access

Open the database window in both databases.

Open the list of tables in both databases

Drag and drop the Office_Address_List table to the new database

Open the list of queries in both database

Drag and drop the Office Address List query to the new database.

Close both databases.

Try attaching to the new database.

I've only just tried this and it seems to work, at leaast in part. If
it
does, I expect there's an easier way to do it but at least there's /a/
way.

Otherwise, I don't know of another way except "the long way around" of
the
kind you describe.

Peter Jamieson

"Lee" wrote in message
...
I tried to run a query and you are correct, Word did not recognize it
as
an
OAL, so I could not edit the demographics (name, street number,
etc.)
It
appears to me that the only option I have is to edit the Mail Merge
Recipients and rename the columns and to meet the specifics for the
letter
and save the datasource for each category of letters I write. Any
other
suggestions?

thanks,
Lee

"Peter Jamieson" wrote:

When you create your datasources via Word 2003, Word creates an
Access
.mdb
(and calls it an "Office Address List" that has a very specific
structure -
for example, it has a single table and a single query.

When Word uses a .mdb as a data source, Word only lets you use the
Edit
button in Mail Merge Recipients if Word thinks the .mdb is also an
OAL.
But
if you add a table (or maybe two tables) to the .mdb, Word will
stop
recognising it as an OAL. The trouble is that even if you delete
those
extra
tables, Word may still not recognise the .mdb as an OAL. In other
words,
it
is not clear exactly what you have to do to a .mdb to ensure that
Word
recognises it as an OAL. However, if you have added tables or
queries
as
part of your process of copying columns, I suggest the first thing
you
do
is
back up your .mdbs and delete the objects you added. Then I'd
probably
also
run the one remaining query from within Access. It may not be
enough,
but
worth a try.

Peter Jamieson

"Lee" wrote in message
...
I am working with Word 2003 and Access 2003 and I am using
mailmerge
to
complete some letters. I have multiple datasources that I use
for
different
letters. I would like to use some of the "columns" in one
database
with
some
other "columns" in another data base. I am currently cut an
pasting
columns
and inserting them into an existing database, however, when I
finish
that,
the datasource will not allow me edit the mailmerge recipients,
to
enter
the
data to show up in the mailmerge letter. The only way that I can
enter
the
data is to go to the Access database and enter the information
and
it
will
then show up in the mailmerge letter. Otherwise, I have to
re-develop
the
whole datasource for the particular entries in need in the
letter,
is
there
an eaier way?

[Lee]

Thank you, I will try it.
Lee

"Peter Jamieson" wrote:

In that case, try the following (I'm not going to go along the Query route
because it's harder to describe):
a. copy the two tables into one database. rename them as "a" and "b".
b. open each table and if their records are not in the same sequence, use
the Record|Sort menu to sort both tables into the same sequence (perhaps you
have an e-mail address in each table)
c. for each column in table a that you want to add to table b,
- select a column in table b then use Insert|Column to insert a new
blank column
- select the column you want to copy from table a, Edit|Copy, select the
new blank column in table b, Edit|Paste
- if necessary, select the field name at the top of the column and
change it to the one you want.
d. when you're done you need to create an OAL. For example
- create a new OAL in Word (just put one record in it
- open that OAL in Access and delete the table Office_Address_List
- copy table b into that OAL and rename it Office_Address_List

I think that will work if I have understood how your data is structured. I
expect there's a simpler way to do it but you should really explore Access
yourself and/or ask around in an Access-oriented group. If the data isn't
structured the way I think then it would be difficult to describe how to do
this without knowing a great deal more.

Peter Jamieson

"Lee" wrote in message
...
I tried to send this earlier, but ran into difficulty. If the first reply
was received, please disregard.

I have a mailmerge letter that contains Test A with mail recipients
entries
that are "verbal IQ" field, "nonverbal IQ" field, etc., with their
respective
scores. In that database, I also have academic fields and scores listed
in
the mail recipient list, such as "spelling" field, "writing fluency"
field.
In another database, I have Test B with "Verbal Comprehension" field,
"Processing Speed" field and their respective scores, and an emotionality
fields, "Aggression" field, "Withdrawal" field in the mail recipient list.
I
would like to send a letter using mailmerge that will combine the fields
in
Test A with fields that have the emotionality entries - the fields are in
different databases. How do I make a query to combine both fields and
Word
will recognize it al OAL? The field names are the same for the
demographics
(student name, address) but the other field names are different (Verbal
IQ,[database with Test A and academics] Withdrawal, Aggression [database
with
Test B and emotionality) fields. The goal is to bring up the mail
recipient
list that will have the fields I need to complete the letter and state
what
test was taken and what their scores were.

Lee

"Peter Jamieson" wrote:

How to combine data depends on exactly what data you have and what the
end
result needs to be. Generally speaking you can "phyisically" combine them
(which is what you seem to be doing at the moment) or you can "logically"
combine them using a query, as Doug Robbins suggested. What's more,
since
you can use the results of the "logical combination" to create a new
table,
thus doing a "physical combine" there are good reasons to follow the
route
he suggested.

The reasons that I've gone along your existing "physically combining"
route
are
a. the additional requirement to be able to edit the combined data from
Word mailmerge in the usual way.
b. it sounded as if you'd already worked out how to do the combination
and
that the only problem was how to use the results in Word.

However, you may find that not being able to do (a) is a price worth
paying
for the additional power that using queries can give you, and if you
haven't
actually worked out how to do (b) then I can only provide some hints.

If you have two OALs (Office address lists) with identical data fields,
oe
with 5 records and the other with 10, and you just want to make a new
table
with all 15 records. In that case you ought to be able to open the two
tables, verify that all the columns are in the same sequence, then select
the records in one table and copy them into the other one.

If you have two OALs that decsribe the same things (or people) but
contain
different information, the to combine them physically you would need
a. to ensure that the two tables had the same number of records
b. to sort them so that the info for the first object is in record one
in
both tables, and so on.
c. to copy/paste the columns ffrom one table to another.

However, in this case it probably would be easier to define a query and
use
the results to create a new table.

So, when I try to move the Office
Address List from one database into another database, it asks "Paste
table
as...", so I give it the name Office Address List2, same for the query.
When
I go to attach the new database to the mailmerge letter, it does allow
me
to
edit (yeah!), but it takes the original Office Address List (without
the
"2"). What am I doing wrong?

In other words, you're not really doing the right thing above (as far as
I
can see - all you're doing is ensuring the two tables are in the same
database, which does not in itself get you anywhere.

It's difficult to know what to suggest at this point, but maybe you could
think about the above and try to work out what applies to your situation.
Let me know if I've misunderstood any key points.

Peter Jamieson
"Lee" wrote in message
...
Thank you for writing back. I am not sure I understand how to combine
different parts of the database into a new database. For example, one
database has diagnostic information using certain tests, another
database
uses other specific tests and lists diagnostic information. I would
like
to
combine both databases listing both tests, So, when I try to move the
Office
Address List from one database into another database, it asks "Paste
table
as...", so I give it the name Office Address List2, same for the query.
When
I go to attach the new database to the mailmerge letter, it does allow
me
to
edit (yeah!), but it takes the original Office Address List (without
the
"2"). What am I doing wrong?

"Peter Jamieson" wrote:

After you created the database you wanted, did you try getting rid of
all
tables and queries except the following:
a. a table called Office_Address_List
b. a query called Office Address List that executes the following
SQL:

SELECT * FROm [Office_Address_List];

? If you did, and you still can't edit in Word, then try the following
if
you can:

Create a new database in Access

Open the database you want in another instance of Access

Open the database window in both databases.

Open the list of tables in both databases

Drag and drop the Office_Address_List table to the new database

Open the list of queries in both database

Drag and drop the Office Address List query to the new database.

Close both databases.

Try attaching to the new database.

I've only just tried this and it seems to work, at leaast in part. If
it
does, I expect there's an easier way to do it but at least there's /a/
way.

Otherwise, I don't know of another way except "the long way around" of
the
kind you describe.

Peter Jamieson

"Lee" wrote in message
...
I tried to run a query and you are correct, Word did not recognize it
as
an
OAL, so I could not edit the demographics (name, street number,
etc.)
It
appears to me that the only option I have is to edit the Mail Merge
Recipients and rename the columns and to meet the specifics for the
letter
and save the datasource for each category of letters I write. Any
other
suggestions?

thanks,
Lee

"Peter Jamieson" wrote:

When you create your datasources via Word 2003, Word creates an
Access
.mdb
(and calls it an "Office Address List" that has a very specific
structure -
for example, it has a single table and a single query.

When Word uses a .mdb as a data source, Word only lets you use the
Edit
button in Mail Merge Recipients if Word thinks the .mdb is also an
OAL.
But
if you add a table (or maybe two tables) to the .mdb, Word will
stop
recognising it as an OAL. The trouble is that even if you delete
those
extra
tables, Word may still not recognise the .mdb as an OAL. In other
words,
it
is not clear exactly what you have to do to a .mdb to ensure that
Word
recognises it as an OAL. However, if you have added tables or
queries
as
part of your process of copying columns, I suggest the first thing
you
do
is
back up your .mdbs and delete the objects you added. Then I'd
probably
also
run the one remaining query from within Access. It may not be
enough,
but
worth a try.

Peter Jamieson

"Lee" wrote in message
...
I am working with Word 2003 and Access 2003 and I am using
mailmerge
to
complete some letters. I have multiple datasources that I use
for
different
letters. I would like to use some of the "columns" in one
database
with
some
other "columns" in another data base. I am currently cut an
pasting
columns
and inserting them into an existing database, however, when I
finish
that,
the datasource will not allow me edit the mailmerge recipients,
to
enter
the
data to show up in the mailmerge letter. The only way that I can
enter
the
data is to go to the Access database and enter the information
and
it
will
then show up in the mailmerge letter. Otherwise, I have to
re-develop
the
whole datasource for the particular entries in need in the
letter,
is
there
an eaier way?

[Doug Robbins - Word MVP]

To create such a query, there will need to be a field in each table that
contains the relevant information to allow a link to be set up between the
tables.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Lee" wrote in message
...
how do I do that and Word will recognize it as OAL? How do I link two
tables
when there is not a match of data? The table I have has matching field
names, but the data in those fields are different. For example, both
tables
have a "name" field but the names in that field are different, so the
query
doesn't link the tables. I even tried to type the same name in both
tables,
ran the query and that worked, but Word did not recognize it as an OAL,
and
the edit button was greyed out when I tried to attach it to the mailmerge
document.

"Doug Robbins - Word MVP" wrote:

Use a Select query in Access to combine the required columns from the
individual tables.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Lee" wrote in message
...
I am working with Word 2003 and Access 2003 and I am using mailmerge to
complete some letters. I have multiple datasources that I use for
different
letters. I would like to use some of the "columns" in one database
with
some
other "columns" in another data base. I am currently cut an pasting
columns
and inserting them into an existing database, however, when I finish
that,
the datasource will not allow me edit the mailmerge recipients, to
enter
the
data to show up in the mailmerge letter. The only way that I can enter
the
data is to go to the Access database and enter the information and it
will
then show up in the mailmerge letter. Otherwise, I have to re-develop
the
whole datasource for the particular entries in need in the letter, is
there
an eaier way?