Create single row with data from multiple rows from another table.

[cuteswan]

This is more of a "would be nice" so it's low priority, but thinking about how to do it drives me crazy anyway.

In Access 2000, I have a database of stories on-line where many have multiple parts, and the part numbers aren't necessarily contiguous. I use a query to generate raw HTML table data (so I can export it and slap into the HTML file). It would be great to include links to each part in-line with the story info. Ideally there should be only one row for each story and, if there are multiple parts, then they will be in-line with the story name and link table cell.

Story table:
Story_ID ...AutoNumber (primary key)
Title ...Text
by ...Text
RE_link ...Text
Update ...Date/Time

(There are many more fields in the real file, but I stripped them out for this example db.)

direct_links table:
{
StoryID ...Number
Part ...Number
} (primary key)
direct_link ...Text

For now I've made a query (a monster string function make_table_data) to generate table data that outputs in the following format:

<tr><td><a href="RE_link">Title</a></td><td>by</td><td>Update</td></tr>

Now I'd like to add the multiple direct_link entries to the individual output rows. I'm positive I saw code to create queries that integrate the many fields from a many-to-one table into the one row (and stupid me for not realizing it at the time). The ideal query should give me the following:

<tr><td><a href="RE_link">Title</a> <a href="direct_linkPart">(Part)</a> for each Part no.</td><td>by</td><td>Update</td></tr>

At one point I tried making a pivot table (_pivot_by_part_nos) but I finally realize that's probably not the right way to go. (Access doesn't like the code for that at all.)

Any ideas are greatly appreciated.

 

[Beetle]

The following is a version of a Concatenate function originally created by Duane Hookom. I have modified it to return the results you want.

Function Concatenate(pstrSQL As String, _
        Optional pstrDelim As String = ", ") _
        As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
'   this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'
'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
    'Dim db As DAO.Database
    'Dim rs As DAO.Recordset
    'Set db = CurrentDb
    'Set rs = db.OpenRecordset(pstrSQL)
'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
    Dim rs As New ADODB.Recordset
    rs.Open pstrSQL, CurrentProject.Connection, _
    adOpenKeyset, adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
                "<a href=" & Chr(34) & "direct_linkpart" & Chr(34) & ">" & _
                .Fields(0) & "</a>"
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
'====== uncomment next line for DAO ========
    'Set db = Nothing
    Concatenate = strConcat
End Function

You would place this function in a global module, then call it in your query like;

table_rows: "<tr><td align=left><a href="""+[Story].[RE_link]+""">"+[Story].[Title]+"</a>" & Concatenate("Select [direct_link] From [direct_links] Where StoryID=" & [Story_ID]) & "</td><td align=left>"+nz([Story].[by]," ")+"</td><td>"+IIf(IsNull([Story].[update])," ",Format([Story].[update],"yyyy-mm-dd"))+"</td></tr>"

I have attached a modified copy of your db which returns the results that you wanted (I think) in the query.

 

[Beetle]

Oops. Uploaded the wrong file. Here is the correct one.

 

[cuteswan]

That is fantastic, so thank you! (Both of you.)

The "direct_linkpart" from my example had snuck in there, but the way you/Duane wrote it made that easy to fix (and also wrap the links around the part nos.). This is a huge help and I appreciate it greatly.