Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>So the link table gives a self join to doc, you absolutely need a doc to be able to:</p> <ol> <li>Link to itself </li> <li>... as many times as it likes</li> <li>Link to another doc</li> <li>... multiple times</li> <li>Be separately linked to by the same other doc</li> <li>... multiple times</li> <li>Store information about the link</li> </ol> <p>So your link table could have 10 separate links 1-1, 1-1, 1-1, 1-2, 1-2, 1-2, 2-1, 2-1, 2-1, 2-2 etc. with just 2 docs in the 'family'.</p> <hr> <p>I expect when you look at this list you will probably think that you don't need most of them, a lot of the inefficiency in your solution might be coming from this unnecessary flexibility. My favoured suggestion would be to start with a strict hierarchy and build minimally from there.</p> <p>But here is my answer anyway, it's tested and working in Access-2010 and local tables. ADODB should work just as well with linked tables. </p> <pre><code>Option Compare Database Option Explicit Const MaxInFamily = 30 'Requires a reference to "Microsoft ActiveX Data Objects 2.x Library" (VBA Menu: Tools, references) Function GetFamily(id As Long) As Long() Dim Found(MaxInFamily) As Long Dim MaxFound As Integer Dim CurrentSearch As Integer Dim Sql As String Dim rs As New ADODB.Recordset Found(1) = id MaxFound = 1 For CurrentSearch = 1 To MaxInFamily If CurrentSearch &gt; MaxFound Then Exit For Sql = "SELECT doc_id_2 as NewID FROM link WHERE doc_id_1 = " &amp; Found(CurrentSearch) _ &amp; " AND doc_id_2 NOT IN (" &amp; ArrayToCsv(Found, MaxFound) &amp; ")" _ &amp; " UNION " _ &amp; " SELECT doc_id_1 FROM link WHERE doc_id_2 = " &amp; Found(CurrentSearch) _ &amp; " AND doc_id_1 NOT IN (" &amp; ArrayToCsv(Found, MaxFound) &amp; ")" rs.Open Sql, CurrentProject.Connection Do While Not rs.EOF MaxFound = MaxFound + 1 Found(MaxFound) = rs("NewID").Value rs.MoveNext Loop rs.Close Next CurrentSearch GetFamily = Found End Function Function ArrayToCsv(SourceArray() As Long, ItemCount As Integer) As String Dim Csv As String Dim ArrayIndex As Integer For ArrayIndex = 1 To ItemCount Csv = Csv &amp; SourceArray(ArrayIndex) If ArrayIndex &lt; ItemCount Then Csv = Csv &amp; ", " Next ArrayIndex ArrayToCsv = Csv End Function </code></pre> <p>Duplicated results and queries are avoided by excluding items already found at the server, and as the link is unidirectional I've used a <code>UNION</code> query to look both ways at once. At most it'll do <code>MaxInFamily</code> round trips to the server.</p>
    singulars
    1. This table or related slice is empty.
    1. POMySQL or VBA Iteration Function To Find Inter-Related Records
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USStephen Turner
    1. USStephen Turner
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POMySQL or VBA Iteration Function To Find Inter-Related Records
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTBountyClose
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks webturner. I'm sure you're right about the union join - as you expressed it better than I did, the links are unidirectional. However, your points (1) and (2) don't apply, as a document can't logically link to itself. Now, that array is certainly something I've never thought of, so I'll try some code out on that.
      singulars
      1. PO
      1. USmaxhugen
    2. COWebturner, your function worked great. I changed GetFamily to return a string, and changed the result to
      singulars
      1. PO
      1. USmaxhugen
    3. COGetFamily = ArrayToCsv(Found, MaxFound) Unfortunately, the function doesn't work directly in a query eg: SELECT * FROM doc WHERE doc_id IN ( GetFamily( [some_doc_id] ) ) Is there something that I'm missing here, or do I need to set the recordsource for forms/reports manually via VBA?
      singulars
      1. PO
      1. USmaxhugen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload