why finding mess up the Distinct function ?

[jiehuang001]

Please refer to the sample file at http://www.duke.edu/~jiehuang/Distinct_test.fp7

In a patient_visit table, I had two counts, one to count the total number of visits, the other to count the total number of patients.

For the two reports, you will find that one counts correctly while the other doesn't. I have been using this counting for many of my real databases. And the same problem, some counts correctly while others don't.

Please Help!!

Jie

[-Queue-]

Actually, both are counting correctly. The problem is that, for example, the first three records of the Pending report have a zero in the 'unique' field, because they are not the first of those three patient records in the file. So the total of unique Patient IDs for the first subsummary is zero.

The unique field will not really help you here, but I have modified your file so that the reports show the correct information.

PatientTransferReportv2.zip

[jiehuang001]

Hi,

Thanks very much for your reply and great to see the modified version works! Just don't know the problem that you pointed out "the first three records of the Pending report have a zero in the 'unique' field". The first three records are 10011004, 10011010, 10011021. You mean that the field can't contain a zero :

Why???

Jie

[jiehuang001]

Also, I found that you created a new field

c.n.Transferred = PatternCount( Transferred; "yes" )

and used this field in the relationship. How about if the report will be based on multiple searching criteria? Do I need to do so for each searching criteria???

Thanks.

Jie

[-Queue-]

The 'unique' field referred to is your YesPatientIDUnique calculation field. The first three records of the pending report have a zero in this field (meaning they are not the first-entered records for the given patients) because the relationship does not account for sort order. So the originals for all three patients were created before these three records were. They may be unique from the perspective of the report, but they are duplicates from the perspective of the relationship.

Do I need to do so for each searching criteria?

I think it would depend, but I will say yes just to be safe. The Patient_Visit 2 relationship is used to group all records on the current report that have the same SiteID and similar Transfer status, as the subsummary does. If different find criteria is used, it will probably change what records are grouped together and the relationship will need to be modified.