Word 2003 mail merge using Access 2003 Query

[goldenbear]

I have Select Query in Access 2003, attached, that is run from a Word Mail merge and the data is put into the body of the email, example attached.

Upon selecting 2 records and clearing all the rest the sometime more than the 2records from the query get emailed, and we don't see this until after the wrong/extra records get sent.

Only thing I notice is that it seems to occur when someone else is using the DB to enter information but it's into an unrelated form/tables from the Selcet Query..

Has anyone else experienced this problem.

Is there a better, safer way to do this?

[NeoPa]

I'll have a quick peruse to see if I can see anything, but I'll also post the attached SQL as it's a bit hard for people to help with a SQL problem when it's not posted visibly :S

SELECT Acquisitions_Manuscript.title,
       Acquisitions_Manuscript.subtitle,
       Acquisitions_Manuscript.authors,
       Personal_Information.professional_title,
       Personal_Information.last_name,
       Personal_Information.full_name,
       Reader_Report_Information.due_date,
       Reader_Report_Information.honorarium_amount,
       Reader_Report_Information.number_honorarium_books,
       Reader_Report_Information.review_length,
       Reader_Report_Information.notes,
       Acquisitions_Manuscript.edited_volume,
       Acquisitions_Manuscript.acquisitions_editor,
       Staff.staff_title,
       Personal_Information.email
 
FROM   Personal_Information INNER JOIN
       ((Acquisitions_Manuscript INNER JOIN
       Staff
  ON   Acquisitions_Manuscript.acquisitions_editor=Staff.full_name) INNER JOIN
       Reader_Report_Information
  ON   Acquisitions_Manuscript.manuscript_id=Reader_Report_Information.manuscript_id)
  ON   Personal_Information.personal_id=Reader_Report_Information.personal_id

[NeoPa]

SELECT Acquisitions_Manuscript.title,
       Acquisitions_Manuscript.subtitle,
       Acquisitions_Manuscript.authors,
       Personal_Information.professional_title,
       Personal_Information.last_name,
       Personal_Information.full_name,
       Reader_Report_Information.due_date,
       Reader_Report_Information.honorarium_amount,
       Reader_Report_Information.number_honorarium_books,
       Reader_Report_Information.review_length,
       Reader_Report_Information.notes,
       Acquisitions_Manuscript.edited_volume,
       Acquisitions_Manuscript.acquisitions_editor,
       Staff.staff_title,
       Personal_Information.email
 
FROM   Personal_Information INNER JOIN
       ((Acquisitions_Manuscript INNER JOIN
       Staff
  ON   Acquisitions_Manuscript.acquisitions_editor=Staff.full_name) INNER JOIN
       Reader_Report_Information
  ON   Acquisitions_Manuscript.manuscript_id=Reader_Report_Information.manuscript_id)
  ON   Personal_Information.personal_id=Reader_Report_Information.personal_id

My first observation is that linking two tables on a FullName field is highly dubious. Any field which is not guaranteed unique (such as a unique index) makes it possible for multiple records to result from such linked SQL. I find it hard to believe a field such as Acquisitions_Manuscript.acquisitions_editor could be textual. If it's not a (probably numeric) unique reference then things are not well designed.

Nothing else springs to mind I'm afraid. Check that out though and see what you can make of it.

[NeoPa]

This is not a technical comment, but in the text of your HTML document the word "alternate" is used in place of "alternative". It may be an idea to replace this as the two words have quite different meanings. I hope this was some help.

[goldenbear]

Thanks NeoPa.

The Acquisitions_Manuscript.acquisitions_editor in a lookup as follows...

SELECT [Staff: Active Acquisitions].full_name
FROM [Staff: Active Acquisitions]
ORDER BY [full_name];

It is textual, do you still think this is/might be the problem?

[NeoPa]

I can't say I fully understand exactly what's going on from your description, but if that's not causing this problem (and it may well be) it's likely to cause you issues somewhere down the line. Certainly worth avoiding.

Are you aware that ComboBoxes can have multiple columns, any of which can be hidden, and any of which can be the value returned as the default .Value of the control. This means you can display the text value, yet have the code see the unique reference. This is the usual way to handle such things.

[goldenbear]

Yes, forgot about the ComboBox with multiple columns. Thanks again!

The real issue is reproducing & testing for the problem as it doesn't occur all the time and you don't know a wrong email is sent until it's too late. :-(

[NeoPa]

I sympathise. The best help I can provide at this point though is simply to link you to Debugging in VBA.

Good luck :)