1

Okay, so, I'm trying to get a count of how many customers each service provider has billed for over the last year or two. Our database's data is slightly imperfect, so I'm trying to kludge together a solution.

Basically, we have a number of service providers who we use regularly and are listed in our database with Provider IDs, but we occasionally also pay one-off providers who aren't listed formally. So, I'm querying against our expense records, trying to group by "Check To" (the name of the Provider who was paid), and where possible match it up via a Provider ID # to our formal Providers table [Support Provider], to pull some additional info where possible (provider classification DE/IC/Agency/General Business). So, I have a Left Join included, so that expense records aren't excluded if there's no matching Provider ID in the Provider table.

Everything works fine when I'm simply DISPLAYING Provider/Customer/Expense info. But when I instead go to COUNT([Customer].[CustID]) and add GROUP BY in the process, to get a handle on how many customers have been served by a given provider in the last year or two, it tells me "no current record." I'm not sure why...

SELECT DISTINCT
  [Plan Revenue Expense].[Check To],
  [Plan Revenue Expense].[SP],
  [Support Provider].[DE?],
  [Support Provider].[IC?],
  [Support Provider].[Agency?] AS [AG?],
  [Support Provider].[GeneralBus?] AS [GB?],
  COUNT(Customer.CustID)
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]>=#7/1/12#)
      AND
      ([Plan Revenue Expense].[First Day]<=#12/31/14#)
    )
    OR
    (
      ([Plan Revenue Expense].[Last Day]>=#7/1/12#)
      AND
      ([Plan Revenue Expense].[Last Day]<=#12/31/14#)
    )
  )
GROUP BY
  [Plan Revenue Expense].[Check To],
  [Plan Revenue Expense].[SP],
  [Support Provider].[DE?],
  [Support Provider].[IC?],
  [Support Provider].[Agency?],
  [Support Provider].[GeneralBus?]
ORDER BY
  [Support Provider].[DE?],
  [Support Provider].[IC?],
  [Support Provider].[Agency?],
  [Support Provider].[GeneralBus?],
  [Plan Revenue Expense].[Check To];

I'm not clear on why the above is returning "No Current Record" when the below returns 2602 records (I'd expect the above to return a few hundred records with "COUNT()s"):

SELECT DISTINCT
  [Plan Revenue Expense].[Check To],
  [Plan Revenue Expense].[SP],
  [Support Provider].[DE?],
  [Support Provider].[IC?],
  [Support Provider].[Agency?] AS [AG?],
  [Support Provider].[GeneralBus?] AS [GB?],
  Customer.CustID
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]>=#7/1/12#)
      AND
      ([Plan Revenue Expense].[First Day]<=#12/31/14#)
    )
    OR
    (
      ([Plan Revenue Expense].[Last Day]>=#7/1/12#)
      AND
      ([Plan Revenue Expense].[Last Day]<=#12/31/14#)
    )
  )
ORDER BY
  [Support Provider].[DE?],
  [Support Provider].[IC?],
  [Support Provider].[Agency?],
  [Support Provider].[GeneralBus?],
  [Plan Revenue Expense].[Check To];

It seems like somewhere in the process of switching Customer.CustID to COUNT(Customer.CustID) and adding the GROUP BY clause, it suffers a massive brain fart and returns "No current record."

I'm getting quite frustrated with this query.

What I really want it to return is a list of Providers & how many distinct customers they've been paid for rendering services to during the specified time period (regardless of how many individual expenses may have been charged).

So, if Provider X billed a hundred times, but all the expenses were for only 3 actual individual clients, I just want it to return something like:

Provider X, Provider Type(s), 3 [Customers Served During Period]

Any thoughts on why I'm getting a "No current record" when counting and/or how to fix it?

...

and I've also tried both SELECT and SELECT DISTINCT as well as joining a subquery into the FROM statement (although it didn't return "no current record" it also didn't COUNT() correctly either, counting ALL Expenses, rather than just distinct CustID's).

1

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.' ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.