Removing records based on Unique values and specific conditions

[smeye011]

Hello,

I’d like to find a solution to the following problem - preferably using pivot tables, but I can also use a work around with the raw data before running the pivot table if needed:

Essentially, my data lists medical encounters with criteria such as date of service, service code, client name, clinician name, and so on.

In any particular data set, there are likely to be numerous entries for the same client (with different dates, service codes, etc)

I’d like to find a way to remove data (or omit in the pivot field) based on these conditions:

If client A shows service code 90791, count the code towards total appointments for client A. If client A shows a code 90791, but also shows code 96101 in ANY other row of data, remove all records (or omit all data) for client A that will impact the pivot table.

I’m not sure if this is a filtering issue, or potentially could be used with the index/match function - any help would be greatly appreciated!

 

[Fazza]

You can incorporate the requirements in the pivot table using

SELECT *
FROM YourTable
WHERE ClientName NOT IN 
(SELECT ClientName
FROM 
(SELECT DISTINCT ClientName, COUNT(ClientCode)
FROM YourTable
WHERE ClientName = 'A' AND ClientCode IN (90791,96101)
GROUP BY ClientName
HAVING COUNT(*) = 2))

Brief description of manual set up follows. With names to suit your field names, & source data's defined name. Save your data file with a simple defined name (I've assumed YourTable) for the source data. ALT-D-P to start the pivot table wizard, choose external data at the first step, follow the wizard to the end choosing the option at the last step to edit in MS Query. Edit the SQL as above (to suit your data & field names) and OK to acknowledge message about not being able to graphically represent. Exit MS Query & create & set up the pivot table.

HTH

 

[Fazza]

So I hadn't tested the solution earlier: when I did it was wrong.

After testing, please change earlier incorrect pivot table definition to

SELECT *
FROM YourTable
WHERE ClientName NOT IN
(SELECT ClientName
FROM (SELECT DISTINCT ClientName, ClientCode
FROM YourTable
WHERE ClientName = 'A' AND ClientCode IN (90791,96101))
GROUP BY ClientName
HAVING COUNT(*) = 2)