possible self join issue

[databaser]

I have a contacts database with 400,000 records, and all the contacts live in the same general area.

the address is composed from many fields and house # and street name are separate fields.

It would be helpful to me if there were a field that showed how many of the other contacts lived on the same street, such that, if 2,806 people lived on wilshire blvd, the field would equal 2806 in all records where the contact lived on wilshire blvd.

I'm not really sure how to do this. i'm guessing it involves a self join.

[Digital Life]

I would make a drop down value list on the street field. Call it say 'Streets' and click on 'Use values from field'. In the window that opens choose the table that the addresses are in and then in the list of fields choose the 'Street' field. This value list might only be temporary so you can run the report if it gets in the way of entering data.

Now create a script that has a loop in it. Something like:

Set Variable[$Counter;Value:1]

Set Variable[$Streets; Value:ValueListItems(Get(FileName); "Streets")]

Set Variable[$CountofValues; Value:PatternCount($Streets;"the return character") +1]

Loop

Enter Find Mode[]

Set Field [Table::Streets; GetValue($Streets;$Counter)]

Perform Find[]

Set Variable [$Result; Value:Get(FoundCount)]

Set Variable[$Counter;Value:$Counter +1]

Exit Loop If [$Counter > $CountofValues]

End Loop

There is a symbol for the return character in the 'Specify Calculation' area. It looks a bit like a backward 'P'.

Then you could store the results in a text field or go to another table and make a record for each result. But basically you need someplace to put all this data so you can read it.

If you go the route of making a report table and making a new record for each result it would consist of at least 2 fields - StreetName and StreetCount

Then you could put the street name into the Field 'StreetName' with

Set field [Report::Street;GetValue($Streets;$Counter)]

and

Set field[Report::StreetCount;$Result)]

where 'Report::StreetCount' is the field that holds how many of each street you found.

It's a start. Hope this helps.

[bcooney]

How are you resolving the fact that one Main Street is not the same as another?

[stefangs]

not sure if this is the best way, but it would work: you could create a self relationship called count_street which is street::street.

then you create a aggregate field count(count_street::street).

with 400000 records this would probably slow down screen drawing quite a bit, so you may want to put that on a separate layout you only use when needed.

[Digital Life]

I think that databaser wants to know how many locations are on Main Street as in a delivery route or something. I take that from the comment that all the streets are in the same general area and that there would not be 2 Main streets that close together.

[bcooney]

I suppose you could include zip code.