How to combine two rows values into one row in Access

[Guest]

I am trying to do a mail merge from Access table. My table is set such a way
that i get the values in two rows. To perform the mail merge from Access to
Word i need the record in one row only.

Example of field set up : Dir: yes/no, Pre : yes/no , first name, last name
In my situation it is possible to have same person working as director and
Presdient

I am stuck at this point. Any help will be highly appreciated. I have a very
limited knowlege of Access programming.

 

[Guest]

I am trying to do a mail merge from Access table. My table is set such a way
that i get the values in two rows. To perform the mail merge from Access to
Word i need the record in one row only.

Example of field set up : Dir: yes/no, Pre : yes/no , first name, last name
In my situation it is possible to have same person working as director and
Presdient

I am stuck at this point. Any help will be highly appreciated. I have a very
limited knowlege of Access programming.

It sounds like all you need is a very simple query, probably from just a
single table. If you have multiple rows per person, there's probably another
table complicating matters. What does your query look like? Open your query
in Design View, then click on View | SQL View to see the SQL code behind your
query. That's often best when posting to a forum like this.

 

[Guest]

Thank you for replying.
there are two tables:
Companies table
People table
i run a query which pull the info from both the tables and put it in query
table. I use this query table in mail merge code/
Companies table and people table are linked with Client ID fields
So when a pull the results with select query, i get something like this
Company Name, Client ID, address, Directors, Oweners, Full Name Etc.....
AAA 1 Xyz YEs Yes WQR
AAA 1 XYZ YES No QAS
Like this .........

Result come in two rows. For mail merge i need the whole record in one row.
Also I need the full name for directors, owners and other officers in that
single row..... Is there any way I can do this all .....
I am passing the ino through a Form which has both people and COmpany table
fields

For Mail merge I am using following code:
Function MergeIt()
Dim objWord As Word.Document

Set objWord = GetObject("C:\Documents and Settings\S\My Documents\test",
"Word.Document")
' Make Word visible.
objWord.Application.Visible = True

' Set the mail merge data source as the current database.
objWord.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="TABLE test", _
SQLStatement:="Select * from [test]"

' Execute the mail merge.
objWord.MailMerge.Execute

I donot know anyother easy way which involves less programming to update a
contract made in word from access/
Any help will be highly appreciated
Thank yu

 

[Guest]

Thank you for replying.
there are two tables:
Companies table
People table
i run a query which pull the info from both the tables and put it in query
table. I use this query table in mail merge code/
Companies table and people table are linked with Client ID fields
So when a pull the results with select query, i get something like this
Company Name, Client ID, address, Directors, Oweners, Full Name Etc.....
AAA 1 Xyz YEs Yes WQR
AAA 1 XYZ YES No QAS
Like this .........

Result come in two rows. For mail merge i need the whole record in one row.
Also I need the full name for directors, owners and other officers in that
single row..... Is there any way I can do this all .....
I am passing the ino through a Form which has both people and COmpany table
fields

For Mail merge I am using following code:
Function MergeIt()
Dim objWord As Word.Document

Set objWord = GetObject("C:\Documents and Settings\S\My Documents\test",
"Word.Document")
' Make Word visible.
objWord.Application.Visible = True

' Set the mail merge data source as the current database.
objWord.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="TABLE test", _
SQLStatement:="Select * from [test]"

' Execute the mail merge.
objWord.MailMerge.Execute

I donot know anyother easy way which involves less programming to update a
contract made in word from access/
Any help will be highly appreciated
Thank yu

That helps a little. If I understand you right, you want one Word document
for each Company, and the document should list the Director and Owner for
that Company.
I'm working blind here, without knowing exactly how your tables are
arranged, but hopefully you can use this as a starting point.

