|
|
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!
|
|
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).
--
Peter Jamieson
http://tips.pjmsn.me.uk
"MrsDux" <MrsDux[ at ]discussions.microsoft.com> wrote in message
news:06C35AD3-44F5-4FE0-89E6-F4F6B27F84B6[ at ]microsoft.com...
[Quoted Text] >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!
|
|
Thanks
They are unicode columns, I'm in Word 2007, how do I open Word VBA?
"Peter Jamieson" wrote:
[Quoted Text] > 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). > > > -- > Peter Jamieson > http://tips.pjmsn.me.uk> > "MrsDux" <MrsDux[ at ]discussions.microsoft.com> wrote in message > news:06C35AD3-44F5-4FE0-89E6-F4F6B27F84B6[ at ]microsoft.com... > >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! > > |
|
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...
"MrsDux" wrote:
[Quoted Text] > 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!
|
|
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.
--
Peter Jamieson
http://tips.pjmsn.me.uk
"MrsDux" <MrsDux[ at ]discussions.microsoft.com> wrote in message
news:2A13E75F-9E68-429D-BB37-BE250B743D65[ at ]microsoft.com...
[Quoted Text] >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...
>
> "MrsDux" wrote:
>
>> 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!
|
|
|