Help on Patrick Matthews DConcat function

Dear all,
I am trying on one of Patrick Matthews DConcat function example at "Domain Aggregate" for Concatenating Values by Group in Microsoft Access article as:

SELECT TITLES_PERSONS_IDIOTITES.Title1, DConcat("idiotita,person","TITLES_PERSONS_IDIOTITES","[ID_films] = " & [ID_films]," "," " & ": ") AS Crew, TITLES_PERSONS_IDIOTITES.cnt_p, TITLES_PERSONS_IDIOTITES.cnt_id
FROM TITLES_PERSONS_IDIOTITES
GROUP BY TITLES_PERSONS_IDIOTITES.Title1, TITLES_PERSONS_IDIOTITES.ID_films, TITLES_PERSONS_IDIOTITES.cnt_p, TITLES_PERSONS_IDIOTITES.cnt_id;

Open in new window


cnt_p and cnt_id are simple number(byte) fields to keep the sorting way due to international movies ID presentation

it returns

Title1       | Crew
-------------------------
BLACK SUNDAY | Art Dir.: Nikita Knatz Ass. Dir.: Jerry Ziesmer Ass. Dir.: Larry Franco Ass. Dir.: Marc Monnet Cast: Bruce Dern Cast: Fritz Weaver Cast: Marthe Keller Cast: Robert Shaw Cast: Steven Keats Cinemat.: John A. Alonzo Comp.: John Williams Dir.: John Frankenhei

But what I need is two more tricks more complicated
1. To group and sort by cnt_id and cnt_p
2. To group people by specialty like

(Dir.:) John Frankenheimer (Script.:) Thomas Harris, Ernest Lehman, Kenneth Ross (Cinemat.:) John A. Alonzo (Comp.:) John Williams (Edit:) John A. Alonzo (Art Dir. :) Nikita Knatz (Ass. Dir. :) Jerry Ziesmer, Larry Franco, Marc Monnet (Cast.:) Bruce Dern, Fritz Weaver, Robert Shaw, Marthe Keller, Robert Shaw, Steven Keats

I tried but didn't manage to make it work

Any suggestions would be greatly appreciated.
thank's
Kostas KonstantinidisFilm criticAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
Two questions:
1. Do you know all the titles?
2. How do you determine the order of the titles?
0
aikimarkCommented:
Part of your request will probably require you to supply a representative sample of your table.  Might as well start now.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Kostas,

good question! I have a fairly complex function to combine that I modified to include a category.  Your field names will be different than mine, of course.  If the data isn't all in one table, the first step is to make a query with the data you want to combine, such as this:
sample data for the LoopAndCombine function in Access to concatenate values from several records into one string
As you can see, the records are in no particular order. It is called qCombineMy.

here is a sub to test the function that you can change for your names:
'~~~~~~~~~~~~~~~~ testLoopAndCombine
Sub testLoopAndCombine()
'test LoopAndCombine
's4p
   Dim sTablename As String _
      , sIDFieldname As String _
      , sTextFieldname As String _
      , nValueID As Long _
      , sSortFields As String _
      , sCategoryField As String
   
   sTablename = "qCombineMy" 'this is actually a query
   sIDFieldname = "MyFK" 'if FK is not a number, you will need to add delimiters to LoopAndCombine where it is referenced
   sTextFieldname = "MyText"
   nValueID = 1 'some number you know is in the table
   sSortFields = "MySort1, MySort2"
   sCategoryField = "MyCategory"
   
   Debug.Print LoopAndCombine( _
      sTablename, sIDFieldname, sTextFieldname, nValueID, , , , sSortFields, sCategoryField)

End Sub

Open in new window

and then here is the LoopAndCombine function:
'~~~~~~~~~~~~~~~~ LoopAndCombine
Function LoopAndCombine( _
   psTablename As String _
   , psIDFieldname As String _
   , psTextFieldname As String _
   , pnValueID As Long _
   , Optional psWhere As String = "" _
   , Optional psDeli As String = ", " _
   , Optional psNoValue As String = "" _
   , Optional psOrderBy As String = "" _
   , Optional psCategoryField As String = "" _
   ) As String
