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.

[arnelgp]

i used Sub-queries on both the 2 new queries (numbered,1 and 2).
and also introduced a new function ConcatRelated (by the famous Allen Browne).
using smaller queries and then joining them is much easier to manage than one "slam dunk" query
that does it all.

[gmaxey]

Thank you. I will look and attempt to understand/employ in the larger project.

BREAK

Very interesting.

The Select Count (which I was not aware of) eliminated some complications. Question: What is the meaning/purpose of ("1") in the e.g, Count ("1"). I can change that to 2, 3, 100 or Man in the moon for that matter and I get the same results.

The ultimate goal at the end of the day is to present this query information in a from and or report. Do you know how to convert Issues e.g., "Test Issue 1/Project 1, Test Issue 2/Project 1" ...

... into a multiline display in the report or form. Or display as multiple lines back in the query?
Test Issue 1/Project 1
Test Issue 2/Project 1

[jdelano]

I used much the same process as arnelgp described:

SELECT pkProjID, Base_Due_Date, Total_Issues, Total_Days, fcnGetIssues([pkProjID]) AS Issues
FROM (tblProjects p
LEFT OUTER JOIN
    (Select count(pkIssueID) as Total_Issues, fkProjID from tblIssues Group By fkProjID) as ci
    on ci.fkProjID=p.pkProjID)
LEFT JOIN 
    (Select iif(IsNull(sum(NumDays)),0, sum(NumDays)) as Total_Days, fkProjID from tblExtensions where ExtType='A' Group By fkProjID) as se
    on se.fkProjID=p.pkProjID

While using your fcnGetIssues function.

[gmaxey]

jdelano,

Thank you as well. You both are transforming my mess into something surely logical to an experienced Access user. Unfortunately my background is Word and Word VBA so much of this stuff while beautiful in expression is incomprehensible at this stage and impossible for me to create on my own.

Is what you have done here what is know as a UNION query? When I create my querys, I use the Design View to drag in tables and connect fields. Then I can sometimes following what the resulting SQL means. What do you do in the design view to cause one query to be called Left Inner, Inner, Outer etc.?

[arnelgp]

normally you use "fieldname" inside the Count() SQL function.
the problem of using field, whether you have Criteria on your query or not on that field, the Query engine will evaluate the field if it is Null or Not.
if it is Not Null, it is included in the Count, if Null, ignored.
using Constant (like "1", or any expression) results in much faster execution since no field is evaluated.

you can create as many query as you like to satisfy what you need.
in this case, you want those issues on separate lines so create new query (4_qryReportPurpose).
then based on that query create the report.
note (on design view of the report), the 3 first fields property (Property->Format->Hide Duplicates) are set to Yes.
you can only do this on the report (hide dups) and not on the form.

[gmaxey]

Thanks. I'm not sure I see the result, 5 records returned instead of 3 but what I am looking for in the report would be:

Project Base_Due_Date Extension Issues
1 7/10/2024 10 1 Test Issue 1/Project 1
2 Test Issue 2/Project 1
2 7/15/2024 15 1 Test Issue 1/Project 2
3 ... and so on.

2024-06-27_8-01-30.jpg

I hope that makes sense.

Edit:

Duh. I just saw your report. Perfect. If I can just replicate that in my larger project.

[jdelano]

jdelano,

Thank you as well. You both are transforming my mess into something surely logical to an experienced Access user. Unfortunately my background is Word and Word VBA so much of this stuff while beautiful in expression is incomprehensible at this stage and impossible for me to create on my own.

Is what you have done here what is know as a UNION query? When I create my querys, I use the Design View to drag in tables and connect fields. Then I can sometimes following what the resulting SQL means. What do you do in the design view to cause one query to be called Left Inner, Inner, Outer etc.?

I am using what is called SubQueries, I've embedded other queries in place of tables. I apologize I should have included information about them SQL subqueries (Microsoft Access SQL) | Microsoft Learn.

For the query view I used the SQL view in Access SQL View in Access- Instructions - TeachUcomp, Inc. SQL is Structured Query Language, which just means the DB engine understands these functions and commands for retrieving data from the tables in your Access database, the normal query view is Access hiding these commands behind a drag and drop, point and click interface.

Using Joins LEFT JOIN, RIGHT JOIN operations (Microsoft Access SQL) | Microsoft Learn, I used those to show all the projects and then only the data that matches from the other tables, you'll see I changed the data in tblExtensions so that project id 3 had none, illustrating that even though there wasn't a record to join with (between projects and extensions) the project record was still included in the result, without the LEFT join that record would have been excluded because both tables didn't have an associated project Id.

I hope this helps explain it a little better. Don't knock yourself, you've done a good job in jumping from Word VBA to Access, they're two totally different worlds.

[gmaxey]

jdelano/arnelgp

I am comparing the solutions you have both provided. I am in no position to judge or even comment on which is best. Only looking at them to see if I can possibly learn to fish rather that be fed fish. I can't really see why arnelgp's 3 query returns 3 records and his 4 query returns 5. jdelano, in how would your query be modified to return the same result as arnelo's query 4?

Second question. The concept of "Normalization" is a cloudy one as well. Would either of you care to comment if you feel this simple database example is "normalized"? Thank you.

[jdelano]

jdelano/arnelgp

I am comparing the solutions you have both provided. I am in no position to judge or even comment on which is best. Only looking at them to see if I can possibly learn to fish rather that be fed fish. I can't really see why arnelgp's 3 query returns 3 records and his 4 query returns 5. jdelano, in how would your query be modified to return the same result as arnelo's query 4?

Second question. The concept of "Normalization" is a cloudy one as well. Would either of you care to comment if you feel this simple database example is "normalized"? Thank you.

We're entering the "too many cooks in the kitchen" territory. I'll back out. Neither is "better" per say, just different. Mine returns 3 because in the test DB I have, there are only 3 projects. arnelgp is supremely adept at Access and its idiosyncrasies. They'll get you where you need to be.

Sorry for the confusion.

[arnelgp]

your db is already normalized (for me).
the only issue is that you have created index on NumDays field (both on tblExtensions and tblIssues).
only create index if the field is involved in a relationship (PK or FK field) or that it is involved in the Criteria of a Query.
Index can make the db faster to search but will easily bloat your db.

[gmaxey]

jdelano, I wish you wouldn't back out. I would certainly be interested to see how you would create a report similar to the report arnegp did in the file he attached earlier. There are only three projects but his query #4 returns five records because both project 1 and project 3 each have two issues.

anelgp, I'm confused. My undertanding is to create relationships you have a Primary Key in the main table e.g., e.g,. pkProjID and then the associated tables have a primary key and a foreign key linking it to the main (or other tables). e.g., pkExtensions and fkProjects/pkIssues and fkProjects ect.

Relationship.jpg

I don't understand what you mean by: "you have created index on NumDays field (both on tblExtensions and tblIssues)" I just don't see that.

One other issue. While I was tinkering with the attached file (trying to consolidate define my method, yours, and jdelano's), I suddenly started getting an error when I attempted to run your 2_qryProject issues. The error was occurring in the ConcatRelated function on this line:

Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)

DB Engine Error.jpg


I fixed that by changing that line to:
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)

Any idea why that error occurred? Later after rebooting the PC, I change it back to your line and it is working again. Is there a reason for DBEngine(0)(0) vice CurrentDb?

Thank you both. I have a long, long way to go and a lot to learn. Unfortunately, unlike with Word, I rarely delve into Access and quickly forget what little I learn