Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    St Albans, Hertfordshire, England
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Engineering Challenge in Access (Access 2k/Visio 2002 std/win 2k)

    I have a database that contains a road map of all tables, fields and relationships in a much larger system, all of the necessary fields are populated:

    tbl_Tables
    System_Key
    Table_Key
    Table_Short_Name
    Table_Name
    Table_Desc

    tbl_Fields
    Table_Key
    Field_Key
    Field_Seq
    Field_Short_Name
    Field_Name
    Field_Desc
    Field_Data_Type
    Field_Len
    Field_Decimals
    Field_Primary_Key_Field
    Field_Foreign_Key
    Field_Allow_Nulls

    tbl_Rels
    Left_Table_Key
    Right_Table_Key
    Left_Field_Key
    Right_Field_Key

    I would like to set up a process, whereby the end user selects the fields that they require in their output query, and the system automatically pilots the gaps between the tables - for example:

    User Requests Fields: Table1.Field1, Table1.Field3, Table7.Field1 (Table names are examples - if only tables were named sequentially <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> )

    I would like the system to determinme, using the ralationships table, that Table1 is related to Table3 and Table4, but only Table4 is related to Table7, so it would return the necessary SQL linking Table1 --> Table4 --> Table7

    It's a little like choosing the fastest route between two points, very much an engineering challenge!

    Any help would be very greatly appreciated.

    Kind regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Engineering Challenge in Access (Access 2k/Visio 2002 std/win 2k)

    You will have to implement a shortest path algorithm. One of the best known is Dijkstra's. If you do a Google search for the words I bolded, you will find descriptions of this algorithm, and pseudo code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •