Mail merge using Microsoft Query - problems

[MrsDux]

I have a SQL Server database that contains the data I want in my mail merge
in a number of different tables. I've written a piece of SQL that displays
the correct information, which I've saved using Microsoft Query. When I set
up my Mail Merge to use this query as its Data Source it only returns data
for fields in one fo the tables. The column headings are there for the
others but not the actual data.

Why might this be and is there an easier way to do this? I only want 5
pieces of data!

 

[Peter Jamieson]

Which version of Word?

What is the SQL code?

Are the columns in the SQL Server database Unicode columns, i.e. with types
such as NVARCHAR, NCHAR etc. rather than VARCHAR? If so, Word cannot see
this data via ODBC, which is how it gets its data when you set up a
connection via MS Query.

If that's the problem, in Word 2000 and earlier, you can either change the
SQL Server column types (if that's up to you) or add CAST or CONVERT
functions into your SQL. In Word 2002 and later, you should be able to open
the data source using OLE DB (which doesn't have that problem) and use Word
VBA to specify the query, as long as it is short enough (the SQL statement
cannot be longer than either 255 or 511 characters - something like that).

 

[MrsDux]

Thanks
They are unicode columns, I'm in Word 2007, how do I open Word VBA?

 

[MrsDux]

I found VBA but unfortunately it seems my query is too long

SELECT 'FR' + Right('000000' + Cast(Defects.DefectId as Varchar),6) ,
Defects.Name, WorkflowSteps.StepName, Custom_207, Custom_159,
Customers.CompanyName FROM CustomerContacts , Customers, DefectCustomFields,
Defects, WorkflowSteps WHERE DefectCustomFields.DefectId = Defects.DefectId
AND Defects.WorkflowStepId = WorkflowSteps.WorkflowStepId AND
CustomerContacts.CustomerId = Customers.CustomerId AND
Defects.ReportedByCustomerContactId = CustomerContacts.CustomerContactId
WHERE Custom_159='" & Version & "'UNION
SELECT 'WK' + Right('000000' + Cast(Features.FeatureId as Varchar),6) ,
Features.Name , WorkflowSteps.StepName , Custom_163 , Custom_177,
Customers.CompanyName FROM CustomerContacts , Customers, FeatureCustomFields,
Features, WorkflowSteps WHERE FeatureCustomFields.FeatureId =
Features.FeatureId AND Features.WorkflowStepId = WorkflowSteps.WorkflowStepId
AND CustomerContacts.CustomerId = Customers.CustomerId AND
Features.RequestedByCustomerContactId = CustomerContacts.CustomerContactId
WHERE Custom_177='" & Version & "'

Back to the drawing board...

 

[Peter Jamieson]

Yes, I was only able to reduce it to around 600 or so characters using
1-character table alias names and * rather than individual field names and
assuming that the right('000000') function could be done somehow in Word,
but that's still too long and I can't see any opportunities for further
reductions.

Assuming you are not in a position to create views etc. in your SQL Server
database...

If you have Access you might be able to use that as a better intermediary
than MS Query (I am not sure in this case). Or if you have Excel and can do
the query as a separate step (e.g. do the query, save the file, do the
merge) you may be able to overcome the length restriction.