How to return a count of related Records and concatenate those records in a field

[gmaxey]

I am way over my head here. A few days ago I posted a question about consolidating and returning a sum of extensions.

http://www.vbaexpress.com/forum/showthread.php?71751-How-to-consolidate-and-return-extensions


My half a dozen photos I pasted here when I drafted this posts have dissapeared
Here is a link to the file: https://www.dropbox.com/scl/fi/2p5wz...wtpo5pcan&dl=0

I have now added another dimension to that project. In addition to tblProjs and tblExtensions, I have added tblIssues.







My goal is to create a query that returns the Base_Due_Date from tblProject, the sum Total_Days of all type A extensions, the count of issues and a concantated string of those issue in a single field. Using a likely Rube Goldburg fashioned solution, I have come close.

Peter, you pushed me in the right direction (downright provided the solution) in my previous post. I tried, for several hours, to work out something similar but couldn't find a built-in function that returned count of records or concatenate those records. Instead, I ended up creating my own functions and a sub query that returned the count and concatenated string:

SELECT tblProjects.pkProjID, fcnGetIssueCount([pkProjID]) AS [Issue Count], fcnGetIssues([pkProjID]) AS Issues
FROM tblProjects INNER JOIN tblIssues ON tblProjects.pkProjID = tblIssues.fkProjID
GROUP BY tblProjects.pkProjID;

Option Compare Database
Option Explicit
Public Function fcnGetIssueCount(ID) As Long
Dim oDB As DAO.Database
Dim oRS As DAO.Recordset
Set oDB = CurrentDb
Dim SQL As String
  SQL = "SELECT * FROM tblIssues WHERE tblIssues.[fkProjID]=" & ID & ";"
  Set oRS = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
  oRS.MoveLast
  fcnGetIssueCount = oRS.RecordCount
  Set oDB = Nothing: Set oRS = Nothing
lbl_Exit:
  Exit Function
End Function
Public Function fcnGetIssues(ID)
Dim oDB As DAO.Database
Dim oRS As DAO.Recordset
Set oDB = CurrentDb
Dim SQL As String
Dim lngIndex As Long
Dim A
  SQL = "SELECT * FROM tblIssues WHERE tblIssues.[fkProjID]=" & ID & ";"
  Set oRS = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
  oRS.MoveFirst
  Do While Not oRS.EOF
    If fcnGetIssues = vbNullString Then
      fcnGetIssues = oRS!Summary.Value 'oRS.Fields(3).Value
    Else
      fcnGetIssues = fcnGetIssues & "*~*" & oRS!Summary.Value 'oRS.Fields(3).Value
    End If
    oRS.MoveNext
  Loop
  Set oDB = Nothing: Set oRS = Nothing
lbl_Exit:
  Exit Function
End Function

I then used that query in the main query to return the results shown:

SELECT tblProjects.pkProjID, tblProjects.Base_Due_Date, Sum(Nz(tblExtensions.NumDays)) AS Total_Days_Extended, tblProjects_Issues_Query.[Issue Count], tblProjects_Issues_Query.Issues
FROM (tblProjects LEFT JOIN tblExtensions ON tblProjects.pkProjID = tblExtensions.fkProjID) INNER JOIN tblProjects_Issues_Query ON tblProjects.pkProjID = tblProjects_Issues_Query.pkProjID
GROUP BY tblProjects.pkProjID, tblProjects.Base_Due_Date, tblProjects_Issues_Query.[Issue Count], tblProjects_Issues_Query.Issues, tblExtensions.ExtType
HAVING (((tblExtensions.ExtType)="A"));





It all seems like a brutally complicated mess. Something tells me that this might be a situation where a "UNION" query would eliminate some of the complexity but I don't have any idea how to start. At one point I eliminated the the sub-query and just put those function in the main query. For whatever reason three records were still returned but the Total_Days_Extended field values where doubled.

A lot of stuff here I know. I have tried to upload the file but the upload process spins a bit then returns and error message. Thanks in advance for any interest and poiners.