Need assistance with a structure to track and report on the "lineage" of an item.
We have a database that we use to capture data points on parcels of real property. One of the main tables (tblParcels) includes: PID (primary key autonumber), Owner (lookup to tblOwners), Parcel Number, etc.
I have a new request to start tracking the various "transactions" involving the parcels. A transaction can be a purchase, sale or subdivision.
I created the following 2 new tables:
tblTransactions - to capture details of the transaction
tblT2P - to capture relation between tblTransactions and tblParcels
I created a form based on tblTransactions (frmTransactions) and added to it a subform from tblT2P (subfrmT2P) to allow for the recording of specific Parcels (tblParcels) that were part of the transaction.
I am now stumped as to the best method for reporting on the "lineage" for a specific parcel. I can't pull it based on Owner (as the Parcel might have changed hands) and I can't pull on Parcel Number because it could have been subdivided.
Here is an example:
TR01 - Grantor: Bob; Grantee: Sally; Type: Purchase; Date: 07/02/2015; Parcels: A, B, C, D, E, F
TR02 - Grantor: Sally; Grantee: Sally; Type: BLA; Date:10/11/2016; Parcels: G, H, I, J (result of combining Parcels A, B, D, & F)
TR03 - Grantor: Sally; Grantee: Jane; Type: Sale; Date: 11/08/2017; Parcel: G
TR04 - Grantor: Sally; Grantee: Pete; Type: Sale; Date: 11/0/2018; Parcel: H
TR05 - Grantor: Sally; Grantee: Beth; Type: Sale; Date: 09/29/201; Parcel: I
TR06 - Grantor: Sally; Grantee: Nick; Type: Sale; Date: 06/02/2017; Parcel: J
Ownership recap:
Parcel A - no longer exists
Parcel B - no longer exists
Parcel C - owned by Sally
Parcel D - no longer exists
Parcel E - owned by Sally
Parcel F - no longer exists
Parcel G - owned by Jane
Parcel H - owned by Pete
Parcel I - owned by Beth
Parcel J - owned by Nick
The "lineage" report for Parcel J would need to include TR06, TR02 and TR01. I can get a report which will show TR06 and TR02, but because the Parcel Number changed between TR01 and TR02, TR01 doesn't show on my report. I could go into TR01 and associate all the new Parcels created via TR02 but then I'd have to continually update previous transactions to include any newly created Parcels. Add to this that the Transaction table is supposed to show only those Parcels included at the time of the Transaction, to add others at a later time would call into question which Parcels were actually a part of the original Transaction.
I feel like I am missing a piece here, but not even sure what that might be. If this request and example make any sense to someone else I'd really appreciate any input you might be able to provide.