* Create a query in design view, with your Companies table and two copies of
the Contacts table.
* If you have an existing relationship between the Companies table and the
Contacts table, click it and hit delete (we're talking about the relationship
as it appears in your query, NOT the permanent relationship you see with View
| Relationships).
* Create new relationships between the Companies table and each of the
Contacts tables. Specify that each should include all records from Companies
table and only matching records from the Contacts table.
* Drag the fields you want into the table part of your query. Include the
Name field from each Contacts table, and include the Director field from one
table, the Owner field from the other.
* Change the Field from Name to DirectorName: Name for the first Name;
change the other one to OwnerName: Name.
* Set the Criteria to Yes for Director and Owner.

If you're familiar with SQL, this is what it should look like:

SELECT
Companies.Name, Companies.Address, Director.Name AS DirectorName, Owner.Name
AS OwnerName
FROM
(Companies LEFT JOIN Contacts AS Director ON Companies.ID = Contacts.ID AND
Contacts.Director = Yes) LEFT JOIN Contacts AS Owner ON Companies.ID =
Contacts.ID AND Contacts.Owner = Yes

Your result should be one row for every company, whether they have zero,
one, or two officers. If instead what you want is one record per Person,
you'll need something a little different.
If this doesn't work for you, let me know. It would also help if you posted
the SQL of the original query, and a list of relevant table and field names.

 

[Guest]

Thank you so much. I will let you know the result today.

I really appreciate your help

Thank you for replying.
there are two tables:
Companies table
People table
i run a query which pull the info from both the tables and put it in query
table. I use this query table in mail merge code/
Companies table and people table are linked with Client ID fields
So when a pull the results with select query, i get something like this
Company Name, Client ID, address, Directors, Oweners, Full Name Etc.....
AAA 1 Xyz YEs Yes WQR
AAA 1 XYZ YES No QAS
Like this .........

Result come in two rows. For mail merge i need the whole record in one row.
Also I need the full name for directors, owners and other officers in that
single row..... Is there any way I can do this all .....
I am passing the ino through a Form which has both people and COmpany table
fields

For Mail merge I am using following code:
Function MergeIt()
Dim objWord As Word.Document

Set objWord = GetObject("C:\Documents and Settings\S\My Documents\test",
"Word.Document")
' Make Word visible.
objWord.Application.Visible = True

' Set the mail merge data source as the current database.
objWord.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="TABLE test", _
SQLStatement:="Select * from [test]"

' Execute the mail merge.
objWord.MailMerge.Execute

I donot know anyother easy way which involves less programming to update a
contract made in word from access/
Any help will be highly appreciated
Thank yu

That helps a little. If I understand you right, you want one Word document
for each Company, and the document should list the Director and Owner for
that Company.
I'm working blind here, without knowing exactly how your tables are
arranged, but hopefully you can use this as a starting point.

* Create a query in design view, with your Companies table and two copies of
the Contacts table.
* If you have an existing relationship between the Companies table and the
Contacts table, click it and hit delete (we're talking about the relationship
as it appears in your query, NOT the permanent relationship you see with View
| Relationships).
* Create new relationships between the Companies table and each of the
Contacts tables. Specify that each should include all records from Companies
table and only matching records from the Contacts table.
* Drag the fields you want into the table part of your query. Include the
Name field from each Contacts table, and include the Director field from one
table, the Owner field from the other.
* Change the Field from Name to DirectorName: Name for the first Name;
change the other one to OwnerName: Name.
* Set the Criteria to Yes for Director and Owner.

If you're familiar with SQL, this is what it should look like:

SELECT
Companies.Name, Companies.Address, Director.Name AS DirectorName, Owner.Name
AS OwnerName
FROM
(Companies LEFT JOIN Contacts AS Director ON Companies.ID = Contacts.ID AND
Contacts.Director = Yes) LEFT JOIN Contacts AS Owner ON Companies.ID =
Contacts.ID AND Contacts.Owner = Yes

Your result should be one row for every company, whether they have zero,
one, or two officers. If instead what you want is one record per Person,
you'll need something a little different.
If this doesn't work for you, let me know. It would also help if you posted
the SQL of the original query, and a list of relevant table and field names.

 

[Guest]

I am sorry I didnot show the query i am using right now. This is how it looks:

