Calculation of related records

[jnmorrison]

Here's my situation:

I have 3 tables: Locations, People & History.

Each record in People will be unique, meaning they are different people.

History records all the locations a certain person visits.

Locations store the possible locations a person can visit.

A person can visit a location many times, creating many related history records.

I need a calculation in my Location table that tells me how many people have visited this location...not how many times it has been visited. In other words, one location may have been visited by 3 out of 4 of my people, but each one of those 3 visited 2 times. I need my calculation to tell me "3" people have visited.

The reason...so that I can tell which locations have been seen by all, so I can mark that location as being complete for the group (of people).

It seems like this should be easy, but I can't quite get there. Can someone help?

[CobaltSky]

The place to perform the calculation you are puzzling over is within the Locations file, and a method you might like to consider would be:

1. Create a relationship from the Locations file to the History file based on the location field in both files.

2. Create a value list called 'Visitors' within the Locations file, which uses values from the People field in the History file, and select the "Only related values" option, based on the relationship defined at step 1.

3. Create an unstored calculation field (of result type number) in the Locations file, with a formula along the lines of:

PatternCount(ValueListItems("Locations.fp5", "Visitors"), "

[jnmorrison]

SWEET!

I had found another way, but this is much, much better. I had no idea about the valuelistitems trick. What does the (+1) do?

Thanks again.

[CobaltSky]

Values in a value list are separated by carriage returns, which means that there is a carriage return after every entry except the last. So the number of "