Unpaid Invoices to customers

[Benedikt Fridbjornsson]

Hi everyone



I want to send summary of unpaid invoices to my customers. I have created
Mail Merge document in Word 2003 that is connected to Access Query. My
problem is that when I merge to new document I get every customer but I only
want those who have unpaid invoices. I have two queries one with my
customers and another with my unpaid invoices. This is what I am trying to
do.



My Queries are

Customers and Unpaid Invoices



Dear {MERGEFIELD "CustomerNo"}



{DATABASE \d "C:\\Data\\test.mdb" \c "DSN=MS Access 2000
Database;DBQ=C:\\Data\\test.mdb; DriverId=25;FIL=MS
Access;MaxBufferSize=512;PageTimeout=5;" \s "Select DocumentNr AS Invoice,
DueDate, Unpaid from [Unpaid Invoices] INNER JOIN Customers ON
Customers.CustomerNo = [Unpaid Invoices.CustomerNo WHERE
Customers.CustomerNo = '{MERGEFIELD "CustomerNo"}' " \l "4" \b "447" \h}



regards,

Benedikt Fridbjornsson

Computer department

SIF Iceland

 

[Debbie]

That may be your query, but what do you have as your
criteria in your query? Do you have a date paid column
where you can put in 'is null' as the criteria so that
only the ones that are null show up in your query?


*** Original Message ***
Hi everyone

I want to send summary of unpaid invoices to my customers.
I have created
Mail Merge document in Word 2003 that is connected to
Access Query. My problem is that when I merge to new
document I get every customer but I only want those who
have unpaid invoices. I have two queries one with my
customers and another with my unpaid invoices. This is
what I am trying to do.

My Queries are Customers and Unpaid Invoices

Dear {MERGEFIELD "CustomerNo"}

{DATABASE \d "C:\\Data\\test.mdb" \c "DSN=MS Access 2000
Database;DBQ=C:\\Data\\test.mdb; DriverId=25;FIL=MS
Access;MaxBufferSize=512;PageTimeout=5;" \s "Select
DocumentNr AS Invoice,
DueDate, Unpaid from [Unpaid Invoices] INNER JOIN
Customers ON
Customers.CustomerNo = [Unpaid Invoices.CustomerNo WHERE
Customers.CustomerNo = '{MERGEFIELD "CustomerNo"}' "
\l "4" \b "447" \h}

regards,
Benedikt Fridbjornsson
Computer department
SIF Iceland

 

[Benedikt Fridbjornsson]

Hi Debbie

I have two queries in Access

My Customer qurey => All Customer there is no criteria
My Unpaid Invoices qurey => All Unpaid Invoices that are overdue. This is
what I want to send to my customers. Some of them have more then one unpaid
Invoices.

regards,
Benedikt Fridbjornsson
Computer department
SIF Iceland

 

[Doug Robbins]

I would suggest that you use a report in Access.

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

Hi Debbie

I have two queries in Access

My Customer qurey => All Customer there is no criteria
My Unpaid Invoices qurey => All Unpaid Invoices that are overdue. This is
what I want to send to my customers. Some of them have more then one
unpaid
Invoices.

regards,
Benedikt Fridbjornsson
Computer department
SIF Iceland

That may be your query, but what do you have as your
criteria in your query? Do you have a date paid column
where you can put in 'is null' as the criteria so that
only the ones that are null show up in your query?


*** Original Message ***
Hi everyone

I want to send summary of unpaid invoices to my customers.
I have created
Mail Merge document in Word 2003 that is connected to
Access Query. My problem is that when I merge to new
document I get every customer but I only want those who
have unpaid invoices. I have two queries one with my
customers and another with my unpaid invoices. This is
what I am trying to do.

My Queries are Customers and Unpaid Invoices

Dear {MERGEFIELD "CustomerNo"}

{DATABASE \d "C:\\Data\\test.mdb" \c "DSN=MS Access 2000
Database;DBQ=C:\\Data\\test.mdb; DriverId=25;FIL=MS
Access;MaxBufferSize=512;PageTimeout=5;" \s "Select
DocumentNr AS Invoice,
DueDate, Unpaid from [Unpaid Invoices] INNER JOIN
Customers ON
Customers.CustomerNo = [Unpaid Invoices.CustomerNo WHERE
Customers.CustomerNo = '{MERGEFIELD "CustomerNo"}' "
\l "4" \b "447" \h}

regards,
Benedikt Fridbjornsson
Computer department
SIF Iceland

 

[Benedikt Fridbjornsson]

Is it possible to create Access report per customer and send it by E-mail
like "Maile Merge to E-mail"

regards,
Benedikt Fridbjornsson
Computer department
SIF Iceland

I would suggest that you use a report in Access.

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

Hi Debbie

I have two queries in Access

My Customer qurey => All Customer there is no criteria
My Unpaid Invoices qurey => All Unpaid Invoices that are overdue. This is
what I want to send to my customers. Some of them have more then one
unpaid
Invoices.

regards,
Benedikt Fridbjornsson
Computer department
SIF Iceland

That may be your query, but what do you have as your
criteria in your query? Do you have a date paid column
where you can put in 'is null' as the criteria so that
only the ones that are null show up in your query?


*** Original Message ***
Hi everyone

I want to send summary of unpaid invoices to my customers.
I have created
Mail Merge document in Word 2003 that is connected to
Access Query. My problem is that when I merge to new
document I get every customer but I only want those who
have unpaid invoices. I have two queries one with my
customers and another with my unpaid invoices. This is
what I am trying to do.

My Queries are Customers and Unpaid Invoices

Dear {MERGEFIELD "CustomerNo"}

{DATABASE \d "C:\\Data\\test.mdb" \c "DSN=MS Access 2000
Database;DBQ=C:\\Data\\test.mdb; DriverId=25;FIL=MS
Access;MaxBufferSize=512;PageTimeout=5;" \s "Select
DocumentNr AS Invoice,
DueDate, Unpaid from [Unpaid Invoices] INNER JOIN
Customers ON
Customers.CustomerNo = [Unpaid Invoices.CustomerNo WHERE
Customers.CustomerNo = '{MERGEFIELD "CustomerNo"}' "
\l "4" \b "447" \h}

regards,
Benedikt Fridbjornsson
Computer department
SIF Iceland

 

[Cindy M -WordMVP-]

Hi Benedikt,

I want to send summary of unpaid invoices to my customers. I have created
Mail Merge document in Word 2003 that is connected to Access Query. My
problem is that when I merge to new document I get every customer but I only
want those who have unpaid invoices. I have two queries one with my
customers and another with my unpaid invoices. This is what I am trying to
do.

You'll find a discussion on how to set up "one-to-many" mail merges in the
"Special merges" section of my website's mail merge FAQ. I think you can use
the Database field approach, although I'm not sure it will update properly
merging directly to Email.

But you should be able to adapt Doug Robbins' macro for sending email with
attachments to process the merge result to email, if it doesn't work the way
you wish. You'll find that on the word.mvps.org website.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail