Okay, well, since nobody else chimed in and I needed to get it done, I took one last shot at it. Not quite sure on the why of it all, but I used basically the entire query as a subquery in the FROM clause, and then ran the report against that, and that seemed to do it...
SELECT
[VendorCusts].[VendorPaid],
[VendorCusts].[DE?],
[VendorCusts].[IC?],
[VendorCusts].[AG?],
[VendorCusts].[GB?],
COUNT(VendorCustID) AS [CustCount]
FROM
(
SELECT DISTINCT
[Plan Revenue Expense].[Check To] AS [VendorPaid],
[Support Provider].[DE?],
[Support Provider].[IC?],
[Support Provider].[Agency?] AS [AG?],
[Support Provider].[GeneralBus?] AS [GB?],
Customer.CustID AS [VendorCustID],
Customer.LName AS [VendorCustLName],
Customer.FName AS [VendorCustFName]
FROM
(((Customer
INNER JOIN Plan ON Customer.CustID=Plan.CustID)
INNER JOIN [Plan Revenue] ON [Plan].[Plan ID]=[Plan Revenue].[PlanID])
INNER JOIN [Plan Revenue Expense] ON [Plan Revenue].[Rev ID]=[Plan Revenue Expense].[RevID])
LEFT JOIN [Support Provider] ON [Plan Revenue Expense].[SP]=[Support Provider].[ID]
WHERE
(
(
([Plan Revenue Expense].[First Day]>=[Expense Start "MM/DD/YYYY"])
AND
([Plan Revenue Expense].[First Day]<=[Expense End "MM/DD/YYYY"])
)
OR
(
([Plan Revenue Expense].[Last Day]>=[Expense Start "MM/DD/YYYY"])
AND
([Plan Revenue Expense].[Last Day]<=[Expense End "MM/DD/YYYY"])
)
)
AND NOT
(
[Plan Revenue].[Service]='111' OR
[Plan Revenue].[Service]='222' OR
[Plan Revenue].[Service] LIKE '333*'
)
AND NOT Customer.[Inactive?]=TRUE
ORDER BY
[Plan Revenue Expense].[Check To],
Customer.LName,
Customer.FName
) AS [VendorCusts]
GROUP BY
[VendorCusts].[VendorPaid],
[VendorCusts].[DE?],
[VendorCusts].[IC?],
[VendorCusts].[AG?],
[VendorCusts].[GB?]
HAVING
NOT ([VendorCusts].[DE?]=TRUE OR [VendorCusts].[IC?]=TRUE)
ORDER BY
[VendorCusts].[DE?] ASC,
[VendorCusts].[IC?] ASC,
[VendorCusts].[AG?] ASC,
[VendorCusts].[GB?] ASC,
[VendorCusts].[VendorPaid] ASC;
This is more or less what I finally came up with and it appears to get the job done. Checked a few data points and they seemed to agree with the slightly more verbose version w/o a Count() function. So, I think the results are good.
Then I abstracted out the start and end of the date range so I could run a few different versions. And added a few other parameters I needed...
Seems like it's working as intended, if a bit kludge-y. Sometimes it's better to be 'right' than 'pretty.' ;)
If I have well understood your question, you should use the DECODE
function in your query in the SELECT
and GROUP BY
sections.
NB: for performance purpose, you might create an index using that function.
Best Answer
How frequent do you need to perform this query?
If it is very rarely, then, perhaps it doesn't matter. And you could do "SELECT COUNT(1) FROM ...", do no need to use "SELECT COUNT(id)". SELECT COUNT(id) is not necessary in your case. Please refer here for more about when to use SELECT COUNT(1) etc.
As pointed out by Justin Cave above, does it need to be accurate all the time?
If you need it to be accurate, you might have to suffer the cost of the query.
If not, perhaps you could consider caching the value and only perform the query once every time period (once per 5 seconds?)