I have two fields:
PatientID
ProviderID

I first find all duplicates of PatientID.
Then I would like to omit only the records where the duplicate count of
the same ProviderID is the same as the duplicate count of the PatientID.

For example:

PatientID ProviderID
1 1
1 1
2 1
2 2
2 1
3 1
3 1
3 1

I would like for the records of PatientID 1 and 3 to be omitted.

How do I go about doing this?
I would guess it involves creating variable $combinedID = PatientID&" :
"&ProviderID

I am having difficulty figuring out the scripting of this.

Thanks much!

Richard

Hi,
Are you sure, from the example you give, that what you want is right, when
you say "I would like for the records of PatientID 1 and 3 to be omitted",
because if I read you correctly, I'd expect 3 records of PatientID 1, 2 and
4 be omitted, instead of only 1 and 3. Or else omit 2, 4...
Please clarify to make me understand better.
Remi-Noel


"Richard Winters" <drrwinters (AT) yahoo (DOT) com> a écrit dans le message de news:
2007011801423316807-drrwinters (AT) yahoocom (DOT) ..

The PatientID needs to have duplicates and the StaffID needs to be the
same for each of the corresponding duplicate PatientID records.

For example,

PatientID StaffID
ron a
ron b
ron b
george c
george b
george a
mary a
mary a
mary a
joe b
theresa b
theresa b
theresa b
theresa b
rochelle c
rochelle c

The result would OMIT:

mary. There are 3 instances where PatientID="mary" and 3 instances
where StaffID="a".
The same StaffID ("a") for each of the PatientID="mary" duplicates.

theresa. There are 4 instances where PatientID="theresa" and 4
instances where StaffID="b".
The same StaffID ("b") for each of the PatientID="theresa" duplicates.

rochelle. There are 2 instances where PatientID="rochelle" and 2
instances where StaffID="c".

The result of the script would INCLUDE:

ron. There are three duplicates of PatientID="ron". However, there are
two different StaffID (1 "a" and 2 "b") associated with PatentID="ron".
george. There are three duplicates of PatientID="ron". However, there
are also three different StaffID ("a","b", and "c").
joe. There are no duplicate records where PatientID="joe".

Thanks much!

Richard



On 2007-01-18 07:12:22 -0800, "Remi-Noel Menegaux"
<rnmenegaux_AT_free.fr> said:

Your description is still somewhat unclear. Is this process being
applied to all your records or just a found set? How are your tables
set up and what exactly are you trying to accomplish? Is your end
result the list of patient names or a found set of the patients and
staff?

Two methods come to mind.

The first is to create two self-join relationships. One new table
occurence is PatientSelf and it's related to the original table based
on PatientID = PatientID. The other TO is CalcPatientStaffSelf based on
a newly defined text calc field = PatientID & " " & StaffID.

Add a new calc field (defined in the context of your original table):
Dupes = ( Count(PatientSelf::PatientID) =/=
Count(CalcPatientStaffSelf::PatientID) )

That field will return 1 for the records you want to find and 0 for
those you don't. You can do a find and get a found set of patient and
staff records.


The second one method is to write a script with a couple nesting Loop
and If statements. This will give you a list of Patient IDs. I
haven't tested this, but you get the idea...This will give you a global
field with a list of the relevant PatientIDs.

//BEGIN SCRIPT
Go To Record First
Loop
Set Variable $patID = PatientID
Set Variable $staffID = StaffID

Loop
If $patID = PatientID and StaffID =/= $staffID
Set $Patients = $Patients & "|" & PatientID
Loop
Go To Next Record (exit after Last)
Exit Loop If $pat =/= PatientID
End Loop
End If
Exit Loop If $pat =/=PatientID
Go To Record Next (Exit after last)
End Loop

End Loop

Set Field gNames = $Patients

//END SCRIPT


G


Richard Winters wrote: