Many to Many property identification number problem.
Hello, I'm pretty new to Filemaker and I'm hoping someone might be able to help me out.
I'm using Filemaker pro Version 10 on Windows Vista Home Premium 64bit.
I am working on a file and I have a major many to many problem I don't know how to solve.
I have a couple of tables
File No. 1-Home Owners
This file includes a lot of information, the most important piece is a PIN that is (sometimes) unique, the time that the owner bought and sold their property (if they have not sold this number is todays date). There is a lot of information that is included in this database, such as the value of the home, and so on.
File No. 2-Foreclosures
This file also has a lot of information. Again this file uses the same PIN numbering as file no. 1. It also has a date for when the property was foreclosed on.
The problem I'm having with the relationship I've attempted to build is that the PIN is not very unique. The files I am using go back several years, so many properties have changed hands and each row is the owner, not the PIN, so a PIN might occur in file no. 1 half a dozen times. The same problem occurs in file no. 2. Each row is a foreclosure, and unfortunately there have been a lot of foreclosures in recent history.
What I want to do is create a relationship where if a foreclosure PIN matches a Home Owner PIN AND the foreclosure date falls inbetween the tenure of that owner all (a person could have several foreclosures) of the foreclosure data (including the PIN and Date there are 4 relevant fields I would like to include) would be transferred to the Home Owner row.
I would really appreciate any guidance.
Thanks very much,