Jump to content
Sign in to follow this  
CWH

comparing records in same table

Recommended Posts

I have a database that I set up (naively) flat. Each record represents a quarterly report from an HIV clinic in Africa. There is an id field for each record, so you can find all the reports for each clinic. Each report is numbered in another field. I need to compare the results from one quarter to another, finding any that have been operating for more than 1 year that have had a decrease in a field that tracks the percentage of women getting treated.

I think I need some kind of script loop, and I started by self joining the id field so records for the same clinic are linked, but after that I'm not sure where to go. An If calc probably, but I'm ignorant about how they work.

Any suggestions much appreciated.

Share this post


Link to post
Share on other sites

Easy way to view both of them on the same screen would be to create two global fields, named whatever.

Create 2 extra instances of your table relate one of your new fields to the id in one of em, and the other field to the id in the 2nd new table instance.

Punch the ID's of the records you wish to compare in your two newly created fields and bam, you now have one record that is related to two records.

Create two portals, (one for each table instance) with the data you wish to compare and thats it.

Now if your wanting to do more than just look and print that might not work for you. but theres a quick and easy way to compare two records in a flat file.

I'd suggest creating a new table for these purposes, and then creating your two global fields in there. But thats more of a preference and need for organization on my part, plus thought of future reports than any real issue i see now for you.

Share this post


Link to post
Share on other sites

Thanks, Zero. I think I didn't give enough background. I'm trying to find any set(s) of records for clinics where a value in field X (actually a percentage) has decreased over time. So my Find is something like Find any record where Report number is greater than 4 (4 quarter reports = operating over 1 year)and X in previous reports is greater. Since I'm trying to find clinics that have an overall declining trend, the script need to look at (and retrieve) a series of records.

Share this post


Link to post
Share on other sites

But still the same basic thing would apply. just make your relationship based off of that number "4" (That you entered into a global field.) with a greater than join to the Report Number field. Then a portal will list everyone record that has a "Report Number" of greater than 4.

Share this post


Link to post
Share on other sites

First, if you want to find clinics, you should have a separate table of clinics - where each clinic has one unique record. This would be related to the reports table by matching the clinic ID, so each report record has a parent clinic record.

This will be only a first step towards solving your issue, but a necessary one. It will be easy to find clinics with more than 4 reports, since each parent can count its children in a calculation field.

The next step is more difficult, because "trend" is such a flimsy concept. Numbers can go up and down in turn, but a human will still determine "an overall decreasing trend". These things are difficult to model, especially in a database. One possible easy way out would be to compare the first and the last value in the series, and call that a trend.

Share this post


Link to post
Share on other sites

thanks comment. That's what I was afraid of. A lesson in the importance of designing well before you start.

Well, my amateur effort did the job for the last 7 years but now the whole thing is migrating to a giant sql system with none of the usability of FMP. I was just trying to milk some last info out of what I built, but doubt I'll have time to set this up.

Share this post


Link to post
Share on other sites

I don't think it should take more than say half an hour to fix this. You would simply import your records to a new Clinics table, while de-duping them - see here, for example:

http://www.fmforums.com/forum/showtopic.php?tid/188466

the whole thing is migrating to a giant sql system with none of the usability of FMP

Sounds like the new version 9 should be right up your alley.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.