Select
 pat.[Inactive],
 pat.[Chart Number],
 pat.[Last Name],
 pat.[First Name],
 pat.[Middle Name],
 pat.[Street 1], 
 pat.[Street 2],
 pat.[City],
 pat.[State],
 pat.[Zip Code],
 pat.[Sex],
 pat.[Date of Birth],
 pat.[Social Security Number],
 pat.[Phone 1] HomePhone,
 pat.[Phone 2] WorkPhone,
 pat.[Phone 3] CellPhone,
 pat.[Phone 4] Fax,
 pat.[Phone 5] OtherPhone,

 cas.[Guarantor],
 gua.[Last Name] GUA_LNAME,
 gua.[First Name] GUA_FNAME,
 gua.[Middle Name] GUA_MiddleName,
 gua.[Street 2] GUA_ADDR1,
 gua.[City] GUA_CITY,
 gua.[State] GUA_STATE,
 gua.[Zip Code] GUA_ZIP,
 gua.[Sex] GUA_SEX,
 gua.[Date Of Birth] GUA_DOB,
 gua.[Social Security Number] GUA_SSN,
 
 cas.[Insured #1],
 insd1.[Last Name] INSD1_LNAME,
 insd1.[First Name] INSD1_FNAME,
 insd1.[Middle Name] INSD1_MiddleName,
 insd1.[Street 2] INSD1_ADDR1,
 insd1.[City] INSD1_CITY,
 insd1.[State] INSD1_STATE,
 insd1.[Zip Code] INSD1_ZIP,
 insd1.[Sex] INSD1_SEX,
 insd1.[Date Of Birth] INSD1_DOB,
 insd1.[Social Security Number] INSD1_SSN,

 cas.[Policy Number #1] PolicyNumber1,
 cas.[Group Number #1] GroupNumber1,
 
 ins1.[Code] INS1CODE,
 ins1.[Name] INS1NAME,
 ins1.[Street 1] INS1ADDR1,
 ins1.[Street 2] INS1ADDR2,
 ins1.[City] INS1CITY,
 ins1.[State] INS1STATE,
 ins1.[Zip Code] INS1ZIP,
 ins1.[Phone] INS1PHONE,
 
 cas.[Insured #2],
 insd1.[Last Name] INSD2_LNAME,
 insd2.[First Name] INSD2_FNAME,
 insd2.[Middle Name] INSD2_MiddleName,
 insd2.[Street 2] INSD2_ADDR1,
 insd2.[City] INSD2_CITY,
 insd2.[State] INSD2_STATE,
 insd2.[Zip Code] INSD2_ZIP,
 insd2.[Sex] INSD2_SEX,
 insd2.[Date Of Birth] INSD2_DOB,
 insd2.[Social Security Number] INSD2_SSN,

 cas.[Policy Number #2] PolicyNumber2,
 cas.[Group Number #2] GroupNumber2,
 
 ins2.[Code] INS2CODE,
 ins2.[Name] INS2NAME,
 ins2.[Street 1] INS2ADDR1,
 ins2.[Street 2] INS2ADDR2,
 ins2.[City] INS2CITY,
 ins2.[State] INS2STATE,
 ins2.[Zip Code] INS2ZIP,
 ins2.[Phone] INS2PHONE
From MWPAT pat, MWPAT gua, MWCAS cas
Left Outer Join MWINS ins1 on cas.[Insurance Carrier #1] = ins1.[Code]
Left Outer Join MWPAT insd1 on cas.[Insured #1] = insd1.[Chart Number]
Left Outer Join MWINS ins2 on cas.[Insurance Carrier #2] = ins2.[Code]
Left Outer Join MWPAT insd2 on cas.[Insured #2] = insd2.[Chart Number]
where
 cas.[Chart Number] = pat.[Chart Number]
 and gua.[Chart Number] = cas.[Guarantor]
 and cas.[Case Number] in
(Select
 Max([Case Number]) [Case Number]
From MWCAS
Group By [Chart Number])
Order By pat.[Last Name]