SQL errors in mail merge

[projectmergeleader]

I am doing a mail merge project that counts the number of instances of a
client in a table. When it trys to count the number of instances for a client
that is not present in the table then it does not produce the required
document. I guess this is because the sql command is throwing an error and
the document cannot handle it so it just skips the relevant invoice. Is there
any sort of try/catch statement that can be used within word?

Thanks

S

 

[Peter Jamieson]

Is there a reason why you cannot write the SQL so that it always succeeds
(i.e. eliminates the clients with no invoices, or whatever it is that causes
the problem? Maybe you could provide the SQL and say exactly in what
circumstances the SQL fails?

Although you can control the course of the Merge to an extent using Word's
MailMerge Events - and in this case, the MailMergeBeforeRecordMerge Event is
likely to be the one you need, but since Word itself is still fetching the
records, I don't think you will be able to trap errors in there or do a
try/catch that will catch this problem (probably worth a try though!)

 

[projectmergeleader]

The table is formatted as such

Client; number; description

and the sql is counting the number of instances where client is equal to
Client but if there is no Client in the table it does not create the invoice
although the sql just returns 0.

S

 

[projectmergeleader]

Looking at it again. When the query executes if there are no entries in the
table where the count statement is being executed. It does not even add it to
the table of results. But if there is one or more entry present it displays
the line as normal with the count at 1.

Thanks,

S

 

[Peter Jamieson]

I'm sorry, you're going to have to spell it out for me as I don't really
understand what you are doing. Remember, we can't look over your shoulder!

Are you by any chance doing a 1-many merge where the Mail Merge Data Source
is "Client" and you are using a DATABASE field to insert a variable number
of records that are related to that client?

Or what? Is the SQL you are talking about the SQL to fetch the data source
data or being executed in some other way.

 

[projectmergeleader]

Its a join issue.
SELECT [Address Table].address, Count(text_message_table.Message) AS
CountOfMessage, [Product Table].product_description, [Product Table].price,
[Address Table].pay_sms, Min(text_message_table.ReminderDue) AS
MinOfReminderDue, Max(text_message_table.ReminderDue) AS MaxOfReminderDue
FROM text_message_table INNER JOIN ([Address Table] INNER JOIN [Product
Table] ON [Address Table].client_id = [Product Table].client_id) ON
text_message_table.Client = [Address Table].client_id
WHERE (((text_message_table.ReminderDue) Between [Enter start date in the
format DD/MM/YYYY] And [Enter end date in the format DD/MM/YYYY]))
GROUP BY [Address Table].address, [Product Table].product_description,
[Product Table].price, [Address Table].pay_sms;

This is the sql but when I change the inner join to an outer join it
complains about ambigious joins. It is the text_message_table to address
table that is causing the problem as it will not join if there are no text
messages and therefore does not return a row in the result table. Any help
is very appreciated.

Thanks,

S

 

[Peter Jamieson]

I'll try to pick this up later or tomorrow, but which dialect of SQL is
this? It looks like Jet/Access, but I'd like to be sure it's not something
like Transact-SQL.

--
Peter Jamieson
http://tips.pjmsn.me.uk

Its a join issue.
SELECT [Address Table].address, Count(text_message_table.Message) AS
CountOfMessage, [Product Table].product_description, [Product
Table].price,
[Address Table].pay_sms, Min(text_message_table.ReminderDue) AS
MinOfReminderDue, Max(text_message_table.ReminderDue) AS MaxOfReminderDue
FROM text_message_table INNER JOIN ([Address Table] INNER JOIN [Product
Table] ON [Address Table].client_id = [Product Table].client_id) ON
text_message_table.Client = [Address Table].client_id
WHERE (((text_message_table.ReminderDue) Between [Enter start date in the
format DD/MM/YYYY] And [Enter end date in the format DD/MM/YYYY]))
GROUP BY [Address Table].address, [Product Table].product_description,
[Product Table].price, [Address Table].pay_sms;

This is the sql but when I change the inner join to an outer join it
complains about ambigious joins. It is the text_message_table to address
table that is causing the problem as it will not join if there are no text
messages and therefore does not return a row in the result table. Any
help
is very appreciated.

Thanks,

S

I'm sorry, you're going to have to spell it out for me as I don't really
understand what you are doing. Remember, we can't look over your
shoulder!

Are you by any chance doing a 1-many merge where the Mail Merge Data
Source
is "Client" and you are using a DATABASE field to insert a variable
number
of records that are related to that client?

Or what? Is the SQL you are talking about the SQL to fetch the data
source
data or being executed in some other way.

--
Peter Jamieson
http://tips.pjmsn.me.uk

in
message news[email protected]...

 

[projectmergeleader]

Its the default language in microsoft access so im gusessing it is Jet/access.

Thanks,

Steve

 

[projectmergeleader]

SELECT [Address Table].address, Count(text_message_table.Message) AS
CountOfMessage, [Product Table].product_description, [Product Table].price,
[Address Table].pay_sms, [Address Table].company_name
FROM [Product Table] INNER JOIN ([Address Table] LEFT JOIN
text_message_table ON [Address Table].client_id = text_message_table.Client)
ON [Product Table].client_id = [Address Table].client_id
WHERE (((text_message_table.ReminderDue) Between [Enter start date in the
format DD/MM/YYYY] And [Enter end date in the format DD/MM/YYYY]))
GROUP BY [Address Table].address, [Product Table].product_description,
[Product Table].price, [Address Table].pay_sms, [Address Table].company_name;

The join isnt actually the problem. I now have an outer join set up. The
problem is that the final table only contains entries for each address if
there exists a entry in the text_message_table between the date that are
entered. I need it to return all of the entries in the address table but only
count the number of messages between the dates. So I think the problem is in
the where clause.

Thanks,

S

Its the default language in microsoft access so im gusessing it is Jet/access.

Thanks,

Steve

 

[projectmergeleader]

I've got it sorted. I just needed to create a seperate query and then join
the two.

Thanks,

S

SELECT [Address Table].address, Count(text_message_table.Message) AS
CountOfMessage, [Product Table].product_description, [Product Table].price,
[Address Table].pay_sms, [Address Table].company_name
FROM [Product Table] INNER JOIN ([Address Table] LEFT JOIN
text_message_table ON [Address Table].client_id = text_message_table.Client)
ON [Product Table].client_id = [Address Table].client_id
WHERE (((text_message_table.ReminderDue) Between [Enter start date in the
format DD/MM/YYYY] And [Enter end date in the format DD/MM/YYYY]))
GROUP BY [Address Table].address, [Product Table].product_description,
[Product Table].price, [Address Table].pay_sms, [Address Table].company_name;

The join isnt actually the problem. I now have an outer join set up. The
problem is that the final table only contains entries for each address if
there exists a entry in the text_message_table between the date that are
entered. I need it to return all of the entries in the address table but only
count the number of messages between the dates. So I think the problem is in
the where clause.

Thanks,

S

 

[Peter Jamieson]

Glad you got it sorted as I only just managed to get back to it...

--
Peter Jamieson
http://tips.pjmsn.me.uk

I've got it sorted. I just needed to create a seperate query and then join
the two.

Thanks,

S

SELECT [Address Table].address, Count(text_message_table.Message) AS
CountOfMessage, [Product Table].product_description, [Product
Table].price,
[Address Table].pay_sms, [Address Table].company_name
FROM [Product Table] INNER JOIN ([Address Table] LEFT JOIN
text_message_table ON [Address Table].client_id =
text_message_table.Client)
ON [Product Table].client_id = [Address Table].client_id
WHERE (((text_message_table.ReminderDue) Between [Enter start date in the
format DD/MM/YYYY] And [Enter end date in the format DD/MM/YYYY]))
GROUP BY [Address Table].address, [Product Table].product_description,
[Product Table].price, [Address Table].pay_sms, [Address
Table].company_name;

The join isnt actually the problem. I now have an outer join set up. The
problem is that the final table only contains entries for each address if
there exists a entry in the text_message_table between the date that are
entered. I need it to return all of the entries in the address table but
only
count the number of messages between the dates. So I think the problem is
in
the where clause.

Thanks,

S

Its the default language in microsoft access so im gusessing it is
Jet/access.

Thanks,

Steve


:

I'll try to pick this up later or tomorrow, but which dialect of SQL
is
this? It looks like Jet/Access, but I'd like to be sure it's not
something
like Transact-SQL.