Mail Merge Query

[ejf071189]

I have a table of clients and a table of accounts for those clients.

Client_Id is a one-many relationship with account_owner.

I want to make a query to be used in a mail merge being sent to clients that lists all of their accounts, but in the queries records it just lists a given client's name once for each account, giving me many records for each client.

Is there anyway to consolidate these into one record with all the accounts listed or manipulate the mail merge system to give me one letter with all the account listed?

Thanks in advance any input.

--Evan

 

[Guus2005]

You have to join the two tables.

Select * from tableA inner join tableB on ClientID = Account_OwnerID

something like this.

If this doesn't help, please post the query.


HTH

 

[ejf071189]

(SELECT c_lname, ACCT_No 
FROM Clients INNER JOIN Accounts 
ON Clients.c_id = Accounts.ACCT_id)

Gave me the following error:

"you have written a subquery that can return more than one field without using the exists reserved word in the main query's from clause, revise the select statement of the subquery to return only one field"

So I removed ACCT_no from the code, which resulted in an error saying it could return at most one record.

Also, I'd preferably use "Client Name" in place of "c_lname", but "Client Name" is from a query that concatenates the clients' full names and I'm not sure if query fields are allowed in these statements.

Can you see anything immediately wrong with this setup that would cause this, or should I post a sample of the database for you to see the actual query/tables for yourself?

 

[Guus2005]

Seems to me this is not the complete query since you don't use the exists keyword in your query. If you do use the exists keyword, you have to know that the keyword only refers to a single field or a concatenated field.

Fieldnames shouldnt have spaces in them. You could use

Select c_lname as "Client Name" from Clients

to display "Client Name" instead of "c_lname".

Enjoy!

 

[apr pillai]

Mail Merge

take a look at this link

http://www.msaccesstips.com/2007/10/ms-access-and-mail-merge-2.shtml