Try sorting by a calculation field =
StreetA < StreetB ;
StreetA & "|" & StreetB ;
StreetB & "|" & StreetA
Bingo! Thank you.
One other issue came up: I want a count of streets by frequency. Whether a street is listed as streetA or streetB is immaterial: if it appears in either, it should be counted toward the total for that street. How can I do that?
There are quite a few ways to do that - perhaps you could give us a little background: what is your table a table of, how do you intend to use this count (a report, on-screen view, etc.) and so on. Also state your version.
It's traffic accident data, with each accident record tagged to the closest intersection. I want to be able to answer questions like, "what are the most hazardous intersections?" (that one's covered by the CASE statement), or "What are the most hazardous streets?" There's other information contained in each record that would let me manually select sub-groups, such as accidents involving hit & runs, or accidents within a date range-- that sort of record selection I can do with find requests. It's how to summarize and report the found records spanning the two street fields that stymies me. So, for any set of found data, my immediate need is for a report layout that shows (on screen and paper) the intersections with the most accidents (that part's now taken care of), and one showing the streets with the most accidents across all intersecting streets. I'm sure that once these reports circulate, more types of reports will be requested, but I'll cross that bridge later :-)
I'm using FM 10 Advanced.
Do you have a table listing all possible street names? How do you make sure the same street won't be counted as two due to a spelling error or the difference between "Some Avenue" and "Some Ave."?
The information is coming from a police database that does the street name normalizing for me.
Here are two possible ways to approach this:
1. A quick fix:
Change the above calculation field to =
Case ( StreetA < StreetB ; StreetA & ¶ & StreetB ; StreetB & ¶ & StreetA )
Define a relationship to the Streets table using the calculation field as the match field. In the Streets table, count the related records in Accidents table.
2. A proper relational approach:
Enter the two streets into two separate records in a related table, instead of into two fields in the accident record itself. Then produce your reports from this table.
I'll give it a try and report back. Thanks for your help.
Mission accomplished. I broke the streets out into a separate table. Now, after I create a found set of accident data, I use a script to go to the related records, then do a summary report.
I kept the intersection schema the same (using the CASE statement within the accident table). It does give me duplication of data (street names in the accident table for intersection reporting, and in the streets table for street reporting) but I'm not going to sweat it because (1) the data are static; (2) it works as-is, so why mess with it; and (3) I couldn't think of an easy way to do it. At a future time I'll take a crack at it.
Thanks for the help.
I kept the intersection schema the same (using the CASE statement within the accident table). It does give me duplication of data (street names in the accident table for intersection reporting, and in the streets table for street reporting)
I hope you are not actually entering the street names twice - that's really not required. You just need to change the calculation field in the Accidents table to refer to the related streets. If you set the relationship to sort the Streets records by street name, the calculation can be simply =
List ( Streets::StreetName )
It appears to me that within Accidents I have to break the related streets into separate fields/variables so I can perform a comparison and create the intersection concat. That's the stumbling block.
I don't see why, but perhaps I am missing something.
What you missed is that I've forgotten 80% of my FM knowledge cuz I haven't touched the stuff in many years; I remember many of the concepts but not the particulars of execution.
Anyway, after rummaging around the function list I used the GetNthRecord statement to pull the two street records into two calc fields in an accident record, then I was on my way. The database is now fully relational.
I used the GetNthRecord statement to pull the two street records into two calc fields in an accident record
But why do you need them there?