Help with UNION query in Access SQL

[Amy Badgett]

The purpose of this query is to return seven different Counts for these 14 different EIDs (employee IDs). The problem is that only 8 of the EIDs are returned: 123, 122, 108, 164, 131, 136, 138, 150. The other six EIDs don't return anything, even if I query them separately. I also realize that the other six EIDs (196, 189, 199, 203, 202, 214) are the highest out of the other EIDs.

I know this is a lot of code, but if anyone could help me with this, I would really appreciate it. Thanks!

SELECT DISTINCT Clients.EID AS EID, 

(SELECT Count([Clients].[Client ID])
FROM [Call Log], Clients
WHERE ((([Call Log].[Client ID])=[Clients].[Client ID]) AND (([Call Log].EID)='E123') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS callsPlaced, 

(SELECT Count([Call Log].[Call Code])
FROM [Call Log], Clients
WHERE (((Clients.[Client ID])=[Call Log].[Client ID]) AND (([Call Log].EID)='E123') AND (([Call Log].[Contact Made])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS contactsMade, 

(SELECT Count([Clients].[Jewish])
FROM Clients
WHERE (((Clients.EID)='E123') AND ((Clients.Jewish)=True) AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]))) AS jewishCount, 

(SELECT Count([Clients].[ReasonForNo]) AS Expr1
FROM Clients
WHERE (((Clients.EID)='E123') AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]) AND ((Clients.Jewish)=False) AND ((Clients.ReasonForNo)='H'))) AS hungUp, 

(SELECT Count([Call Log].[DVD/Tract Requested])
FROM [Call Log]
WHERE ((([Call Log].EID)='E123') AND (([Call Log].[DVD/Tract Requested])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS DVDs, 

(SELECT Count([Call Log].[Gospel Presented])
FROM [Call Log]
WHERE ((([Call Log].EID)='E123') AND (([Call Log].[Gospel Presented])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS gospelPresented, 

(SELECT Count([Call Log].[Salvation])
FROM [Call Log]
WHERE ((([Call Log].EID)='E123') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]) AND (([Call Log].Salvation)=True))) AS salvations

FROM [Call Log], Clients
WHERE [Call Log].[Client ID] = Clients.[Client ID]
AND Clients.EID = 'E123'

UNION ALL

SELECT DISTINCT Clients.EID AS EID, 

(SELECT Count([Clients].[Client ID])
FROM [Call Log], Clients
WHERE ((([Call Log].[Client ID])=[Clients].[Client ID]) AND (([Call Log].EID)='E196') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS callsPlaced, 

(SELECT Count([Call Log].[Call Code])
FROM [Call Log], Clients
WHERE (((Clients.[Client ID])=[Call Log].[Client ID]) AND (([Call Log].EID)='E196') AND (([Call Log].[Contact Made])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS contactsMade, 

(SELECT Count([Clients].[Jewish])
FROM Clients
WHERE (((Clients.EID)='E196') AND ((Clients.Jewish)=True) AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]))) AS jewishCount, 

(SELECT Count([Clients].[ReasonForNo]) AS Expr1
FROM Clients
WHERE (((Clients.EID)='E196') AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]) AND ((Clients.Jewish)=False) AND ((Clients.ReasonForNo)='H'))) AS hungUp, 

(SELECT Count([Call Log].[DVD/Tract Requested])
FROM [Call Log]
WHERE ((([Call Log].EID)='E196') AND (([Call Log].[DVD/Tract Requested])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS DVDs, 

(SELECT Count([Call Log].[Gospel Presented])
FROM [Call Log]
WHERE ((([Call Log].EID)='E196') AND (([Call Log].[Gospel Presented])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS gospelPresented, 

(SELECT Count([Call Log].[Salvation])
FROM [Call Log]
WHERE ((([Call Log].EID)='E196') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]) AND (([Call Log].Salvation)=True))) AS salvations

FROM [Call Log], Clients
WHERE [Call Log].[Client ID] = Clients.[Client ID]
AND Clients.EID = 'E196'

UNION ALL

SELECT DISTINCT Clients.EID AS EID, 

(SELECT Count([Clients].[Client ID])
FROM [Call Log], Clients
WHERE ((([Call Log].[Client ID])=[Clients].[Client ID]) AND (([Call Log].EID)='E122') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS callsPlaced, 

(SELECT Count([Call Log].[Call Code])
FROM [Call Log], Clients
WHERE (((Clients.[Client ID])=[Call Log].[Client ID]) AND (([Call Log].EID)='E122') AND (([Call Log].[Contact Made])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS contactsMade, 

(SELECT Count([Clients].[Jewish])
FROM Clients
WHERE (((Clients.EID)='E122') AND ((Clients.Jewish)=True) AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]))) AS jewishCount, 

(SELECT Count([Clients].[ReasonForNo]) AS Expr1
FROM Clients
WHERE (((Clients.EID)='E122') AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]) AND ((Clients.Jewish)=False) AND ((Clients.ReasonForNo)='H'))) AS hungUp, 

(SELECT Count([Call Log].[DVD/Tract Requested])
FROM [Call Log]
WHERE ((([Call Log].EID)='E122') AND (([Call Log].[DVD/Tract Requested])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS DVDs, 

(SELECT Count([Call Log].[Gospel Presented])
FROM [Call Log]
WHERE ((([Call Log].EID)='E122') AND (([Call Log].[Gospel Presented])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS gospelPresented, 

(SELECT Count([Call Log].[Salvation])
FROM [Call Log]
WHERE ((([Call Log].EID)='E122') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]) AND (([Call Log].Salvation)=True))) AS salvations

FROM [Call Log], Clients
WHERE [Call Log].[Client ID] = Clients.[Client ID]
AND Clients.EID = 'E122'

UNION ALL

SELECT DISTINCT Clients.EID AS EID, 

(SELECT Count([Clients].[Client ID])
FROM [Call Log], Clients
WHERE ((([Call Log].[Client ID])=[Clients].[Client ID]) AND (([Call Log].EID)='E189') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS callsPlaced, 

(SELECT Count([Call Log].[Call Code])
FROM [Call Log], Clients
WHERE (((Clients.[Client ID])=[Call Log].[Client ID]) AND (([Call Log].EID)='E189') AND (([Call Log].[Contact Made])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS contactsMade, 

(SELECT Count([Clients].[Jewish])
FROM Clients
WHERE (((Clients.EID)='E189') AND ((Clients.Jewish)=True) AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]))) AS jewishCount, 

(SELECT Count([Clients].[ReasonForNo]) AS Expr1
FROM Clients
WHERE (((Clients.EID)='E189') AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]) AND ((Clients.Jewish)=False) AND ((Clients.ReasonForNo)='H'))) AS hungUp, 

(SELECT Count([Call Log].[DVD/Tract Requested])
FROM [Call Log]
WHERE ((([Call Log].EID)='E189') AND (([Call Log].[DVD/Tract Requested])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS DVDs, 

(SELECT Count([Call Log].[Gospel Presented])
FROM [Call Log]
WHERE ((([Call Log].EID)='E189') AND (([Call Log].[Gospel Presented])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS gospelPresented, 

(SELECT Count([Call Log].[Salvation])
FROM [Call Log]
WHERE ((([Call Log].EID)='E189') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]) AND (([Call Log].Salvation)=True))) AS salvations

FROM [Call Log], Clients
WHERE [Call Log].[Client ID] = Clients.[Client ID]
AND Clients.EID = 'E189'

UNION ALL

SELECT DISTINCT Clients.EID AS EID, 

(SELECT Count([Clients].[Client ID])
FROM [Call Log], Clients
WHERE ((([Call Log].[Client ID])=[Clients].[Client ID]) AND (([Call Log].EID)='E108') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS callsPlaced, 

(SELECT Count([Call Log].[Call Code])
FROM [Call Log], Clients
WHERE (((Clients.[Client ID])=[Call Log].[Client ID]) AND (([Call Log].EID)='E108') AND (([Call Log].[Contact Made])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS contactsMade, 

(SELECT Count([Clients].[Jewish])
FROM Clients
WHERE (((Clients.EID)='E108') AND ((Clients.Jewish)=True) AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]))) AS jewishCount, 

(SELECT Count([Clients].[ReasonForNo]) AS Expr1
FROM Clients
WHERE (((Clients.EID)='E108') AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]) AND ((Clients.Jewish)=False) AND ((Clients.ReasonForNo)='H'))) AS hungUp, 

(SELECT Count([Call Log].[DVD/Tract Requested])
FROM [Call Log]
WHERE ((([Call Log].EID)='E108') AND (([Call Log].[DVD/Tract Requested])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS DVDs, 

(SELECT Count([Call Log].[Gospel Presented])
FROM [Call Log]
WHERE ((([Call Log].EID)='E108') AND (([Call Log].[Gospel Presented])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS gospelPresented, 

(SELECT Count([Call Log].[Salvation])
FROM [Call Log]
WHERE ((([Call Log].EID)='E108') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]) AND (([Call Log].Salvation)=True))) AS salvations

FROM [Call Log], Clients
WHERE [Call Log].[Client ID] = Clients.[Client ID]
AND Clients.EID = 'E108'

UNION ALL

SELECT DISTINCT Clients.EID AS EID, 

(SELECT Count([Clients].[Client ID])
FROM [Call Log], Clients
WHERE ((([Call Log].[Client ID])=[Clients].[Client ID]) AND (([Call Log].EID)='E199') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS callsPlaced, 

(SELECT Count([Call Log].[Call Code])
FROM [Call Log], Clients
WHERE (((Clients.[Client ID])=[Call Log].[Client ID]) AND (([Call Log].EID)='E199') AND (([Call Log].[Contact Made])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS contactsMade, 

(SELECT Count([Clients].[Jewish])
FROM Clients
WHERE (((Clients.EID)='E199') AND ((Clients.Jewish)=True) AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]))) AS jewishCount, 

(SELECT Count([Clients].[ReasonForNo]) AS Expr1
FROM Clients
WHERE (((Clients.EID)='E199') AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]) AND ((Clients.Jewish)=False) AND ((Clients.ReasonForNo)='H'))) AS hungUp, 

(SELECT Count([Call Log].[DVD/Tract Requested])
FROM [Call Log]
WHERE ((([Call Log].EID)='E199') AND (([Call Log].[DVD/Tract Requested])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS DVDs, 

(SELECT Count([Call Log].[Gospel Presented])
FROM [Call Log]
WHERE ((([Call Log].EID)='E199') AND (([Call Log].[Gospel Presented])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS gospelPresented, 

(SELECT Count([Call Log].[Salvation])
FROM [Call Log]
WHERE ((([Call Log].EID)='E199') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]) AND (([Call Log].Salvation)=True))) AS salvations

FROM [Call Log], Clients
WHERE [Call Log].[Client ID] = Clients.[Client ID]
AND Clients.EID = 'E199'

UNION ALL

SELECT DISTINCT Clients.EID AS EID, 

(SELECT Count([Clients].[Client ID])
FROM [Call Log], Clients
WHERE ((([Call Log].[Client ID])=[Clients].[Client ID]) AND (([Call Log].EID)='E164') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS callsPlaced, 

(SELECT Count([Call Log].[Call Code])
FROM [Call Log], Clients
WHERE (((Clients.[Client ID])=[Call Log].[Client ID]) AND (([Call Log].EID)='E164') AND (([Call Log].[Contact Made])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS contactsMade, 

(SELECT Count([Clients].[Jewish])
FROM Clients
WHERE (((Clients.EID)='E164') AND ((Clients.Jewish)=True) AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]))) AS jewishCount, 

(SELECT Count([Clients].[ReasonForNo]) AS Expr1
FROM Clients
WHERE (((Clients.EID)='E164') AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]) AND ((Clients.Jewish)=False) AND ((Clients.ReasonForNo)='H'))) AS hungUp, 

(SELECT Count([Call Log].[DVD/Tract Requested])
FROM [Call Log]
WHERE ((([Call Log].EID)='E164') AND (([Call Log].[DVD/Tract Requested])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS DVDs, 

(SELECT Count([Call Log].[Gospel Presented])
FROM [Call Log]
WHERE ((([Call Log].EID)='E164') AND (([Call Log].[Gospel Presented])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS gospelPresented, 

(SELECT Count([Call Log].[Salvation])
FROM [Call Log]
WHERE ((([Call Log].EID)='E164') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]) AND (([Call Log].Salvation)=True))) AS salvations

FROM [Call Log], Clients
WHERE [Call Log].[Client ID] = Clients.[Client ID]
AND Clients.EID = 'E164'

UNION ALL 

SELECT DISTINCT Clients.EID AS EID, 

(SELECT Count([Clients].[Client ID])
FROM [Call Log], Clients
WHERE ((([Call Log].[Client ID])=[Clients].[Client ID]) AND (([Call Log].EID)='E131') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS callsPlaced, 

(SELECT Count([Call Log].[Call Code])
FROM [Call Log], Clients
WHERE (((Clients.[Client ID])=[Call Log].[Client ID]) AND (([Call Log].EID)='E131') AND (([Call Log].[Contact Made])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS contactsMade, 

(SELECT Count([Clients].[Jewish])
FROM Clients
WHERE (((Clients.EID)='E131') AND ((Clients.Jewish)=True) AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]))) AS jewishCount, 

(SELECT Count([Clients].[ReasonForNo]) AS Expr1
FROM Clients
WHERE (((Clients.EID)='E131') AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]) AND ((Clients.Jewish)=False) AND ((Clients.ReasonForNo)='H'))) AS hungUp, 

(SELECT Count([Call Log].[DVD/Tract Requested])
FROM [Call Log]
WHERE ((([Call Log].EID)='E131') AND (([Call Log].[DVD/Tract Requested])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS DVDs, 

(SELECT Count([Call Log].[Gospel Presented])
FROM [Call Log]
WHERE ((([Call Log].EID)='E131') AND (([Call Log].[Gospel Presented])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS gospelPresented, 

(SELECT Count([Call Log].[Salvation])
FROM [Call Log]
WHERE ((([Call Log].EID)='E131') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]) AND (([Call Log].Salvation)=True))) AS salvations

FROM [Call Log], Clients
WHERE [Call Log].[Client ID] = Clients.[Client ID]
AND Clients.EID = 'E131'

UNION ALL

SELECT DISTINCT Clients.EID AS EID, 

(SELECT Count([Clients].[Client ID])
FROM [Call Log], Clients
WHERE ((([Call Log].[Client ID])=[Clients].[Client ID]) AND (([Call Log].EID)='E203') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS callsPlaced, 

(SELECT Count([Call Log].[Call Code])
FROM [Call Log], Clients
WHERE (((Clients.[Client ID])=[Call Log].[Client ID]) AND (([Call Log].EID)='E203') AND (([Call Log].[Contact Made])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS contactsMade, 

(SELECT Count([Clients].[Jewish])
FROM Clients
WHERE (((Clients.EID)='E203') AND ((Clients.Jewish)=True) AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]))) AS jewishCount, 

(SELECT Count([Clients].[ReasonForNo]) AS Expr1
FROM Clients
WHERE (((Clients.EID)='E203') AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]) AND ((Clients.Jewish)=False) AND ((Clients.ReasonForNo)='H'))) AS hungUp, 

(SELECT Count([Call Log].[DVD/Tract Requested])
FROM [Call Log]
WHERE ((([Call Log].EID)='E203') AND (([Call Log].[DVD/Tract Requested])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS DVDs, 

(SELECT Count([Call Log].[Gospel Presented])
FROM [Call Log]
WHERE ((([Call Log].EID)='E203') AND (([Call Log].[Gospel Presented])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS gospelPresented, 

(SELECT Count([Call Log].[Salvation])
FROM [Call Log]
WHERE ((([Call Log].EID)='E203') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]) AND (([Call Log].Salvation)=True))) AS salvations

FROM [Call Log], Clients
WHERE [Call Log].[Client ID] = Clients.[Client ID]
AND Clients.EID = 'E203'

UNION ALL

SELECT DISTINCT Clients.EID AS EID, 

(SELECT Count([Clients].[Client ID])
FROM [Call Log], Clients
WHERE ((([Call Log].[Client ID])=[Clients].[Client ID]) AND (([Call Log].EID)='E136') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS callsPlaced, 

(SELECT Count([Call Log].[Call Code])
FROM [Call Log], Clients
WHERE (((Clients.[Client ID])=[Call Log].[Client ID]) AND (([Call Log].EID)='E136') AND (([Call Log].[Contact Made])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS contactsMade, 

(SELECT Count([Clients].[Jewish])
FROM Clients
WHERE (((Clients.EID)='E136') AND ((Clients.Jewish)=True) AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]))) AS jewishCount, 

(SELECT Count([Clients].[ReasonForNo]) AS Expr1
FROM Clients
WHERE (((Clients.EID)='E136') AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]) AND ((Clients.Jewish)=False) AND ((Clients.ReasonForNo)='H'))) AS hungUp, 

(SELECT Count([Call Log].[DVD/Tract Requested])
FROM [Call Log]
WHERE ((([Call Log].EID)='E136') AND (([Call Log].[DVD/Tract Requested])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS DVDs, 

(SELECT Count([Call Log].[Gospel Presented])
FROM [Call Log]
WHERE ((([Call Log].EID)='E136') AND (([Call Log].[Gospel Presented])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS gospelPresented, 

(SELECT Count([Call Log].[Salvation])
FROM [Call Log]
WHERE ((([Call Log].EID)='E136') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]) AND (([Call Log].Salvation)=True))) AS salvations

FROM [Call Log], Clients
WHERE [Call Log].[Client ID] = Clients.[Client ID]
AND Clients.EID = 'E136'

UNION ALL

SELECT DISTINCT Clients.EID AS EID, 

(SELECT Count([Clients].[Client ID])
FROM [Call Log], Clients
WHERE ((([Call Log].[Client ID])=[Clients].[Client ID]) AND (([Call Log].EID)='E138') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS callsPlaced, 

(SELECT Count([Call Log].[Call Code])
FROM [Call Log], Clients
WHERE (((Clients.[Client ID])=[Call Log].[Client ID]) AND (([Call Log].EID)='E138') AND (([Call Log].[Contact Made])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS contactsMade, 

(SELECT Count([Clients].[Jewish])
FROM Clients
WHERE (((Clients.EID)='E138') AND ((Clients.Jewish)=True) AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]))) AS jewishCount, 

(SELECT Count([Clients].[ReasonForNo]) AS Expr1
FROM Clients
WHERE (((Clients.EID)='E138') AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]) AND ((Clients.Jewish)=False) AND ((Clients.ReasonForNo)='H'))) AS hungUp, 

(SELECT Count([Call Log].[DVD/Tract Requested])
FROM [Call Log]
WHERE ((([Call Log].EID)='E138') AND (([Call Log].[DVD/Tract Requested])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS DVDs, 

(SELECT Count([Call Log].[Gospel Presented])
FROM [Call Log]
WHERE ((([Call Log].EID)='E138') AND (([Call Log].[Gospel Presented])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS gospelPresented, 

(SELECT Count([Call Log].[Salvation])
FROM [Call Log]
WHERE ((([Call Log].EID)='E138') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]) AND (([Call Log].Salvation)=True))) AS salvations

FROM [Call Log], Clients
WHERE [Call Log].[Client ID] = Clients.[Client ID]
AND Clients.EID = 'E138'

UNION ALL

SELECT DISTINCT Clients.EID AS EID, 

(SELECT Count([Clients].[Client ID])
FROM [Call Log], Clients
WHERE ((([Call Log].[Client ID])=[Clients].[Client ID]) AND (([Call Log].EID)='E202') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS callsPlaced, 

(SELECT Count([Call Log].[Call Code])
FROM [Call Log], Clients
WHERE (((Clients.[Client ID])=[Call Log].[Client ID]) AND (([Call Log].EID)='E202') AND (([Call Log].[Contact Made])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS contactsMade, 

(SELECT Count([Clients].[Jewish])
FROM Clients
WHERE (((Clients.EID)='E202') AND ((Clients.Jewish)=True) AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]))) AS jewishCount, 

(SELECT Count([Clients].[ReasonForNo]) AS Expr1
FROM Clients
WHERE (((Clients.EID)='E202') AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]) AND ((Clients.Jewish)=False) AND ((Clients.ReasonForNo)='H'))) AS hungUp, 

(SELECT Count([Call Log].[DVD/Tract Requested])
FROM [Call Log]
WHERE ((([Call Log].EID)='E202') AND (([Call Log].[DVD/Tract Requested])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS DVDs, 

(SELECT Count([Call Log].[Gospel Presented])
FROM [Call Log]
WHERE ((([Call Log].EID)='E202') AND (([Call Log].[Gospel Presented])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS gospelPresented, 

(SELECT Count([Call Log].[Salvation])
FROM [Call Log]
WHERE ((([Call Log].EID)='E202') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]) AND (([Call Log].Salvation)=True))) AS salvations

FROM [Call Log], Clients
WHERE [Call Log].[Client ID] = Clients.[Client ID]
AND Clients.EID = 'E202'

UNION ALL

SELECT DISTINCT Clients.EID AS EID, 

(SELECT Count([Clients].[Client ID])
FROM [Call Log], Clients
WHERE ((([Call Log].[Client ID])=[Clients].[Client ID]) AND (([Call Log].EID)='E150') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS callsPlaced, 

(SELECT Count([Call Log].[Call Code])
FROM [Call Log], Clients
WHERE (((Clients.[Client ID])=[Call Log].[Client ID]) AND (([Call Log].EID)='E150') AND (([Call Log].[Contact Made])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS contactsMade, 

(SELECT Count([Clients].[Jewish])
FROM Clients
WHERE (((Clients.EID)='E150') AND ((Clients.Jewish)=True) AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]))) AS jewishCount, 

(SELECT Count([Clients].[ReasonForNo]) AS Expr1
FROM Clients
WHERE (((Clients.EID)='E150') AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]) AND ((Clients.Jewish)=False) AND ((Clients.ReasonForNo)='H'))) AS hungUp, 

(SELECT Count([Call Log].[DVD/Tract Requested])
FROM [Call Log]
WHERE ((([Call Log].EID)='E150') AND (([Call Log].[DVD/Tract Requested])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS DVDs, 

(SELECT Count([Call Log].[Gospel Presented])
FROM [Call Log]
WHERE ((([Call Log].EID)='E150') AND (([Call Log].[Gospel Presented])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS gospelPresented, 

(SELECT Count([Call Log].[Salvation])
FROM [Call Log]
WHERE ((([Call Log].EID)='E150') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]) AND (([Call Log].Salvation)=True))) AS salvations

FROM [Call Log], Clients
WHERE [Call Log].[Client ID] = Clients.[Client ID]
AND Clients.EID = 'E150'

UNION ALL

SELECT DISTINCT Clients.EID AS EID, 

(SELECT Count([Clients].[Client ID])
FROM [Call Log], Clients
WHERE ((([Call Log].[Client ID])=[Clients].[Client ID]) AND (([Call Log].EID)='E214') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS callsPlaced, 

(SELECT Count([Call Log].[Call Code])
FROM [Call Log], Clients
WHERE (((Clients.[Client ID])=[Call Log].[Client ID]) AND (([Call Log].EID)='E214') AND (([Call Log].[Contact Made])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS contactsMade, 

(SELECT Count([Clients].[Jewish])
FROM Clients
WHERE (((Clients.EID)='E214') AND ((Clients.Jewish)=True) AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]))) AS jewishCount, 

(SELECT Count([Clients].[ReasonForNo]) AS Expr1
FROM Clients
WHERE (((Clients.EID)='E214') AND ((Clients.[Date Entered]) Between [Start Date] And [End Date]) AND ((Clients.Jewish)=False) AND ((Clients.ReasonForNo)='H'))) AS hungUp, 

(SELECT Count([Call Log].[DVD/Tract Requested])
FROM [Call Log]
WHERE ((([Call Log].EID)='E214') AND (([Call Log].[DVD/Tract Requested])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS DVDs, 

(SELECT Count([Call Log].[Gospel Presented])
FROM [Call Log]
WHERE ((([Call Log].EID)='E214') AND (([Call Log].[Gospel Presented])=True) AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]))) AS gospelPresented, 

(SELECT Count([Call Log].[Salvation])
FROM [Call Log]
WHERE ((([Call Log].EID)='E214') AND (([Call Log].[Date of Call]) Between [Start Date] And [End Date]) AND (([Call Log].Salvation)=True))) AS salvations

FROM [Call Log], Clients
WHERE [Call Log].[Client ID] = Clients.[Client ID]
AND Clients.EID = 'E214';

[gershwyn]

I can't say for sure what is causing those IDs not to show up, but I would start by rewriting the query. It is incredibly (and needlessly) complicated.

Is there any difference between the queries for each EID? If not, you can eliminate a lot of complexity by getting rid of the unions entirely, and writing one query that is restricted to the EIDs you want. You can use a where clause in the overall query to determine the list of EIDs that are shown.

You also are using the cartesian product of the two tables where it looks as if an inner join is what you want. I would start with a much simpler version. The query below should give the 14 IDs and the total calls made within the given date range.

SELECT [Call Log].[EID], Count(1) AS CallsPlaced
FROM [Clients] INNER JOIN [Call Log]
ON [Clients].[Client ID] = [Call Log].[Client ID]
WHERE [Call Log].[EID] IN (108, 122, 123, 131, 136, 138, 150, 164, 189, 196, 199, 202, 203, 214)
AND [Call Log].[Date of Call] BETWEEN [Start Date] AND [End Date]
GROUP BY [Call Log].[EID]

I suspect the remaining fields can be calculated by using a sum(iif(...)) construction, but I would need to know more about what you are trying to accomplish with this. In particular, I am confused at the difference between the [Clients].[Entered Date] and [Call Log].[Date of Call], both of which you test against your date range.

[Amy Badgett]

Thank you, gershwyn, for helping me simplify my query. I have several counts that require where clauses just for the count itself. How would I do this? Inner select doesn't work and I can't get DCount to work, but maybe I'm doing it wrong.

[Amy Badgett]

Can anyone else help me with this problem?

[Rabbit]

It would help to see some sample data and sample results.

[Brian Connelly]

Are the IDs from different tables? You code query count distinct IDs with outer joins.

[Amy Badgett]

No, the IDs are on the same table (Call Log), and I didn't think I would need any joins unless I was wanting to reference another table.

Does anyone else have any other ideas on this?

[gershwyn]

As Rabbit pointed out previously, it would help tremendously if you supplied sample data and the results you would expect to see from your query. You mention having seven counts, but other than digging through the code you provided, we have no details of what they should be or how they are calculated. If you can give us more details about what you want, I'm sure someone will be able to help you further.

[TheSmileyCoder]

Ive looked at your code and at Gershwyn's post #2.

From what I can tell it should address most of your needs. Have you tried implementing it?

If so, which parts are "missing" for gershwyn's answer to be a complete solution?