Jump to content

Calculation of related records

This topic is 7812 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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"), "

Link to comment
Share on other sites

This topic is 7812 days old. Please don't post here. Open a new topic instead.

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
  • Create New...

Important Information

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