subform results to be used for word mail merge

I

InACCESS_Able

Hi, My name is Ed and I'm a NooB here. I've been playing with Access fo
many years but don't know any real coding or sql. Usually I get thing
done by trial and error or finding answers on forums like this
However, I think I've wasted enough time trying to figure this out o
my own and I just wanna get it done!!

I have a big, hairy query that draws from several tables which I us
for a mail merge report in word. Demographics has a key by Hosp#
AVM/Tumor Details is related to Demographics by Hosp#, but the key i
Hosp# and Lesion#, in order to allow a many to one relationship wit
Demographics. I recently added a subform to my main form to accommodat
the addition of another level of many to one with AVM/Tumor details.

Now, I have the results of the query repeating information fo
everything except for the fields of the subform, which is what I wan
(I think).

I need to somehow give each result from the subform a unique ID so Wor
can read it in mailmerge.

thanks!

here's the sql:

SELECT Demographics.[LAST NAME], Demographics.[FIRST NAME]
Demographics.[HOSP #], [AVM/Tumor details].[DATE STRT], [AVM/Tumo
details].[LESION #], loc1.LOCATION, Key.HISTOLOGY, [type query].TYPE
[AVM/Tumor details].[HIST DESCRIPTION], [AVM/Tumor details].[VENOU
DRAINAGE], [AVM/Tumor details].[# DRAINING VEINS], [AVM/Tumo
details].[S&M AVM GRADE], [AVM/Tumor details].[# PRIOR HEMORRHAGES]
[AVM/Tumor details].[SURGERY DATE], [AVM/Tumor details].[NOTES 1]
[AVM/Tumor details].CT, [AVM/Tumor details].MRI, [AVM/Tumo
details].ANGIO, [AVM/Tumor details].sx, [AVM/Tumor details].MRI_Time
[AVM/Tumor details].Auth, [AVM/Tumor details].[UCLA Neurologist]
[AVM/Tumor details].[Evaluation For], [AVM/Tumor details].[RSPNS IM
#], [AVM/Tumor details].RadOnc, [AVM/Tumor details].CT_Time, [AVM/Tumo
details].MaskingTime, [AVM/Tumor details].MaskMaking, [AVM/Tumo
details].FU, [AVM/Tumor details].fxstart1, [AVM/Tumor details].fxend1
[AVM/Tumor details].radoncmd, [AVM/Tumor details].MRcuts, [AVM/Tumo
details].Physics, [AVM/Tumor details].Sex, [AVM/Tumo
details].[#lesionstreated], [AVM/Tumor details].IsodoseLine, [AVM/Tumo
details].Collimator, [AVM/Tumor details].[MaxDose cGy], [AVM/Tumo
details].[Volume cc], [AVM/Tumor details].NumberIsocenters, [AVM/Tumo
details].Fractions, [AVM/Tumor details].[T Dose], [AVM/Tumo
details].SIDE, [AVM/Tumor details].[AVM SIZE], [AVM/Tumo
details].Target, [AVM/Tumor details].Arcs, [AVM/Tumo
details].POSITION, [Ref MD].[FIRST NAME], [Ref MD].[LAST NAME], [Re
MD].ADDRESS, [Ref MD].[ADDRESS 2], [Ref MD].CITY, [Ref MD].STATE, [Re
MD].ZIP, [Ref MD].phone, [AVM/Tumor details].Beams, [AVM/Tumo
details].addscan, Demographics.Insurance, [AVM/Tumo
details].Indications, [AVM/Tumor details].complications, [cc md1].[LAS
NAME], [cc md1].[FIRST NAME], [cc md1].ADDRESS, [cc md1].[ADDRESS 2]
[cc md1].CITY, [cc md1].STATE, [cc md1].ZIP, [cc md2].[LAST NAME], [c
md2].[FIRST NAME], [cc md2].ADDRESS, [cc md2].[ADDRESS 2], [c
md2].CITY, [cc md2].STATE, [cc md2].ZIP, [cc md3].[LAST NAME], [c
md3].[FIRST NAME], [cc md3].ADDRESS, [cc md3].[ADDRESS 2], [c
md3].CITY, [cc md3].STATE, [cc md3].ZIP, [AVM/Tumor details].FxDose
[AVM/Tumor details].FxMaxDose, [AVM/Tumor details].recordID
qrydoseform.[Dose cGy], qrydoseform.IsodoseLine
qrydoseform.Collimator, qrydoseform.[MaxDose cGy], qrydoseform.[Volum
cc], qrydoseform.NumberIsocenters, qrydoseform.Fractions
qrydoseform.[T Dose], qrydoseform.Target, qrydoseform.Arcs
qrydoseform.Beams, qrydoseform.FxDose, qrydoseform.FxMaxDose
qrydoseform.TYPE, qrydoseform.DIAGNOSIS, qrydoseform.[HIS
DESCRIPTION], qrydoseform.Location, qrydoseform.SIDE
qrydoseform.POSITION
FROM qrydoseform INNER JOIN (((Demographics LEFT JOIN [Ref MD] O
Demographics.[REF MD ID] = [Ref MD].[REF MD ID]) LEFT JOIN [type query
ON Demographics.[HOSP #] = [type query].[HOSP #]) INNER JOI
((((([AVM/Tumor details] LEFT JOIN [Key] ON [AVM/Tumor details].[HIST#
= Key.[HIST#]) LEFT JOIN loc1 ON [AVM/Tumor details].[HOSP #]
loc1.[HOSP #]) LEFT JOIN [cc md3] ON [AVM/Tumor details].[HOSP #] = [c
md3].[HOSP #]) LEFT JOIN [cc md1] ON [AVM/Tumor details].[HOSP #] = [c
md1].[HOSP #]) LEFT JOIN [cc md2] ON [AVM/Tumor details].[HOSP #] = [cc
md2].[HOSP #]) ON Demographics.[HOSP #] = [AVM/Tumor details].[HOSP #])
ON qrydoseform.recordID = [AVM/Tumor details].recordID
WHERE ((([AVM/Tumor details].sx) Like "*radiosurgery*" Or ([AVM/Tumor
details].sx) Like "*fractionation*") AND (([AVM/Tumor
details].recordID)=[Forms]![RSreports]![recordID]));


Ed
 
M

[MVP] S.Clark

You can pump the data into a new table, which has an autonumber, or use the
ID that uniquely ID's each subform record.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

InACCESS_Able said:
Hi, My name is Ed and I'm a NooB here. I've been playing with Access for
many years but don't know any real coding or sql. Usually I get things
done by trial and error or finding answers on forums like this.
However, I think I've wasted enough time trying to figure this out on
my own and I just wanna get it done!!

I have a big, hairy query that draws from several tables which I use
for a mail merge report in word. Demographics has a key by Hosp#.
AVM/Tumor Details is related to Demographics by Hosp#, but the key is
Hosp# and Lesion#, in order to allow a many to one relationship with
Demographics. I recently added a subform to my main form to accommodate
the addition of another level of many to one with AVM/Tumor details.

Now, I have the results of the query repeating information for
everything except for the fields of the subform, which is what I want
(I think).

I need to somehow give each result from the subform a unique ID so Word
can read it in mailmerge.

thanks!

here's the sql:

SELECT Demographics.[LAST NAME], Demographics.[FIRST NAME],
Demographics.[HOSP #], [AVM/Tumor details].[DATE STRT], [AVM/Tumor
details].[LESION #], loc1.LOCATION, Key.HISTOLOGY, [type query].TYPE,
[AVM/Tumor details].[HIST DESCRIPTION], [AVM/Tumor details].[VENOUS
DRAINAGE], [AVM/Tumor details].[# DRAINING VEINS], [AVM/Tumor
details].[S&M AVM GRADE], [AVM/Tumor details].[# PRIOR HEMORRHAGES],
[AVM/Tumor details].[SURGERY DATE], [AVM/Tumor details].[NOTES 1],
[AVM/Tumor details].CT, [AVM/Tumor details].MRI, [AVM/Tumor
details].ANGIO, [AVM/Tumor details].sx, [AVM/Tumor details].MRI_Time,
[AVM/Tumor details].Auth, [AVM/Tumor details].[UCLA Neurologist],
[AVM/Tumor details].[Evaluation For], [AVM/Tumor details].[RSPNS IMG
#], [AVM/Tumor details].RadOnc, [AVM/Tumor details].CT_Time, [AVM/Tumor
details].MaskingTime, [AVM/Tumor details].MaskMaking, [AVM/Tumor
details].FU, [AVM/Tumor details].fxstart1, [AVM/Tumor details].fxend1,
[AVM/Tumor details].radoncmd, [AVM/Tumor details].MRcuts, [AVM/Tumor
details].Physics, [AVM/Tumor details].Sex, [AVM/Tumor
details].[#lesionstreated], [AVM/Tumor details].IsodoseLine, [AVM/Tumor
details].Collimator, [AVM/Tumor details].[MaxDose cGy], [AVM/Tumor
details].[Volume cc], [AVM/Tumor details].NumberIsocenters, [AVM/Tumor
details].Fractions, [AVM/Tumor details].[T Dose], [AVM/Tumor
details].SIDE, [AVM/Tumor details].[AVM SIZE], [AVM/Tumor
details].Target, [AVM/Tumor details].Arcs, [AVM/Tumor
details].POSITION, [Ref MD].[FIRST NAME], [Ref MD].[LAST NAME], [Ref
MD].ADDRESS, [Ref MD].[ADDRESS 2], [Ref MD].CITY, [Ref MD].STATE, [Ref
MD].ZIP, [Ref MD].phone, [AVM/Tumor details].Beams, [AVM/Tumor
details].addscan, Demographics.Insurance, [AVM/Tumor
details].Indications, [AVM/Tumor details].complications, [cc md1].[LAST
NAME], [cc md1].[FIRST NAME], [cc md1].ADDRESS, [cc md1].[ADDRESS 2],
[cc md1].CITY, [cc md1].STATE, [cc md1].ZIP, [cc md2].[LAST NAME], [cc
md2].[FIRST NAME], [cc md2].ADDRESS, [cc md2].[ADDRESS 2], [cc
md2].CITY, [cc md2].STATE, [cc md2].ZIP, [cc md3].[LAST NAME], [cc
md3].[FIRST NAME], [cc md3].ADDRESS, [cc md3].[ADDRESS 2], [cc
md3].CITY, [cc md3].STATE, [cc md3].ZIP, [AVM/Tumor details].FxDose,
[AVM/Tumor details].FxMaxDose, [AVM/Tumor details].recordID,
qrydoseform.[Dose cGy], qrydoseform.IsodoseLine,
qrydoseform.Collimator, qrydoseform.[MaxDose cGy], qrydoseform.[Volume
cc], qrydoseform.NumberIsocenters, qrydoseform.Fractions,
qrydoseform.[T Dose], qrydoseform.Target, qrydoseform.Arcs,
qrydoseform.Beams, qrydoseform.FxDose, qrydoseform.FxMaxDose,
qrydoseform.TYPE, qrydoseform.DIAGNOSIS, qrydoseform.[HIST
DESCRIPTION], qrydoseform.Location, qrydoseform.SIDE,
qrydoseform.POSITION
FROM qrydoseform INNER JOIN (((Demographics LEFT JOIN [Ref MD] ON
Demographics.[REF MD ID] = [Ref MD].[REF MD ID]) LEFT JOIN [type query]
ON Demographics.[HOSP #] = [type query].[HOSP #]) INNER JOIN
((((([AVM/Tumor details] LEFT JOIN [Key] ON [AVM/Tumor details].[HIST#]
= Key.[HIST#]) LEFT JOIN loc1 ON [AVM/Tumor details].[HOSP #] =
loc1.[HOSP #]) LEFT JOIN [cc md3] ON [AVM/Tumor details].[HOSP #] = [cc
md3].[HOSP #]) LEFT JOIN [cc md1] ON [AVM/Tumor details].[HOSP #] = [cc
md1].[HOSP #]) LEFT JOIN [cc md2] ON [AVM/Tumor details].[HOSP #] = [cc
md2].[HOSP #]) ON Demographics.[HOSP #] = [AVM/Tumor details].[HOSP #])
ON qrydoseform.recordID = [AVM/Tumor details].recordID
WHERE ((([AVM/Tumor details].sx) Like "*radiosurgery*" Or ([AVM/Tumor
details].sx) Like "*fractionation*") AND (([AVM/Tumor
details].recordID)=[Forms]![RSreports]![recordID]));


Ed
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Names and address list in Excel-mail merge to Word for labels 2

Top