SELECT Companies.ClientId AS Companies_ClientId, Companies.[Business Name],
Companies.[Bus Desc], Companies.LLC, Companies.SCorp, Companies.CCorp,
Companies.Partnership, Companies.[Inc Adr], Companies.RA, Companies.[RA-St],
Companies.[RA-St2], Companies.[RA City], Companies.[RA State], Companies.[RA
Zip], Companies.[RA-Phone], Companies.[RA-SSN], Companies.EIN,
Companies.County, Companies.[State of Inc], Companies.[Pr Office-St],
Companies.[Pr Office-City], Companies.[Pr Office-State], Companies.[Pr
Office-County], Companies.FY, Companies.[Date Bus Commenced],
Companies.[Stock-Single Class], Companies.[Stock-Auth],
Companies.[Stock-Multiple Class], Companies.[Class 1], Companies.[Class 2],
Companies.[Class 3], Companies.[Auth No-1], Companies.[Auth No-2],
Companies.[Auth No-3], Companies.[Shares Issued], Companies.[SH-Shares
Issued] AS [Companies_SH-Shares Issued], Companies.[Date Shares Acquired] AS
[Companies_Date Shares Acquired], Companies.[Sign Title], Companies.[Term
Date/ Dissolved], Companies.[Closing Date for LP/LLC], People.ClientId AS
People_ClientId, People.FirstName, People.[Last Name], People.Director,
People.Pres, People.SH, People.[SH- Name/Adr], People.[SH-SSN],
People.[SH-Shares Issued] AS [People_SH-Shares Issued], People.[Date Shares
Acquired] AS [People_Date Shares Acquired], People.[SH-Tax Yea],
People.Treasurer, People.Sec, People.Organizer, People.MGR, People.GP,
People.LP, People.Address1, People.Address2, People.City, People.State,
People.Zip, People.Phone, People.Fax, People.Date INTO test
FROM Companies INNER JOIN People ON Companies.ClientId = People.ClientId;

I just created this query as a test. that is why you see all the fields. But
when i ran the query i found the problem of two rows.

-------------------------------------------------------------------------------------

Now I am trying to implement what you suggested in your last response:

* Create new relationships between the Companies table and each of the
Contacts tables. Specify that each should include all records from Companies
table and only matching records from the Contacts table.
-----please let me know if this is way it will be done in the desgin view, I
see three tables. I deletd the earlier relationship between Companies and
contact(which is people table). Now to create new relationship: in both the
tables i have Client ID as the common field only in People, People 1 and
companies table.......
How will I a create the relationship that each field in companies table
should be in people, People1/contacts/Contacts1........

It might be my lack of understanding......

Thanks




*

Thank you for replying.
there are two tables:
Companies table
People table
i run a query which pull the info from both the tables and put it in query
table. I use this query table in mail merge code/
Companies table and people table are linked with Client ID fields
So when a pull the results with select query, i get something like this
Company Name, Client ID, address, Directors, Oweners, Full Name Etc.....
AAA 1 Xyz YEs Yes WQR
AAA 1 XYZ YES No QAS
Like this .........

Result come in two rows. For mail merge i need the whole record in one row.
Also I need the full name for directors, owners and other officers in that
single row..... Is there any way I can do this all .....
I am passing the ino through a Form which has both people and COmpany table
fields

For Mail merge I am using following code:
Function MergeIt()
Dim objWord As Word.Document

Set objWord = GetObject("C:\Documents and Settings\S\My Documents\test",
"Word.Document")
' Make Word visible.
objWord.Application.Visible = True

' Set the mail merge data source as the current database.
objWord.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="TABLE test", _
SQLStatement:="Select * from [test]"

' Execute the mail merge.
objWord.MailMerge.Execute

I donot know anyother easy way which involves less programming to update a
contract made in word from access/
Any help will be highly appreciated
Thank yu

That helps a little. If I understand you right, you want one Word document
for each Company, and the document should list the Director and Owner for
that Company.
I'm working blind here, without knowing exactly how your tables are
arranged, but hopefully you can use this as a starting point.

