Data missing in Mailmerge from Access query

[Sally Smith]

When merging patient data from an Access 2003 query, some fields come into
the MailMerge document but others do not appear, even though we can see them
in the query table. Some fields in some rows contain no data. The only
switches we used were to format dates. Why do some fields not populate?
Thanks for any advice.

 

[Doug Robbins - Word MVP]

I have never experienced that problem. What type of data is in the fields?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Peter Jamieson]

Do you have the SQL code for the query?

 

[Sally Smith]

Here's the SQL code!! Don't laugh....the query may be long & complex, but we
do get all the data we ask for in the query result. The issue is that the
Radiotherapy and Antineoplastic items don't appear in the MailMerge document,
but the Surgery items do:

SELECT DISTINCTROW qryAllPatientAndDiseaseRecords.Surname,
qryAllPatientAndDiseaseRecords.[Given Name],
qryAllPatientAndDiseaseRecords.Title, qryAllPatientAndDiseaseRecords.Sex,
qryAllPatientAndDiseaseRecords.[Date Of Birth],
qryAllPatientAndDiseaseRecords.[Country of Birth],
qryAllPatientAndDiseaseRecords.[Indigenous Status Code],
qryAllPatientAndDiseaseRecords.[Street/Address],
qryAllPatientAndDiseaseRecords.Suburb,
qryAllPatientAndDiseaseRecords.Postcode, qryAllPatientAndDiseaseRecords.[Date
of Death], qryAllPatientAndDiseaseRecords.[Disease Record Id],
qryAllPatientAndDiseaseRecords.[Date of Primary Cancer Diagnosis],
qryAllPatientAndDiseaseRecords.[Definitive Site of Cancer (ICD Code],
xtblPrimarySiteLookup.Site, qryAllPatientAndDiseaseRecords.[Cancer Site
Group], qryAllPatientAndDiseaseRecords.[Best Basis for Diagnosis],
qryAllPatientAndDiseaseRecords.Laterality,
qryAllPatientAndDiseaseRecords.[Histopathological Grade],
qryAllPatientAndDiseaseRecords.[Morphology of Cancer (ICD Code)],
qryAllPatientAndDiseaseRecords.[Morphology of Cancer],
qryAllPatientAndDiseaseRecords.[T Stage], qryAllPatientAndDiseaseRecords.[N
Stage], qryAllPatientAndDiseaseRecords.[M Stage],
qryAllPatientAndDiseaseRecords.[Other Staging Basis],
qryAllPatientAndDiseaseRecords.[Other Staging Scheme],
qryAllPatientAndDiseaseRecords.[Scheme Group],
qryAllPatientAndDiseaseRecords.[Disease Entry Status],
qryProcedureCodesTabularWithSurgeon.[Facility Code],
qryProcedureCodesTabularWithSurgeon.MRN,
qryProcedureCodesTabularWithSurgeon.[AMO Registration Number],
qryProcedureCodesTabularWithSurgeon.internet_display_name,
qryProcedureCodesTabularWithSurgeon.[Admission Date],
qryProcedureCodesTabularWithSurgeon.Procedure1,
qryProcedureCodesTabularWithSurgeon.[Surgical Procedure Descriptor 1],
qryProcedureCodesTabularWithSurgeon.[Procedure 2],
qryProcedureCodesTabularWithSurgeon.[Surgical Procedure Descriptor 2],
qryProcedureCodesTabularWithSurgeon.[Procedure 3],
qryProcedureCodesTabularWithSurgeon.[Surgical Procedure Descriptor 3],
qryProcedureCodesTabularWithSurgeon.[Discharge Date],
qryRadioFilteredByDiseaseCodeRange.[Facility Code],
qryRadioFilteredByDiseaseCodeRange.MRN,
qryRadioFilteredByDiseaseCodeRange.[AMO Registration Number],
qryRadioFilteredByDiseaseCodeRange.internet_display_name,
qryRadioFilteredByDiseaseCodeRange.[Radiotherapy Start Date],
qryRadioFilteredByDiseaseCodeRange.[Radiotherapy Type],
qryRadioFilteredByDiseaseCodeRange.[Radiation Dose],
qryRadioFilteredByDiseaseCodeRange.[Number of Radiation Fractions],
qryRadioFilteredByDiseaseCodeRange.[Radiotherapy End Date],
qryRadioFilteredByDiseaseCodeRange.[Date of Referral to Specialist],
qryRadioFilteredByDiseaseCodeRange.[Date of Consultation with Specialist],
qryChemoFilteredByDiseaseCodeRange.[Facility Code],
qryChemoFilteredByDiseaseCodeRange.MRN,
qryChemoFilteredByDiseaseCodeRange.[AMO Registration Number],
qryChemoFilteredByDiseaseCodeRange.internet_display_name,
qryChemoFilteredByDiseaseCodeRange.[Anti-Neoplastic Start Date],
qryChemoFilteredByDiseaseCodeRange.[Protocol Unique Identifier],
qryChemoFilteredByDiseaseCodeRange.[Protocol Acronym],
qryChemoFilteredByDiseaseCodeRange.[Number of Cycles],
qryChemoFilteredByDiseaseCodeRange.[Anti-Neoplastic End Date],
qryChemoFilteredByDiseaseCodeRange.[Date of Referral to Specialist],
qryChemoFilteredByDiseaseCodeRange.[Date of Consultation with Specialist],
dtblAllClinCRQOCRecords.[Date First Clinical Trial Enrolment],
dtblAllClinCRQOCRecords.[Date First MTeam Consultation],
dtblAllClinCRQOCRecords.[Palliative Care Status],
dtblAllClinCRQOCRecords.[Date of Referral to Palliative Care],
dtblAllClinCRQOCRecords.[Performance Status Code at Diag],
dtblAllClinCRQOCRecords.[Psycho Social Referral],
dtblAllClinCRQOCRecords.[Distress Thermometer Score]
FROM ((((qryAllPatientAndDiseaseRecords INNER JOIN xtblPrimarySiteLookup ON
qryAllPatientAndDiseaseRecords.[Definitive Site of Cancer (ICD Code] =
xtblPrimarySiteLookup.ICD10Code) LEFT JOIN
qryProcedureCodesTabularWithSurgeon ON
qryAllPatientAndDiseaseRecords.[Disease Record Id] =
qryProcedureCodesTabularWithSurgeon.[Disease Record ID]) LEFT JOIN
qryRadioFilteredByDiseaseCodeRange ON qryAllPatientAndDiseaseRecords.[Disease
Record Id] = qryRadioFilteredByDiseaseCodeRange.[Disease Record Id]) LEFT
JOIN qryChemoFilteredByDiseaseCodeRange ON
qryAllPatientAndDiseaseRecords.[Disease Record Id] =
qryChemoFilteredByDiseaseCodeRange.[Disease Record ID]) LEFT JOIN
dtblAllClinCRQOCRecords ON qryAllPatientAndDiseaseRecords.[Disease Record Id]
= dtblAllClinCRQOCRecords.[Disease Record Id]
WHERE (((qryAllPatientAndDiseaseRecords.[Definitive Site of Cancer (ICD
Code]) Between "C51" And "C59.9" Or
(qryAllPatientAndDiseaseRecords.[Definitive Site of Cancer (ICD Code]) Like
"C48*" Or (qryAllPatientAndDiseaseRecords.[Definitive Site of Cancer (ICD
Code])="C45.1"))
ORDER BY qryAllPatientAndDiseaseRecords.Surname;

Thanks,
Sally.--
Sally Smith

 

[Sally Smith]

Data in the fields is number, text or date/time. None of them is particularly
long.
Sally.

 

[Peter Jamieson]

I wondered if something might stand out in the code, but it doesn't.

In this kind of situation, I'd either try a number of experiments, or use
Access facilities to export the data into another format and use that.

As experiments I'd probably
a. try the three different connection methods available (check
Word->Tools->Options->General->Confirm conversion at open, and reconnect
b. see if the individual items appeared in Word if I used much simpler
queries as my data source.
c. perhaps alter the sequence of the fields in the query

I'd also be wondering whether all the data was held in a .mdb or maybe in a
SQL Server/MSDE type database, and in the latter case, whether there might
be something unusual about the field types.

RIght now I can't think of anything more than that.

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

Here's the SQL code!! Don't laugh....the query may be long & complex, but
we
do get all the data we ask for in the query result. The issue is that the
Radiotherapy and Antineoplastic items don't appear in the MailMerge
document,
but the Surgery items do:

SELECT DISTINCTROW qryAllPatientAndDiseaseRecords.Surname,
qryAllPatientAndDiseaseRecords.[Given Name],
qryAllPatientAndDiseaseRecords.Title, qryAllPatientAndDiseaseRecords.Sex,
qryAllPatientAndDiseaseRecords.[Date Of Birth],
qryAllPatientAndDiseaseRecords.[Country of Birth],
qryAllPatientAndDiseaseRecords.[Indigenous Status Code],
qryAllPatientAndDiseaseRecords.[Street/Address],
qryAllPatientAndDiseaseRecords.Suburb,
qryAllPatientAndDiseaseRecords.Postcode,
qryAllPatientAndDiseaseRecords.[Date
of Death], qryAllPatientAndDiseaseRecords.[Disease Record Id],
qryAllPatientAndDiseaseRecords.[Date of Primary Cancer Diagnosis],
qryAllPatientAndDiseaseRecords.[Definitive Site of Cancer (ICD Code],
xtblPrimarySiteLookup.Site, qryAllPatientAndDiseaseRecords.[Cancer Site
Group], qryAllPatientAndDiseaseRecords.[Best Basis for Diagnosis],
qryAllPatientAndDiseaseRecords.Laterality,
qryAllPatientAndDiseaseRecords.[Histopathological Grade],
qryAllPatientAndDiseaseRecords.[Morphology of Cancer (ICD Code)],
qryAllPatientAndDiseaseRecords.[Morphology of Cancer],
qryAllPatientAndDiseaseRecords.[T Stage],
qryAllPatientAndDiseaseRecords.[N
Stage], qryAllPatientAndDiseaseRecords.[M Stage],
qryAllPatientAndDiseaseRecords.[Other Staging Basis],
qryAllPatientAndDiseaseRecords.[Other Staging Scheme],
qryAllPatientAndDiseaseRecords.[Scheme Group],
qryAllPatientAndDiseaseRecords.[Disease Entry Status],
qryProcedureCodesTabularWithSurgeon.[Facility Code],
qryProcedureCodesTabularWithSurgeon.MRN,
qryProcedureCodesTabularWithSurgeon.[AMO Registration Number],
qryProcedureCodesTabularWithSurgeon.internet_display_name,
qryProcedureCodesTabularWithSurgeon.[Admission Date],
qryProcedureCodesTabularWithSurgeon.Procedure1,
qryProcedureCodesTabularWithSurgeon.[Surgical Procedure Descriptor 1],
qryProcedureCodesTabularWithSurgeon.[Procedure 2],
qryProcedureCodesTabularWithSurgeon.[Surgical Procedure Descriptor 2],
qryProcedureCodesTabularWithSurgeon.[Procedure 3],
qryProcedureCodesTabularWithSurgeon.[Surgical Procedure Descriptor 3],
qryProcedureCodesTabularWithSurgeon.[Discharge Date],
qryRadioFilteredByDiseaseCodeRange.[Facility Code],
qryRadioFilteredByDiseaseCodeRange.MRN,
qryRadioFilteredByDiseaseCodeRange.[AMO Registration Number],
qryRadioFilteredByDiseaseCodeRange.internet_display_name,
qryRadioFilteredByDiseaseCodeRange.[Radiotherapy Start Date],
qryRadioFilteredByDiseaseCodeRange.[Radiotherapy Type],
qryRadioFilteredByDiseaseCodeRange.[Radiation Dose],
qryRadioFilteredByDiseaseCodeRange.[Number of Radiation Fractions],
qryRadioFilteredByDiseaseCodeRange.[Radiotherapy End Date],
qryRadioFilteredByDiseaseCodeRange.[Date of Referral to Specialist],
qryRadioFilteredByDiseaseCodeRange.[Date of Consultation with Specialist],
qryChemoFilteredByDiseaseCodeRange.[Facility Code],
qryChemoFilteredByDiseaseCodeRange.MRN,
qryChemoFilteredByDiseaseCodeRange.[AMO Registration Number],
qryChemoFilteredByDiseaseCodeRange.internet_display_name,
qryChemoFilteredByDiseaseCodeRange.[Anti-Neoplastic Start Date],
qryChemoFilteredByDiseaseCodeRange.[Protocol Unique Identifier],
qryChemoFilteredByDiseaseCodeRange.[Protocol Acronym],
qryChemoFilteredByDiseaseCodeRange.[Number of Cycles],
qryChemoFilteredByDiseaseCodeRange.[Anti-Neoplastic End Date],
qryChemoFilteredByDiseaseCodeRange.[Date of Referral to Specialist],
qryChemoFilteredByDiseaseCodeRange.[Date of Consultation with Specialist],
dtblAllClinCRQOCRecords.[Date First Clinical Trial Enrolment],
dtblAllClinCRQOCRecords.[Date First MTeam Consultation],
dtblAllClinCRQOCRecords.[Palliative Care Status],
dtblAllClinCRQOCRecords.[Date of Referral to Palliative Care],
dtblAllClinCRQOCRecords.[Performance Status Code at Diag],
dtblAllClinCRQOCRecords.[Psycho Social Referral],
dtblAllClinCRQOCRecords.[Distress Thermometer Score]
FROM ((((qryAllPatientAndDiseaseRecords INNER JOIN xtblPrimarySiteLookup
ON
qryAllPatientAndDiseaseRecords.[Definitive Site of Cancer (ICD Code] =
xtblPrimarySiteLookup.ICD10Code) LEFT JOIN
qryProcedureCodesTabularWithSurgeon ON
qryAllPatientAndDiseaseRecords.[Disease Record Id] =
qryProcedureCodesTabularWithSurgeon.[Disease Record ID]) LEFT JOIN
qryRadioFilteredByDiseaseCodeRange ON
qryAllPatientAndDiseaseRecords.[Disease
Record Id] = qryRadioFilteredByDiseaseCodeRange.[Disease Record Id]) LEFT
JOIN qryChemoFilteredByDiseaseCodeRange ON
qryAllPatientAndDiseaseRecords.[Disease Record Id] =
qryChemoFilteredByDiseaseCodeRange.[Disease Record ID]) LEFT JOIN
dtblAllClinCRQOCRecords ON qryAllPatientAndDiseaseRecords.[Disease Record
Id]
= dtblAllClinCRQOCRecords.[Disease Record Id]
WHERE (((qryAllPatientAndDiseaseRecords.[Definitive Site of Cancer (ICD
Code]) Between "C51" And "C59.9" Or
(qryAllPatientAndDiseaseRecords.[Definitive Site of Cancer (ICD Code])
Like
"C48*" Or (qryAllPatientAndDiseaseRecords.[Definitive Site of Cancer (ICD
Code])="C45.1"))
ORDER BY qryAllPatientAndDiseaseRecords.Surname;

Thanks,
Sally.--
Sally Smith

Do you have the SQL code for the query?