'strive4peace
'loop through recordset and combine values to one string
'modified 180125 psCategoryField
   
   'NEEDS REFERENCE
   'a Microsoft DAO Library
   ' -- OR --
   ' Microsoft Office #.0 Access Database Engine Object Library
   
   'PARAMETERS
   'psTablename --> table name (or query name) to get list from
   'psIDFieldname --> fieldname to link on (ie: "BookID")
   'psTextFieldname --> fieldname to combine (ie: "PageNumber")
   'pnValueID --> actual value of ID for this iteration ( ie: [BookID])
   'psWhere, Optional  --> more criteria (ie: "Year(PubDate) = 2006")
   'psDeli, Optional  --> delimiter other than comma (ie: ";", Chr(13) & Chr(10))
   'psNoValue, Optional  --> value to use if no data (ie: "No Pages")
   'psOrderBy, Optional  --> fieldlist to Order By
   '  -- add [Brackets] if names have spaces or special characters
   'psCategoryField, Optional  --> field to categorize by (does NOT sort)
   '  -- if specified, will appear as (CategoryValue: )

   'Set up error handler
   On Error GoTo Proc_Err
      
   'dimension variables
   Dim rs As DAO.Recordset _
      , sSQL As String _
      , vAllValues As Variant _
      , vOrderBy As Variant _
      , vGroupValue As Variant _
      , vNewText As Variant
    
   vAllValues = Null
   vOrderBy = Null
   vGroupValue = "something that this value won't be"
   vNewText = Null
      
   'see if sorting was specified
   If Len(psOrderBy) > 0 Then
      vOrderBy = " ORDER BY " & psOrderBy
   End If

   sSQL = "SELECT [" & psTextFieldname & "]" _
       & IIf(Len(psCategoryField) > 0, ", [" & psCategoryField & "]", "") _
       & " FROM [" & psTablename & "]" _
       & " WHERE [" & psIDFieldname & "] = " & pnValueID _
       & IIf(Len(psWhere) > 0, " AND " & psWhere, "") _
       & vOrderBy _
       & ";"
Debug.Print sSQL

   'open the recordset
   Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
      
   'loop through the recordset until the end
   With rs
      Do While Not rs.EOF
         If Not IsNull(.Fields(psTextFieldname)) Then
            'field has a value -- initialize New Text
            vNewText = Null
            
            'see if there is a grouping field to list first
            If Len(psCategoryField) > 0 Then
               'see if there is a value
               If Not IsNull(.Fields(psCategoryField)) Then
                  'only add to string if it has changed
                  If .Fields(psCategoryField) <> vGroupValue Then
                     'show as (GroupValue:)
                     vNewText = " (" & .Fields(psCategoryField) & ":) "
                     vGroupValue = .Fields(psCategoryField)
                  End If
               End If
            End If
            
            If IsNull(vNewText) Then
               'see if delimiter needs to be added
               vAllValues = (vAllValues + psDeli)
            End If
            
            vAllValues = vAllValues & vNewText _
               & Trim(.Fields(psTextFieldname))

          End If
         .MoveNext
      Loop
   End With 'rs
      
   If IsNull(vAllValues) Then
      vAllValues = psNoValue
   End If
 
   LoopAndCombine = Trim(vAllValues)
   
Proc_Exit:
   On Error Resume Next
   If Not rs Is Nothing Then
      'close the recordset
      rs.Close
      'release the recordset variable
      Set rs = Nothing
   End If
   LoopAndCombine = Trim(Nz(vAllValues, ""))
   Exit Function
   
'if there is an error, the following code will execute
Proc_Err:
   MsgBox Err.Description, , _
     "ERROR " & Err.Number _
      & "   LoopAndCombine"
 
   Resume Proc_Exit
   Resume
End Function

Open in new window

here is the resulting SQL statement:
SELECT [MyText], MyCategory FROM [qCombineMy] WHERE [MyFK] = 1 ORDER BY MySort1, MySort2;

and here is what the function returns for the test values:

(Dir:) Nikita Knatz (Script:) Jerry Ziesmer, Larry Franco, Marc Monnet (Cinemat:) John Frankenhei (Comp.:) John Williams (Edit:) John A. Alonzo (Art Dir:) Nikita Knatz (Ass. Dir.:) Jerry Ziesmer (Cast:) Bruce Dern, Fritz Weaver, Marthe Keller, Robert Shaw, Steven Keats

