summarizing distinct record sets at once

[afh2006]

(not sure that this is in the right forum. if not, someone please let me know where to place this question.)

i'm in the middle of the follow-up phase of a medical study, and have an amateur's problem:

each patient has values attached to their record telling me if they obtained complete relief from the treatment, their ethnicity, state of residence, and so on. of course there are many other facts, but i'll keep the case simple. i'd like to create a single layout which will show me the numbers for total patients meeting certain criteria. as more patients respond and i put their info into the database, this summary page automatically recalculates and displays the new statistics. so, from the whole population of patients (no records omitted), i want to see how many hispanics from wisconsin got complete relief, what percentage of texans got no relief, and so on, all in the same place. these are just examples, and i know it is easy to reckon these numbers from a simple find, but i have about a hundred (and growing) distinct curiosities and i can't recalculate my totals each time we add a patient's outcome to our data pool. please let me know if anyone has a solution to this seemingly simple problem. thanks,

andrew

[Genx]

... ok, lets say your doing it by one criteria... i.e. whether or not the patient recovered and their ethnicity...

we use one table lets call it patient and utilize a self join between the patient table... particular to our criteria which is ethnicity... then one way of identifying whether a patient has recovered is using a checkbox with a value list of ... basically just 1... then in the patient table we make two new calc fields... one field is just a count that refers to the related table... i.e. count(patient4calc::somefield) (note that that field that is being counted must never be empty or it wont be picked up... so use an id or something).. anyway, then we make one more field to get a sum of all the recovered patients... utilizing the "1" value list to do this...

so just sum(patient4calc::recovered?)...

anyway, im not that great at explaining things so i just make sample files... have a look at the attached one...

genx

sample.zip

[Genx]

please dont triple post?

genx

[Lee Smith]

I deleted the other two posts

[afh2006]

sorry about the multiple posts. wasn't sure where it belonged.

thanks for the reply and sample file. so if i use self-joins to do this, i have to create a new relationship table for each criteria that i use in analysis? there are dozens of these. it does seem to work, but it's going to make the database very messy. is there possibly another way to go about it without all the clutter?

andrew

[comment]

You could try defining a single relationship to combine all criteria, as shown in the attached.

However, a scripted find may still be your best choice. Consider, for example, a criterion like Age. In defining a relationship, you must hardwire either an equality, or a range or a comparison. A find gives you a flexibility of specifying "above 40" one day, and "35 to 45" the next.

You could create a table of search criteria combinations, and script a find - so you'd only need to click on a named find, and view the results immediately.

FilteredStats.fp7.zip

[afh2006]

these are good solutions and if necessary i can live with it. but the central problem still remains.

the fact is that i have over a hundred analytical points that i need to know about the patient population, and the addition of one data set will change each of these numbers. i'm trying to find a solution where i don't have to run through a find function, even a scripted one, for each point each time a new patient enrolls. if there were a way to make a script that would automatically execute all the different scripted finds, and then insert the results (a count of the positives) of each find into a field, that would be ideal. then the recipient fields could all be placed in one layout. (as the finding scripts execute sequentially, the result of the former is not changed by the result of the latter becuase the data is dumped to a recipient field.) this is my theory. is it possible?

thanks again for the help.

andrew

[comment]

It is possible. But it may get quite complex, especially if you have AND search criteria. I'd suggest you watch this video and DL the accompanying files.

[afh2006]

thanks for the link, but that still isn't what i'm getting at. let me rephrase my idea.

1. Pasting Script: pastes count of Finding Script results into

recipient field. Variable for the recipient field designated. The

count function must be within (as opposed to occuring in the recipient field itself, otherwise the count would change with each new find).

2a. Finding Script: runs a particular find, then runs the Pasting

Script. If necessary, a "reset" or "show all" command must occur after the pasting script (alternately, at the beginning of each Finding Script).

2b. Calculation Script: A script that runs a summary calculation, such as standard deviation. Replaces the find command.

3. Execution Script: runs all finding scripts in sequence. A

corresponding button will initiate.

f(3(2(1)))=the simultaneous display of each individual count and

calculation on one page.

Recipient fields are each associated with the finding script that will feed it. If one finding script is Side=left, Lacrimation=neg,

Ptosis=pos then there will be a recipient field named as such.

is this is the correct scripting tactic to use? do i need to

create a seperate table for the fields that will be used to define the finding criteria?

this seems like it should be easy but i'm just a beginner. it's just three simple scripts nested together. by the way, i'm the only one using this database and so there is no concern about privileges, fanciness, or any extraneous functions. i should also add that i do not care about the actual found results, only the count. in other words, i don't need to know the identities of the patients who are positive for ptosis, negative for lacrimation, and have left-sided symptoms, only how many.

Edited February 15, 2006 by Guest

[comment]

I am sorry - you have lost me completely there. Never mind the tactic - what do you want to accomplish by this?

I thought you would want a table of Criteria (where each record is a combination of search criteria, and the associated count). Then you'd need a mechanism (script) to update the count in each Criteria record, as the population in the Patients table gets updated?