Results 1 to 6 of 6

Table structure advice

  1. #1
    emberk is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    May 2014
    Posts
    6

    Table structure advice

    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.
    Last edited by emberk; 10-03-2019 at 05:03 PM. Reason: Added date perameters

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,287
    I don't see any Dates related to your activities??
    Can you show us the tables and relationships you have, or a copy of your test database?
    Suggest you work from a data model and sample data and Stump the Model

    Good luck

  3. #3
    emberk is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    May 2014
    Posts
    6
    Attached is a screen shot of the entire database. The circled area are the tables in question. Also added date information to original post.
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,287
    What exactly does tblEntity represent (simple terms)?
    Same for tblEntityType and tblEntityStatus.

    Can a Parcel have multiple owners?
    In any transaction there will always be 1 Grantor and 1 Grantee?
    It seems there may be some possible merge/subdivide of a parcel to become another parcel with some historical info to be recorded. Some brief overview of the impact of this would help readers.

    Do RecDate and RecNumber refer to Transaction? A brief description may help.
    Where does ExciseNumber originate?


    For hierarchy/pedigree where records refer to other records in the same table see this Allen Browne link.

    This approach was used in post 34-37 here.
    I think you'll need something similar or along the same concept to record and display the ownership history of a parcel, and/or possibly the merger/division of parcel(s).

    Hope that gives you some ideas re where to place the new info in your model.
    Good luck.

  5. #5
    emberk is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    May 2014
    Posts
    6
    tblEntity is the Owner lookup table, thought more people would have understood Owner versus Entity.

    tblEntityType describes a type of Entity (i.e. Development, Commercial) used for internal reporting.

    tblEntityStatus identifies the Entity as Active, Inactive, etc.

    Parcel can only have 1 Owner/Entity at any given time. If ownership changes that field is updated to reflect current ownership.

    Grantor/Grantee - if there are multiple then they are recorded in the same field. Those are not lookup fields.

    We are a development company so it's not unusual for us to acquire individual parcels and the subdivide them.

    RecDate, RecNumber, ExciseNumber all relate to a single transaction. They just provide detail about the transaction.

    I was thinking that perhaps I need an additional field in the tblTransactions to capture a "Parent" Transaction ID, but not sure. I will take a look at the suggested links.

    Thanks for the response.

  6. #6
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    156
    Hi

    On looking at your ER Diagram I am a little concerned that you have not Enforced any Referential Integrity between any tables?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 03-05-2015, 03:30 PM
  2. structure advice?
    By akwormy in forum Forms
    Replies: 2
    Last Post: 06-11-2014, 06:43 AM
  3. general advice needed on db structure
    By mike_980 in forum Access
    Replies: 5
    Last Post: 11-27-2013, 05:29 PM
  4. Advice on structure of VB Code
    By alsaf in forum Programming
    Replies: 2
    Last Post: 12-27-2011, 08:42 AM
  5. Database Structure Advice
    By Douglasrac in forum Access
    Replies: 9
    Last Post: 07-16-2011, 07:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums