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" wrote:
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
"projectmergeleader" wrote in
message news
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
"projectmergeleader" wrote:
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
"Peter Jamieson" wrote:
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!)
--
Peter Jamieson
http://tips.pjmsn.me.uk
"projectmergeleader"
wrote in
message ...
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