My query is displaying a blank where there should be data

[jessiemacmillan]

I haven't been able to figure out why 3 of 104 rows in a query don't
display the data in one field. I've finally created a query that
displays all the data, but when I use it in a report or a sum query,
the field is blank again. The field I'm having trouble with is PDP.

Here's the query that works:

SELECT dbo_ENTITIES.ENTITYNAME AS Advocate, dbo_ENTITIES_1.ENTITYNAME
AS Client, dbo_MATTER.MATTERNUMBER, dbo_MATTER.SHORTMATTERNAME,
dbo_STATUS.DESCRIPTION AS Status, dbo_AUXMEDD.DRUGCLASS,
dbo_AUXMEDD.LEVEL1OUTCOME, dbo_AUXMEDD.LEVEL2OUTCOME,
dbo_AUXMEDD.AUTHORIZATIONREQUESTE, dbo_AUXMEDD.AUTHORIZATIONRECEIVED,
dbo_AUXMEDD.MDSUPPORTINGREQUESTED, dbo_AUXMEDD.MDSUPPORTRECEIVED,
dbo_AUXMEDD.COVDETFILED, dbo_AUXMEDD.COVDETDECIDED,
dbo_AUXMEDD.LEVEL1FILED, dbo_AUXMEDD.LEVEL1DUE,
dbo_AUXMEDD.LEVEL1DECREC, dbo_AUXMEDD.LEVEL1DEADLINE,
dbo_AUXMEDD.LEVEL2FILED, dbo_AUXMEDD.LEVEL2DUE,
dbo_AUXMEDD.LEVEL2DECREC, dbo_AUXMEDD.LEVEL2DEADLINE,
dbo_AUXMEDD.CLOSINGLTR, dbo_AUXMEDD.DRUGNAME,
dbo_AUXMEDD.COVDETDEADLINE, dbo_MATTER.DATECLOSED,
dbo_MATTER.DATEOPENED, dbo_STATUS.SHOWMODE, dbo_AUXMEDD.LEVEL0OUTCOME,
dbo_LW_A_MATTERTYPE.DESCRIPTION AS Type, dbo_AUXMEDD.LEVEL2DRCONTACT,
dbo_AUXMEDD.LEVEL2RECORDSRECD, dbo_AUXMEDD.COVDETDUE, IIf([aetna] Is
Not Null,[aetna],IIf([american] Is Not Null,[american],IIf([anthem] Is
Not Null,[anthem],IIf([cigna] Is Not Null,[cigna],IIf([fox] Is Not
Null,[fox],IIf([medco] Is Not Null,[medco],IIf([memberhealth] Is Not
Null,[memberhealth],IIf([pacificare] Is Not
Null,[pacificare],IIf([sterling] Is Not
Null,[sterling],"other"))))))))) AS PDP1, IIf([rxamerica] Is Not
Null,[rxamerica],IIf([silverscript] Is Not
Null,[silverscript],IIf([pennsylvania] Is Not
Null,[pennsylvania],IIf([unicare] Is Not
Null,[unicare],IIf([unitedamerican] Is Not
Null,[unitedamerican],IIf([unitedhealthcare] Is Not
Null,[unitedhealthcare],IIf([wellcare] Is Not
Null,[wellcare],IIf([firsthealthpremier] Is Not
Null,[firsthealthpremier],IIf([avantra] Is Not Null,[avantra],"No
PDP"))))))))) AS PDP2, IIf([PDP1]<>"other",[PDP1],[PDP2]) AS PDP
FROM (((dbo_AUXMEDD RIGHT JOIN (((dbo_ASSIGN LEFT JOIN dbo_ENTITIES ON
dbo_ASSIGN.ENTITYID = dbo_ENTITIES.ENTITYID) RIGHT JOIN dbo_MATTER ON
dbo_ASSIGN.MATTERID = dbo_MATTER.MATTERID) LEFT JOIN dbo_STATUS ON
dbo_MATTER.STATUSID = dbo_STATUS.STATUSID) ON dbo_AUXMEDD.REFERID =
dbo_MATTER.MATTERID) LEFT JOIN dbo_ASSIGN AS dbo_ASSIGN_1 ON
dbo_MATTER.MATTERID = dbo_ASSIGN_1.MATTERID) LEFT JOIN dbo_ENTITIES AS
dbo_ENTITIES_1 ON dbo_ASSIGN_1.ENTITYID = dbo_ENTITIES_1.ENTITYID) LEFT
JOIN dbo_LW_A_MATTERTYPE ON dbo_MATTER.MATTERTYPEID =
dbo_LW_A_MATTERTYPE.MATTERTYPEID
WHERE (((dbo_STATUS.SHOWMODE)="O") AND ((dbo_MATTER.SITEID)=300) AND
((dbo_ASSIGN.ROLEID)=199) AND ((dbo_MATTER.LAWTYPEID)=412) AND
((dbo_ASSIGN_1.ROLEID)=101) AND ((dbo_ENTITIES.ENTITYID)=300043 Or
(dbo_ENTITIES.ENTITYID)=300003 Or (dbo_ENTITIES.ENTITYID)=300002 Or
(dbo_ENTITIES.ENTITYID)=300042 Or (dbo_ENTITIES.ENTITYID)=300919))
ORDER BY dbo_ENTITIES.ENTITYNAME, dbo_ENTITIES_1.ENTITYNAME;

And here's the query that doesn't display all the data:

SELECT [Open Cases by Advocate].PDP, Count([Open Cases by
Advocate].PDP) AS CountOfPDP
FROM [Open Cases by Advocate]
GROUP BY [Open Cases by Advocate].PDP;

In the second query, the "blank" PDP fields are counted "correctly,"
but there are no blanks in the original query.

Any ideas?

Thanks,
Jessie