1

I'm writing a query in access 2010 and i can't use count(distinct... so I'm running into a bit of trouble with what can be found below:

An example of my table is as follows

Provider  | Member ID | Dollars | Status
FacilityA | 1001      | 50      | Pended
FacilityA | 1001      | 100     | Paid
FacilityA | 1002      | 200     | Paid
FacilityB | 1005      | 30      | Pended
FacilityB | 1009      | 90      | Pended
FacilityC | 1001      | 100     | Paid
FacilityC | 1008      | 500     | Paid

I want to return the total # of unique members that have visited each facility, but I also want to get the total dollar amount that is Pended, so for this example the ideal output would be 

Provider  | # members | Total Pended charges
FacilityA | 2         | 50 
FacilityB | 2         | 120   
FacilityC | 2         | 0

I tried using some code I found here: Count Distinct in a Group By aggregate function in Access 2007 SQL and here: SQL: Count distinct values from one column based on multiple criteria in other columns

Copying the code from the first link provided by gzaxx: 

SELECT cd.DiagCode, Count(cd.CustomerID)
FROM (select distinct DiagCode, CustomerID from CustomerTable) as cd 
Group By cd.DiagCode;

I can make this work for counting the members:

SELECT cd.Provider_Number, Count(cd.Member_ID)
FROM (select distinct Provider_Number, Member_ID from Claims_Table) as cd
ON claims_table.Provider_Number=cd.Provider_Number
Group By cd.Provider_Number;

However, no matter what I try I can't get a second portion dealing with the dollars to work without causing an error or messing up the calculation on the member count.

Community
  • 1
  • 1
RyCoG71
  • 11
  • 1

2 Answers2

0
SELECT cd.Provider_Number, 
-- claims_table.Member_ID, claims_table.Dollars
SUM(IIF ( Claims_Table.Status = 'Pended' , Claims_Table.Dollars , 0 )) as Dollars_Pending,
  Count(cd.Member_ID) as  Uniq_Members, 
  Sum(Dollars) as Dollar_Wrong
FROM (select distinct Provider_Number, Member_ID from Claims_Table) as cd inner join #claims_table 
ON claims_table.Provider_Number=cd.Provider_Number and claims_table.Member_ID = cd.Member_ID
Group By cd.Provider_Number;
objectNotFound
  • 1,683
  • 2
  • 18
  • 25
0

This should work fine based only on the table you described (named Tabelle1):

SELECT Provider, count(MemberID) as [# Members],
       NZ(SUM(SWITCH([Status]='Pended', Dollars)),0) as [Total pending charges]
FROM Tabelle1
GROUP BY Provider;

Explanation I think the first and second column are self-explanatory.

The third column is where most things are done. The SWITCH([Status]='Pended', Dollars) returns the Dollars only if the status is pending. This then gets summed up by SUM. The NZ(..,0) will set the column to 0 if the SUM returns a NULL.

EDIT: This was tested on Access 2016

Philipp Löwen
  • 1
  • 1