none
Newbie - Link Field to Itself? RRS feed

  • Question

  • Hi all

    No idea how to proceed with this!

    Essentially my database has a contract table and multiple equipment-details tables.

    So, when entering data about a piece of equipment, the user first selects a contract to attribute this equipment to and then enters the equipment specific details.

    What I would like to do is include the facility to add a link between two or more contracts and the reason for that link - for instance a project may have equipment supplied in two phases under two separate contracts but with each being essential for the end result.

    Once the link has been established, I would like the reason displayed each time a query is run against any linked contract.

    Typical reason might be "See also pre-engineering contract C7154".

    Thanks for any enlightenment.

    Phil

    Sunday, November 29, 2015 1:49 PM

Answers

  • Do you wish to pursue the associations to an arbitrary number of levels?  If so you will need to model the relationship type between the instances of the Contracts table by means of a further table, e.g.

    ContractReferences
    ….ReferencingContractID
    ….ReferencedContractID
    ….ReferenceReason

    This is the classic model for an adjacency list to represent a convergent graph.  The first two columns are the composite key of the table, and the third column represents the non-key attribute of the reason for the reference.

    Returning the referenced contracts to a fixed number of levels is simply a fixed number of joins of instances of the Contracts table via a fixed number of instances of the ContractReferences table.  Doing so to an arbitrary number of levels requires recursive querying, which JET/ACE SQL as used natively by Access does not support, but which can be simulated.  You'll find an example as BoM.accdb in BoM.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    The zip archive for this little demo file also includes PartsTree.accdb, which illustrates the simpler approach, with associations to a fixed number of levels, expressed in the demo as an indented report.

    For another illustration of the use of an adjacency list table, this time in a genealogical context, see FamilyStructure.zip in the same OneDrive folder.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Sunday, November 29, 2015 3:53 PM Hyperlink inserted.
    • Marked as answer by TheHC Monday, November 30, 2015 2:50 PM
    Sunday, November 29, 2015 3:52 PM

All replies

  • It's called adjacency list or self-referencing table.

    The normal scheme is

    Just add your table twice in the relationship tool. ParentContractID is NULL for contracts without parent.


    Sunday, November 29, 2015 2:06 PM
  • Do you wish to pursue the associations to an arbitrary number of levels?  If so you will need to model the relationship type between the instances of the Contracts table by means of a further table, e.g.

    ContractReferences
    ….ReferencingContractID
    ….ReferencedContractID
    ….ReferenceReason

    This is the classic model for an adjacency list to represent a convergent graph.  The first two columns are the composite key of the table, and the third column represents the non-key attribute of the reason for the reference.

    Returning the referenced contracts to a fixed number of levels is simply a fixed number of joins of instances of the Contracts table via a fixed number of instances of the ContractReferences table.  Doing so to an arbitrary number of levels requires recursive querying, which JET/ACE SQL as used natively by Access does not support, but which can be simulated.  You'll find an example as BoM.accdb in BoM.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    The zip archive for this little demo file also includes PartsTree.accdb, which illustrates the simpler approach, with associations to a fixed number of levels, expressed in the demo as an indented report.

    For another illustration of the use of an adjacency list table, this time in a genealogical context, see FamilyStructure.zip in the same OneDrive folder.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Sunday, November 29, 2015 3:53 PM Hyperlink inserted.
    • Marked as answer by TheHC Monday, November 30, 2015 2:50 PM
    Sunday, November 29, 2015 3:52 PM
  • Thanks Stefan

    I have added the field LinkContractID along with a LinkFact field for the description of the reason for the link.

    But I don't see how to query the resulting tables via a form to add the link and reason.

    Can you explain how to move forward please?

    Tried to paste image of created relationships without success.

    

    Thanks

    Phil

    Sunday, November 29, 2015 5:02 PM
  • You would use a sub-query to get that information.

    Can you publish a working sample on OneDrive?

    Sunday, November 29, 2015 6:48 PM
  • Hi Ken/Stefan

    Firstly Ken thanks for links to your sample files (I am already using your BrowseDemo sample in this DB).

    I have looked at your parts sample, but cannot quite see how this relates to the current issue. When trying to launch your BoM sample, I get an error "unrecognised database format" (I am using Access 2007).

    The database in its current form can be downloaded at http://a360.co/1PUR7nf

    Looking at the relationships diagram, I have added the second incidence of the contract table.

    What I would like is for each contract to be linkable to any other contract(s) with a statement of reason attached.  Then each time the contract number is used in a query e. g. FrmQryPrincDetailsEvap, a message should be displayed such as "This contract is linked to contract(s) <contract(s)> : Reason statement"

    As a newbie I have no idea how to populate/arrange the two incidences of the contract table, or to check for contract links and display above message. Presumably the check can be coded as part of the afterupdate function of the contract combo box.

    Any further help gratefully received.

    Phil

    Sunday, November 29, 2015 8:27 PM
  • My BoM demo is relevant to your scenario in two possible ways:

    1.  If you want to return the chain of associations of contract to contract down as far as it goes, however far that might be, then the BoM demo would be the appropriate as this simulates the recursive querying to return the associations to an arbitrary depth.

    2.   If, on the other hand, you wish to return the associations to a fixed depth, which can be any number from 2 upwards then the simpler PartsTree demo provides an appropriate model as its query illustrates the sort of chain of joins of multiple instances of the tables which you'd need.  You'd just need to add an extra 'reason' column to the table which models the adjacency list, which in relational-speak is a many-to-many relationship type between two instances of a single table (contracts in your case).

    So, which of these two approaches is the one you want to do?

    Ken Sheridan, Stafford, England

    Sunday, November 29, 2015 8:52 PM
  • Hi Ken

    The problem I am trying to address is far less complex than your solutions suggest.

    As noted earlier, I could not open your BoM example at home (Access 2007) but have had no trouble at work today (Access 2010).  This gives the many-to-many relationship and junction table which I believe is the way forward. All I needed was to define a link between two contract IDs and specify a reason in that junction table, just as you have described above in your ContractReferences table scheme above.

    Many thanks for the starter (again).

    Phil

    Monday, November 30, 2015 2:51 PM