14 Replies Latest reply on Jul 3, 2009 4:57 PM by comment_1

# need help with reporting on conjunction of two fields

### Title

need help with reporting on conjunction of two fields

### Post

I have a table containing (among other things) two fields defining the cross-streets of an intersection. Multiple records can refer to one intersection. I want a report listing how many records pertain to each intersection, sorted by declining value.

The issue is that the intersection of Your Street crossing My Street is actually the same intersection as My Street crossing Your Street. If the table contains 5 records of Your Street/My Street and 8 records of My Street/Your Street, how do I arrange things to get a report showing 13 records pertaining to that one intersection?

• ###### 1. Re: need help with reporting on conjunction of two fields
Try sorting by a calculation field =

`Case (StreetA < StreetB ;StreetA & "|" & StreetB ;StreetB & "|" & StreetA) `

• ###### 2. Re: need help with reporting on conjunction of two fields

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?

• ###### 3. Re: need help with reporting on conjunction of two fields
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.
• ###### 4. Re: need help with reporting on conjunction of two fields

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 :-)

• ###### 5. Re: need help with reporting on conjunction of two fields
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."?
• ###### 6. Re: need help with reporting on conjunction of two fields
The information is coming from a police database that does the street name normalizing for me.
• ###### 7. Re: need help with reporting on conjunction of two fields
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.

• ###### 8. Re: need help with reporting on conjunction of two fields
I'll give it a try and report back. Thanks for your help.
• ###### 9. Re: need help with reporting on conjunction of two fields

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.

• ###### 10. Re: need help with reporting on conjunction of two fields

rafi wrote:

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 ) `

Sorting the records in Accidents will be a tad slower using an unstored calculation, but I don't think it needs to bother you unless you have a huge amount of them and need to report very often.

• ###### 11. Re: need help with reporting on conjunction of two fields
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.
• ###### 12. Re: need help with reporting on conjunction of two fields
I don't see why, but perhaps I am missing something.
• ###### 13. Re: need help with reporting on conjunction of two fields

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.

• ###### 14. Re: need help with reporting on conjunction of two fields

rafi wrote:
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?