if something isn't quite right with this,  please let me know, thanks.

The error handler is explained in this EE video:
https://www.experts-exchange.com/videos/1478/Basic-Error-Handling-code-for-VBA-and-Microsoft-Office.html

have an awesome day,
crystal
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Kostas KonstantinidisFilm criticAuthor Commented:
Well, the database keeps all the Greek Cinema history  (short and longs films from 1914 something means about 11000 movies and more than 180000 persons worked for that). So all the records are in Greek language. For my request I designed a sample table with international names for obvious reasons....

aikimark --> Every title has a unic ID_films a Title and all the rest info. So the films sorting is pretty easy.

Hope with the following attachments to be clear the complete design even if some names are in greeklish

In 03.png is clear how the data must be sorted...

First based on cnt_id so that the specialties are presented in the required order
Second based on cnt_p to keep up the order of priority of the people as mentioned in zenerique of each movie

crystal --> I'll try it tomorrow with clear mind and will let you know.

Thank you individually for your response
01.png
02.png
03.png
0
aikimarkCommented:
It would help if you extracted the unique values from the idiotita table and arranged them in the order you need to see them.
0
Kostas KonstantinidisFilm criticAuthor Commented:
aikimark, it doesn't really help because the unicqe values of that table is not critical. It's just an intermediate common number between ST_peoplefilms and T_idiotita keeping the relationship (take a look at 02.png). What is critical is the cnt_id indicator which must points exactly  to the  first part of DConcat in order iditita to be sorted according to cnt_id and that is already extracted into the final query (03.png). Thank's
0
Kostas KonstantinidisFilm criticAuthor Commented:
hey Crystal, just tried it... I converted all the names and It works on immediate window... but how to use the function into query as concatenated values? Also noticed that you use nValueID = 1 I guess to filter for ID_films = 1 What about for all the ID_films? Many thank's
0
aikimarkCommented:
@Kostas

I don't have your database and avoid doing data entry whenever possible.  I'll stay in the thread and see what Crystal can do for you.

What I was looking for would be a recordset, array, collection that resulted from a Select Distinct on the Idiotita and cnt_id fields in the TITLES_PERSONS_IDIOTITES table.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Kostas,

in a query, you can't skip optional parameters ... so do something like this:
   MyList: LoopAndCombine( "myTablename", "myFKfieldname", "MyTextFieldname", [FilmID], "" , ", " , "" , "[mySortField1], [MySortField2]", "Role")

Open in new window

Note that [FilmID] is not surrounded by quotes -- so whatever is the value of the field for that record will be used.

> "What about for all the ID_films?"

yes, that is what MyFK* is for.  Naturally, you would use whatever field names your query has.

*FK = foreign key
If this is not a number, perhaps it should be? Otherwise, you will need to modify LoopAndCombine for a different data type

If you want ALL the records, remove the lines for the WHERE clause :
       & " WHERE [" & psIDFieldname & "] = " & pnValueID _
       & IIf(Len(psWhere) > 0, " AND " & psWhere, "") _

... or you can rearrange parameters to also make the psIDFieldname  and pnValueID also be optional. Once a parameter is declared to be optional, then all subsequent parameters must also be optional. I will leave that up to you to do, though -- since if you are going to use this, which you will probably do a lot since it is useful! then you should understand how it works. Hint:  Create a variable called vWhere and look at how vOrderBy is used.

... but wouldn't you want this list by film?

have an awesome day,
crystal
0
Kostas KonstantinidisFilm criticCommented:
yes sir,
it works

many many thank's
Kostas Konstantinidis
*I can't connect via facebook neither to point on your answer as accepted solution...
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
welcome to Experts Exchange. Perhaps close the question with the same login you used to post it? (and btw, I am not a 'sir') Glad it works for you!
0
Kostas KonstantinidisFilm criticCommented:
Dear Crystal,
I have customized your code according to my needs. The last problem I have is that althogh the querries return fine results the report cuts the fields on 255 characters. Have to add some code on reports too?

thank's
Best regards
/kostas
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
glad it worked for you, Kostas. Ask another question about the report problem
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.