Unpaid Invoices to customers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Benedikt Fridbjornsson - 08 Sep 2004 12:49 GMT

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

Reply to this Message

Debbie - 08 Sep 2004 20:42 GMT

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

Reply to this Message

Benedikt Fridbjornsson - 09 Sep 2004 09:35 GMT

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
[quoted text clipped - 31 lines]
> Computer department
> SIF Iceland

Reply to this Message

Doug Robbins - 09 Sep 2004 10:26 GMT

I would suggest that you use a report in Access.

Signature

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
>
[quoted text clipped - 46 lines]
>> Computer department
>> SIF Iceland

Reply to this Message

Benedikt Fridbjornsson - 09 Sep 2004 11:31 GMT

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.
>
[quoted text clipped - 48 lines]
> >> Computer department
> >> SIF Iceland

Reply to this Message

Cindy M -WordMVP- - 13 Sep 2004 20:02 GMT

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 :-)

Reply to this Message