* Create a query in design view, with your Companies table and two copies of
the Contacts table.
* If you have an existing relationship between the Companies table and the
Contacts table, click it and hit delete (we're talking about the relationship
as it appears in your query, NOT the permanent relationship you see with View
| Relationships).
* Create new relationships between the Companies table and each of the
Contacts tables. Specify that each should include all records from Companies
table and only matching records from the Contacts table.
* Drag the fields you want into the table part of your query. Include the
Name field from each Contacts table, and include the Director field from one
table, the Owner field from the other.
* Change the Field from Name to DirectorName: Name for the first Name;
change the other one to OwnerName: Name.
* Set the Criteria to Yes for Director and Owner.

If you're familiar with SQL, this is what it should look like:

SELECT
Companies.Name, Companies.Address, Director.Name AS DirectorName, Owner.Name
AS OwnerName
FROM
(Companies LEFT JOIN Contacts AS Director ON Companies.ID = Contacts.ID AND
Contacts.Director = Yes) LEFT JOIN Contacts AS Owner ON Companies.ID =
Contacts.ID AND Contacts.Owner = Yes

Your result should be one row for every company, whether they have zero,
one, or two officers. If instead what you want is one record per Person,
you'll need something a little different.
If this doesn't work for you, let me know. It would also help if you posted
the SQL of the original query, and a list of relevant table and field names.

 

[Guest]

I am sorry I didnot show the query i am using right now. This is how it looks:

SELECT Companies.ClientId AS Companies_ClientId, Companies.[Business Name],
Companies.[Bus Desc], Companies.LLC, Companies.SCorp, Companies.CCorp,
Companies.Partnership, Companies.[Inc Adr], Companies.RA, Companies.[RA-St],
Companies.[RA-St2], Companies.[RA City], Companies.[RA State], Companies.[RA
Zip], Companies.[RA-Phone], Companies.[RA-SSN], Companies.EIN,
Companies.County, Companies.[State of Inc], Companies.[Pr Office-St],
Companies.[Pr Office-City], Companies.[Pr Office-State], Companies.[Pr
Office-County], Companies.FY, Companies.[Date Bus Commenced],
Companies.[Stock-Single Class], Companies.[Stock-Auth],
Companies.[Stock-Multiple Class], Companies.[Class 1], Companies.[Class 2],
Companies.[Class 3], Companies.[Auth No-1], Companies.[Auth No-2],
Companies.[Auth No-3], Companies.[Shares Issued], Companies.[SH-Shares
Issued] AS [Companies_SH-Shares Issued], Companies.[Date Shares Acquired] AS
[Companies_Date Shares Acquired], Companies.[Sign Title], Companies.[Term
Date/ Dissolved], Companies.[Closing Date for LP/LLC], People.ClientId AS
People_ClientId, People.FirstName, People.[Last Name], People.Director,
People.Pres, People.SH, People.[SH- Name/Adr], People.[SH-SSN],
People.[SH-Shares Issued] AS [People_SH-Shares Issued], People.[Date Shares
Acquired] AS [People_Date Shares Acquired], People.[SH-Tax Yea],
People.Treasurer, People.Sec, People.Organizer, People.MGR, People.GP,
People.LP, People.Address1, People.Address2, People.City, People.State,
People.Zip, People.Phone, People.Fax, People.Date INTO test
FROM Companies INNER JOIN People ON Companies.ClientId = People.ClientId;

I just created this query as a test. that is why you see all the fields. But
when i ran the query i found the problem of two rows.

-------------------------------------------------------------------------------------

Now I am trying to implement what you suggested in your last response:

* Create new relationships between the Companies table and each of the
Contacts tables. Specify that each should include all records from Companies
table and only matching records from the Contacts table.
-----please let me know if this is way it will be done in the desgin view, I
see three tables. I deletd the earlier relationship between Companies and
contact(which is people table). Now to create new relationship: in both the
tables i have Client ID as the common field only in People, People 1 and
companies table.......
How will I a create the relationship that each field in companies table
should be in people, People1/contacts/Contacts1........

It might be my lack of understanding......

Thanks

Good morning, SL, sorry for the delay.
So you have three tables showing, Companies, People, and People_1. To
create a relationship, click and drag from Companies.ClientId to
People.ClientId. Double-click on the resulting relationship line, and choose
"Include all records from 'Companies' and only those records from 'People'
where the joined fields are equal. Repeat with Companies and People_1.
Then, drag the People.Director field to the table below, and set its
Criteria to Yes. Drag People_1.Pres below, and also set its Criteria.
Finally, drag any other fields you want like Companies.[Business Name],
People.[Last Name], and People_1.[Last Name]. I recommend you stick to just
a few fields until you have the query working right, then add more.

On a design note, I strongly recommend you not use spaces in field names,
since they force you to use braces later on in your queries and code. If you
forget a brace or two, it can cause frustrating bugs. I personally like to
use mixed case (i.e., LastName, StreetAddress); some prefer to use
underscores (i.e., Last_Name, Street_Address).

Let me know if you have any trouble!

 

[Guest]

Would this work in a situation where each record may have several rows fo
values not just two rows?

I am sorry I didnot show the query i am using right now. This is how it looks:

SELECT Companies.ClientId AS Companies_ClientId, Companies.[Business Name],
Companies.[Bus Desc], Companies.LLC, Companies.SCorp, Companies.CCorp,
Companies.Partnership, Companies.[Inc Adr], Companies.RA, Companies.[RA-St],
Companies.[RA-St2], Companies.[RA City], Companies.[RA State], Companies.[RA
Zip], Companies.[RA-Phone], Companies.[RA-SSN], Companies.EIN,
Companies.County, Companies.[State of Inc], Companies.[Pr Office-St],
Companies.[Pr Office-City], Companies.[Pr Office-State], Companies.[Pr
Office-County], Companies.FY, Companies.[Date Bus Commenced],
Companies.[Stock-Single Class], Companies.[Stock-Auth],
Companies.[Stock-Multiple Class], Companies.[Class 1], Companies.[Class 2],
Companies.[Class 3], Companies.[Auth No-1], Companies.[Auth No-2],
Companies.[Auth No-3], Companies.[Shares Issued], Companies.[SH-Shares
Issued] AS [Companies_SH-Shares Issued], Companies.[Date Shares Acquired] AS
[Companies_Date Shares Acquired], Companies.[Sign Title], Companies.[Term
Date/ Dissolved], Companies.[Closing Date for LP/LLC], People.ClientId AS
People_ClientId, People.FirstName, People.[Last Name], People.Director,
People.Pres, People.SH, People.[SH- Name/Adr], People.[SH-SSN],
People.[SH-Shares Issued] AS [People_SH-Shares Issued], People.[Date Shares
Acquired] AS [People_Date Shares Acquired], People.[SH-Tax Yea],
People.Treasurer, People.Sec, People.Organizer, People.MGR, People.GP,
People.LP, People.Address1, People.Address2, People.City, People.State,
People.Zip, People.Phone, People.Fax, People.Date INTO test
FROM Companies INNER JOIN People ON Companies.ClientId = People.ClientId;

I just created this query as a test. that is why you see all the fields. But
when i ran the query i found the problem of two rows.

-------------------------------------------------------------------------------------

Now I am trying to implement what you suggested in your last response:

* Create new relationships between the Companies table and each of the
Contacts tables. Specify that each should include all records from Companies
table and only matching records from the Contacts table.
-----please let me know if this is way it will be done in the desgin view, I
see three tables. I deletd the earlier relationship between Companies and
contact(which is people table). Now to create new relationship: in both the
tables i have Client ID as the common field only in People, People 1 and
companies table.......
How will I a create the relationship that each field in companies table
should be in people, People1/contacts/Contacts1........

It might be my lack of understanding......

Thanks

Good morning, SL, sorry for the delay.
So you have three tables showing, Companies, People, and People_1. To
create a relationship, click and drag from Companies.ClientId to
People.ClientId. Double-click on the resulting relationship line, and choose
"Include all records from 'Companies' and only those records from 'People'
where the joined fields are equal. Repeat with Companies and People_1.
Then, drag the People.Director field to the table below, and set its
Criteria to Yes. Drag People_1.Pres below, and also set its Criteria.
Finally, drag any other fields you want like Companies.[Business Name],
People.[Last Name], and People_1.[Last Name]. I recommend you stick to just
a few fields until you have the query working right, then add more.

On a design note, I strongly recommend you not use spaces in field names,
since they force you to use braces later on in your queries and code. If you
forget a brace or two, it can cause frustrating bugs. I personally like to
use mixed case (i.e., LastName, StreetAddress); some prefer to use
underscores (i.e., Last_Name, Street_Address).

Let me know if you have any trouble!

 

[Guest]

Would this work in a situation where each record may have several rows fo
values not just two rows?

I'm not sure what you're asking. This technique can be expanded, yes. You
can keep adding copies of the People table, and corresponding left-handed
joins, for as many records as you need. However, this quickly creates a very
complex, slow, and confusing query.
A better technique involves normalizing your data. This is a whole topic to
itself, but the principles will help your database run much more smoothly.
In general, a normalized database stores all data once, and in one place, and
in the most general format.
In this case, what we should probably do is create a third table, Roles. It
would have three fields: Person, Company, and Title. You would add a record
to this table for each title. For example, Person A could be both Director
and President of School B, and also Treasurer for School C. There would be
three lines in the Roles table:
PersonA, SchoolB, "Director"
PersonA, SchoolB, "President"
PersonA, SchoolC, "Treasurer"
With a system like this, one person can have multiple roles with multiple
different companies. By making Director and President fields in the
Companies table, we need a join for each of these roles. If we decide in the
future that we also need to track Deans and Treasurers, we have to add two
new fields to the Companies table. If we had a special Roles table we'd just
add a record for each Dean and each Treasurer.
This is just an example. Good design is crucial to making a successful
database, especially if you expect to expand it in the future. For anyone
interested in getting the most out of Access, or any other database, I
strongly recommend picking up a book on database design, or trawling Google
for tutorials.

 

[Guest]

Thanks. Actually I was trying to do a mail merge with a query that had
multiple values for one record and print one letter listing all the values,
i.e., a billie id that had multiple contract associated with that id. Since
this is a one to many relationship and would be cumberson as you said unless
OLE code is written. I have since done this as a two page report in access
and now am running into the problem of letters spilling onto a second page
when the page length is not sufficient to handle all the records.

 

[Guest]

Thanks. Actually I was trying to do a mail merge with a query that had
multiple values for one record and print one letter listing all the values,
i.e., a billie id that had multiple contract associated with that id. Since
this is a one to many relationship and would be cumberson as you said unless
OLE code is written. I have since done this as a two page report in access
and now am running into the problem of letters spilling onto a second page
when the page length is not sufficient to handle all the records.

Gotcha. Do you want to cut it off when there are too many records to fit on
the first page? Or would you prefer to reduce the font size to make them fit?

To prevent the list from growing past a set point, try inserting a subform
with the contents of your query. You can set the subform as big as you want
it, and then set its CanGrow property to No and its CanShrink to Yes. When
the list is longer than the space alloted, it will simply be cut off.

To resize the text, add code to the form's OnFormat event. It should look
something like this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const MaxContractsFit As Integer = 10
If ContractsList.ItemData.Count > MaxContractsFit Then
ContractsList.Properties!FontSize =
ContractsList.Properties!FontSize / (ContractsList.ItemData.Count /
MaxContractsFit)
End If
End Sub

Hope that helps!

 

[Guest]

Thanks. I will try this on tomorrow.

Gotcha. Do you want to cut it off when there are too many records to fit on
the first page? Or would you prefer to reduce the font size to make them fit?

To prevent the list from growing past a set point, try inserting a subform
with the contents of your query. You can set the subform as big as you want
it, and then set its CanGrow property to No and its CanShrink to Yes. When
the list is longer than the space alloted, it will simply be cut off.

