Calculation of ownership duration

[Bmiller4evr]

About the data. I have 1 table that contains current ownership information about real property and the date it was purchased. Each piece of real property has a unique account number. In a second table I have prior ownership information about the real property, if prior ownership records are in existence, then the account number exists and the date the owner bouts the property is recorded with the owners name, address, ect. Some property records have no prior owners, and some have several, in those instances the unique account number from the first table will be present in the second table somewhere between zero and several times. To make sure this is clear table 1 has these fields: Acct Num (Unique), Deed Date, Owner name. Table 2 has Acct: Acct Num (Not Unique), Deed Date, Prior Owner Name.

About the problem. I need to calculate the length of time that previous owners have held the property, not the current owner, but the previous owners. For example I would like to know that the previous owner of account number 1234, if such records exist, owned the property for x amount of days, and the owner of the property before that, again, if the records exist, owned it for y amount of days.

Currently I browse the records from the 1st table and use a portal to display the prior ownership information if it is available. What I now need is the capacity to see duration of time that previous owners have held the property and search by that criteria.

I hope that I have been concise in explaining my situation and welcome the advise from this community in helping me to solve this problem.

[comment]

It's not the best starting point, IMHO. Why not keep all deeds - current and past - in the same table? Then you could use a self-join to determine the date of the next deed (if one exists) and calculate the duration of each ownership.

You can still do the same thing with your current structure - it's just more complicated: if there's no subsequent deed in the PriorDeeds table, you must get the end date from the CurrentDeeds table.

[Bmiller4evr]

No, it is not the best starting point. I did not choose for the records to be kept that way. What is the calculation that will measure the time between the 2 dates? how will it know which 2 dates to calculate between if there are multiple prior owners? When I display in a portal I can sort, but if I create a new field that shows time of ownership (hopefully u can provide the calculation) how will it know which of the prior deed dates to compare with?

[Vaughan]

When I display in a portal I can sort, but if I create a new field that shows time of ownership (hopefully u can provide the calculation) how will it know which of the prior deed dates to compare with?

A sort can be applied at the base relationship level, not just the individual portal. Say the relationship was sorted by SaleDate, ascending order, then the last date of sale would be calculated by Last( deeds::saledate ).

[Bmiller4evr]

Vaughan, that does certainly help. I guess the next step would be to add a field in the Prior owner table so that each record would calculate the time between the date on that record and the deed date of the current owner on the primary table. Someone please tell me what function would do that.... but how would this new field know to relate to the primary table or other records in the prior owners table if it was a record of say 2 or 3 owners ago?

[comment]

Perhaps I am missing something, but I would calculate the duration of each ownership in its own record rather than the duration of the previous one - see the attached example:

Ownerships.zip

[Bmiller4evr]

If in your example George is the most recent prior owner, how does that calculation get made so that it compares George's deed date with the Current owner in primary table?

[Bmiller4evr]

Here is your table back Comment. It more accurately reflects how my data is stored.

http://dl.dropbox.com/u/24727871/Ownerships.fp7