Multi table query

[hkraft]

Here's my problem...

I need to sort information by physician name. The name is pulled from
[MDname] in the DICTMD table and is derived from [ATTNPHYS] in the Master
table. The problem is that the [ATTNPhys] used to contain values from only
one field in the DICTMD table, [License #]. Now, however, it can contain
either [License #] or [NPI].

The query works fine when I use [License] or use [NPI], but I need to be
able to get the [MDName] for either filed.

Please help... SQL view below:

SELECT Master.ATTNPHYS, First([Last Name] & ", " & [First Name]) AS [MD
Name], First([DictMD with Address].[Group ID]) AS GroupID,
First(DictMDGroup.GroupName) AS GroupNM, First([DictMD with
Address].[Specialty Code]) AS SpecCode,
First(DICTSPEC.[Primary-Specialty-Surgical]) AS PMS, First(DICTSPEC.SPECNAME)
AS SpecNM, Sum(IIf([hoscode]=xxxxx And [disdate] Between
DateAdd("yyyy",-1,Forms![Get Dates]![Start Date]) And
DateAdd("yyyy",-1,Forms![Get Dates]![End Date]),1,0)) AS [MMC Previous],
Sum(IIf([hoscode]=zzzzz And [disdate] Between DateAdd("yyyy",-1,Forms![Get
Dates]![Start Date]) And DateAdd("yyyy",-1,Forms![Get Dates]![End
Date]),1,0)) AS [SJH Previous], Sum(IIf([hoscode]=xxxxx And [disdate] Between
Forms![Get Dates]![Start Date] And Forms![Get Dates]![End Date],1,0)) AS [MMC
Current], Sum(IIf([hoscode]=zzzzz And [disdate] Between Forms![Get
Dates]![Start Date] And Forms![Get Dates]![End Date],1,0)) AS [SJH Current],
[MMC Current]-[MMC Previous] AS [MMC Change], Sum(IIf([disdate] Between
DateAdd("yyyy",-1,Forms![Get Dates]![Start Date]) And
DateAdd("yyyy",-1,Forms![Get Dates]![End Date]),1,0))+Sum(IIf([disdate]
Between Forms![Get Dates]![Start Date] And Forms![Get Dates]![End Date],1,0))
AS [All]
FROM ((Master LEFT JOIN [DictMD with Address] ON Master.ATTNPHYS = [DictMD
with Address].NPI) LEFT JOIN DICTSPEC ON [DictMD with Address].[Specialty
Code] = DICTSPEC.SPECODE) LEFT JOIN DictMDGroup ON [DictMD with
Address].[Group ID] = DictMDGroup.[Group ID]
WHERE (((Master.HOSCODE) Between xxxxx And zzzzzz) AND ((Master.DISDATE)
Between DateAdd("yyyy",-1,[Forms]![Get Dates]![Start Date]) And [Forms]![Get
Dates]![End Date]) AND ((Master.DRGCODE) Not Between 385 And 391))
GROUP BY Master.ATTNPHYS
HAVING (((Sum(IIf([disdate] Between DateAdd("yyyy",-1,[Forms]![Get
Dates]![Start Date]) And DateAdd("yyyy",-1,[Forms]![Get Dates]![End
Date]),1,0))+Sum(IIf([disdate] Between [Forms]![Get Dates]![Start Date] And
[Forms]![Get Dates]![End Date],1,0)))>0));

Thanks!

 

[hkraft]

Trying to clarify:
I think what I need to do is add an IIF statement somewhere so Access knows
to lookup the [MDName] based on [license #] if [ATTNPHYS] is <=99999999 and
based on [NPI] if [ATTNPHYS] is >100000000.