To resize the text, add code to the form's OnFormat event. It should look
something like this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const MaxContractsFit As Integer = 10
If ContractsList.ItemData.Count > MaxContractsFit Then
ContractsList.Properties!FontSize =
ContractsList.Properties!FontSize / (ContractsList.ItemData.Count /
MaxContractsFit)
End If
End Sub

Hope that helps!

 

[Guest]

Thank you so much for your help. I had some issues as a result could not work
on the probelm earlier. I worked on it yesterday. It is not working. my
query looks like:

SELECT Companies.[Business Name], People.FirstName AS DirName,
People.Director, People_1.Pres, People_1.FirstName AS PresName
FROM (Companies LEFT JOIN People ON Companies.ClientId = People.ClientId)
LEFT JOIN People AS People_1 ON Companies.ClientId = People_1.ClientId
WHERE (((People.Director)=Yes) AND ((People_1.Pres)=Yes));

Does it has anything to do with the check box value for directors and owners?

Wouldn't the check box value be in "Yes" or "No" format

Thanks you. Any help will be highly appreciated

Thank you for replying.
there are two tables:
Companies table
People table
i run a query which pull the info from both the tables and put it in query
table. I use this query table in mail merge code/
Companies table and people table are linked with Client ID fields
So when a pull the results with select query, i get something like this
Company Name, Client ID, address, Directors, Oweners, Full Name Etc.....
AAA 1 Xyz YEs Yes WQR
AAA 1 XYZ YES No QAS
Like this .........

Result come in two rows. For mail merge i need the whole record in one row.
Also I need the full name for directors, owners and other officers in that
single row..... Is there any way I can do this all .....
I am passing the ino through a Form which has both people and COmpany table
fields

For Mail merge I am using following code:
Function MergeIt()
Dim objWord As Word.Document

Set objWord = GetObject("C:\Documents and Settings\S\My Documents\test",
"Word.Document")
' Make Word visible.
objWord.Application.Visible = True

' Set the mail merge data source as the current database.
objWord.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="TABLE test", _
SQLStatement:="Select * from [test]"

' Execute the mail merge.
objWord.MailMerge.Execute

I donot know anyother easy way which involves less programming to update a
contract made in word from access/
Any help will be highly appreciated
Thank yu

That helps a little. If I understand you right, you want one Word document
for each Company, and the document should list the Director and Owner for
that Company.
I'm working blind here, without knowing exactly how your tables are
arranged, but hopefully you can use this as a starting point.

* Create a query in design view, with your Companies table and two copies of
the Contacts table.
* If you have an existing relationship between the Companies table and the
Contacts table, click it and hit delete (we're talking about the relationship
as it appears in your query, NOT the permanent relationship you see with View
| Relationships).
* Create new relationships between the Companies table and each of the
Contacts tables. Specify that each should include all records from Companies
table and only matching records from the Contacts table.
* Drag the fields you want into the table part of your query. Include the
Name field from each Contacts table, and include the Director field from one
table, the Owner field from the other.

* Change the Field from Name to DirectorName: Name for the first Name;
change the other one to OwnerName: Name.

---------------I try to do the above but in the design query I am getting it
as an express

 

[Guest]

Thank you so much for your help. I had some issues as a result could not work
on the probelm earlier. I worked on it yesterday. It is not working. my
query looks like:

SELECT Companies.[Business Name], People.FirstName AS DirName,
People.Director, People_1.Pres, People_1.FirstName AS PresName
FROM (Companies LEFT JOIN People ON Companies.ClientId = People.ClientId)
LEFT JOIN People AS People_1 ON Companies.ClientId = People_1.ClientId
WHERE (((People.Director)=Yes) AND ((People_1.Pres)=Yes));

Does it has anything to do with the check box value for directors and owners?

Wouldn't the check box value be in "Yes" or "No" format

Thanks you. Any help will be highly appreciated

Hmm. That looks right from here. I don't know exactly how your database is
laid out, though. Is it correct to link Companies to People with ClientID?
Are you getting an error, or incomplete results, or duplicates, or nothing at
all?