Combine Months Name in One Row

[irsmalik]

Hello Friends

For me, it it a difficult question but hope there will be an easy solution. From query, I am getting details of Farmers meetings data i.e.

Company Region Territory DTP TDate Periods

Here is the query:

SELECT DM.CName AS Company, DM.RName AS Region, DM.AreaName AS Territory, Sum(DM.DTP) AS SumOfDTP, DM.TDate, Format([TDate],"mmm") AS Periods
FROM DM
GROUP BY DM.CName, DM.RName, DM.AreaName, DM.TDate, Format([TDate],"mmm")
HAVING (((Sum(DM.DTP))>0))
ORDER BY DM.CName, DM.RName, DM.AreaName, Sum(DM.DTP) DESC , DM.TDate, Format([TDate],"mmm");

Query gives me 5 records data wise... I want this results in One Records which gives me Months name with Comma Like (Jan, Feb, Mar etc). Please see the attached picture for more clarification.

Hoping for help... thanks
irsmalik

[cactusdata]

You can use my DJoin function in this query:

SELECT 
    DM.CName AS Company, 
    DM.RName AS Region, 
    DM.AreaName AS Territory, 
    Sum(DM.DTP) AS TotalDTP, 
    DJoin("StrConv(Format(TDate,'mmm'),3)","DM","",", ") AS Periods
FROM 
    DM
GROUP BY 
    DM.CName, 
    DM.RName, 
    DM.AreaName
HAVING 
    Sum(DM.DTP)>0
ORDER BY 
    DM.CName, 
    DM.RName, 
    DM.AreaName;

Output: See the attached file, please.

This is the function:

' Returns the joined (concatenated) values from a field of records having the same key.
' The joined values are stored in a collection which speeds up browsing a query or form
' as all joined values will be retrieved once only from the table or query.
' Null values and zero-length strings are ignored.
'
' If no values are found, Null is returned.
'
' The default separator of the joined values is a space.
' Optionally, any other separator can be specified.
'
' Syntax is held close to that of the native domain functions, DLookup, DCount, etc.
'
' Typical usage in a select query using a table (or query) as source:
'
'   Select
'       KeyField,
'       DJoin("[ValueField]", "[Table]", "[KeyField] = " & [KeyField] & "") As Values
'   From
'       Table
'   Group By
'       KeyField
'
' The source can also be an SQL Select string:
'
'   Select
'       KeyField,
'       DJoin("[ValueField]", "Select ValueField From SomeTable Order By SomeField", "[KeyField] = " & [KeyField] & "") As Values
'   From
'       Table
'   Group By
'       KeyField
'
' To clear the collection (cache), call DJoin with no arguments:
'
'   DJoin
'
' Requires:
'   CollectValues
'
' 2019-06-24, Cactus Data ApS, Gustav Brock
'
Public Function DJoin( _
    Optional ByVal Expression As String, _
    Optional ByVal Domain As String, _
    Optional ByVal Criteria As String, _
    Optional ByVal Delimiter As String = " ") _
    As Variant
 
    ' Expected error codes to accept.
    Const CannotAddKey      As Long = 457
    Const CannotReadKey     As Long = 5
    ' SQL.
    Const SqlMask           As String = "Select {0} From {1} {2}"
    Const SqlLead           As String = "Select "
    Const SubMask           As String = "({0}) As T"
    Const FilterMask        As String = "Where {0}"
 
    Static Values   As New Collection
 
    Dim Records     As DAO.Recordset
    Dim Sql         As String
    Dim SqlSub      As String
    Dim Filter      As String
    Dim Result      As Variant
 
    On Error GoTo Err_DJoin
 
    If Expression = "" Then
        ' Erase the collection of keys.
        Set Values = Nothing
        Result = Null
    Else
        ' Get the values.
        ' This will fail if the current criteria hasn't been added
        ' leaving Result empty.
        Result = Values.Item(Criteria)
        '
        If IsEmpty(Result) Then
            ' The current criteria hasn't been added to the collection.
            ' Build SQL to lookup values.
            If InStr(1, LTrim(Domain), SqlLead, vbTextCompare) = 1 Then
                ' Domain is an SQL expression.
                SqlSub = Replace(SubMask, "{0}", Domain)
            Else
                ' Domain is a table or query name.
                SqlSub = Domain
            End If
            If Trim(Criteria) <> "" Then
                ' Build Where clause.
                Filter = Replace(FilterMask, "{0}", Criteria)
            End If
            ' Build final SQL.
            Sql = Replace(Replace(Replace(SqlMask, "{0}", Expression), "{1}", SqlSub), "{2}", Filter)
 
            ' Look up the values to join.
            Set Records = CurrentDb.OpenRecordset(Sql, dbOpenSnapshot)
            CollectValues Records, Delimiter, Result
            ' Add the key and its joined values to the collection.
            Values.Add Result, Criteria
        End If
    End If
 
    ' Return the joined values (or Null if none was found).
    DJoin = Result
 
Exit_DJoin:
    Exit Function
 
Err_DJoin:
    Select Case Err
        Case CannotAddKey
            ' Key is present, thus cannot be added again.
            Resume Next
        Case CannotReadKey
            ' Key is not present, thus cannot be read.
            Resume Next
        Case Else
            ' Some other error. Ignore.
            Resume Exit_DJoin
    End Select
 
End Function
 
' To be called from DJoin.
'
' Joins the content of the first field of a recordset to one string
' with a space as delimiter or an optional delimiter, returned by
' reference in parameter Result.
'
' 2019-06-11, Cactus Data ApS, Gustav Brock
'
Private Sub CollectValues( _
    ByRef Records As DAO.Recordset, _
    ByVal Delimiter As String, _
    ByRef Result As Variant)
 
    Dim SubRecords  As DAO.Recordset
 
    Dim Value       As Variant
 
    If Records.RecordCount > 0 Then
        While Not Records.EOF
            Value = Records.Fields(0).Value
            If Records.Fields(0).IsComplex Then
                ' Multi-value field (or attachment field).
                Set SubRecords = Records.Fields(0).Value
                CollectValues SubRecords, Delimiter, Result
            ElseIf Nz(Value) = "" Then
                ' Ignore Null values and zero-length strings.
            ElseIf IsEmpty(Result) Then
                ' First value found.
                Result = Value
            Else
                ' Join subsequent values.
                Result = Result & Delimiter & Value
            End If
            Records.MoveNext
        Wend
    Else
        ' No records found with the current criteria.
        Result = Null
    End If
    Records.Close
 
End Sub

Code is also at GitHub: VBA.DJoin

Full documentation is here:
Join (concat) values from one field from a table or query

[irsmalik]

Hello cactusdata

Thanks for help & reply. I copied first code in query and other code in Module1 and run.

It gives me error

Undefined function 'DJoin' in expression

I did not change any thing in both codes... Where I am making mistake. Kindly correct me. thanks...

irsmalik

[cactusdata]

You probably need to compile and save the module.

[irsmalik]

Yes it is already done.. but the still giving error.

irsmalik

[cactusdata]

Can't tell. But study my demo.
The query is DMall.

For some reason, the site refuses to upload my demo with your table and query.
So, I have uploaded it here: VBA.DJoin Demos

It the file DJoinDemo DM.zip

[irsmalik]

Dear Mr cactusdata

Thanks for your help... but unfortunately, it is Not Downloading.
I have tried to Sign Up... but not succeeded.

You are requested to please send its zip copy to
{NeoPa edit - email addresses removed in accordance with our rules & to protect you.}
thanks
irsmalik

[cactusdata]

Check your inbox, please.

[irsmalik]

Dear cactusdata

Thanks for your help. I have just download your file and checked it. Yes this is the result I need. It is OK..

Now I add 3 more record to your file and run query DJoin. But it did not show the newly total records. I closed database and re-open it, now it is showing the new + old records.

Can it be Refresh within the database when it is being edited... ?
thanks
irsmalik

[irsmalik]

Dear cactusdata

Thanks for your help.. I really appreciate you spend time for me...
Once again thank you so much.

irsmalik

[cactusdata]

Yes, reset the cache before running the query again:

' To clear the collection (cache), call DJoin with no arguments:
'
'   DJoin

[irsmalik]

Dear Cactusdata....

1. where to above mentioned sentence....

2. There is also one more problem
If there are more than 1 Areas &
One Area has 3 records, and other Area has 6 record
query will show 6 records for both areas....
it should count & display only the ACTUAL record it has.

thanks
irsmalik




irsmalik

[irsmalik]

I mean where to above mentioned sentence....

[cactusdata]

Just call DJoin in VBA:

DJoin

Or you can modify the query for an automatic reset of DJoin:

SELECT 
    DM.CName AS Company, 
    DM.RName AS Region, 
    DM.AreaName AS Territory, 
    Sum(DM.DTP) AS TotalDTP, 
    DJoin("StrConv(Format(TDate,'mmm'),3)","DM","",", ") AS Periods
FROM 
    DM
WHERE
    DJoin() Is Null
GROUP BY 
    DM.CName, 
    DM.RName, 
    DM.AreaName
HAVING 
    Sum(DM.DTP)>0
ORDER BY 
    DM.CName, 
    DM.RName, 
    DM.AreaName;

[cactusdata]

> One Area has 3 records, and other Area has 6 record

Then apply a filter to DJoin as you would for any other Dxxx function:

SELECT 
    DM.CName AS Company, 
    DM.RName AS Region, 
    DM.AreaName AS Territory, 
    Sum(DM.DTP) AS TotalDTP, 
    DJoin("StrConv(Format(TDate,'mmm'),3)","DM","[AreaName]='" & [Territory] & "'",", ") AS Periods
FROM 
    DM
WHERE 
    DJoin() Is Null
GROUP BY 
    DM.CName, 
    DM.RName, 
    DM.AreaName
HAVING 
    Sum(DM.DTP)>0
ORDER BY 
    DM.CName, 
    DM.RName, 
    DM.AreaName;

[Rabbit]

An alternative way to do this without VBA is to use a crosstab query

[irsmalik]

Dear Mr cactusdata

Sorry for late reply... Thank you very much for your help and spending your time. Results of my query are now exactly I wanted. I will be once again thankful..... if... if ... one more thing to be helped.

Suppose if There are 5 meetings in one territory in April... query give me Apr, Apr, Apr, Apr, Apr. (means 5 time Apr). Can this be only One Apr....

Thanks in advance for your help.
irsmalik

[cactusdata]

Then you will have to group by the month.

So, either replace above table DM with a query where you group by the month and return, say, the first day of each month, or let DJoin use an SQL query as source (the domain) as exemplified in the in-line documentation:

' The source can also be an SQL Select string:
'
'   Select
'       KeyField,
'       DJoin("[ValueField]", "Select ValueField From SomeTable Order By SomeField", "[KeyField] = " & [KeyField] & "") As Values
'   From
'       Table
'   Group By
'       KeyField

That could turn out like this:

SELECT 
    DM.CName AS Company, 
    DM.RName AS Region, 
    DM.AreaName AS Territory, 
    Sum(DM.DTP) AS TotalDTP, 
    DJoin("StrConv([Period], 3)","Select AreaName, MonthName(Month([TDate])) As [Period] From DM Group By CName, RName, AreaName, MonthName(Month([TDate]))","[AreaName]='" & [Territory] & "'",", ") AS Periods
FROM 
    DM
WHERE 
    DJoin() Is Null
GROUP BY 
    DM.CName, 
    DM.RName, 
    DM.AreaName
HAVING 
   Sum(DM.DTP) > 0
ORDER BY 
   DM.CName, 
   DM.RName, 
   DM.AreaName;

Then, I guess, you could mark this as answered.

[irsmalik]

Dear Mr cactusdata

First I made a query of DM table where I convert TDate into Month. And then Use this query in another query and insert your new Select statement lines. But Query is producing Syntax Error.

The Expression you entered contains invalid syntax
You may have entered an operand without an operator

Regards from
irsmalik

[cactusdata]

Dates and months are not the same.
So, either use my tested and verified method or correct your query, as I noted, to return a date.

[irsmalik]

Dear Mr cactusdata

Sorry to confess that I am failed... I could not follow your instructions. Rather I tried as per my level of access programming.

From your above mentioned DJoin function and query it is producing results as I required. But I want some more better results. If Apr comes for 5 times and Aug comes for 3 times, it should display
(Apr 5), (Aug 3).

Kindly guide me where should I change. In table or query..?

Thanks for your time.
irsmalik

[cactusdata]

You could include the count in the source query of DJoin:

SELECT 
    DM.CName AS Company, 
    DM.RName AS Region, 
    DM.AreaName AS Territory, 
    Sum(DM.DTP) AS TotalDTP, 
    DJoin("'(' & StrConv([Period], 3) & ' ' & MonthCount & ')'","Select AreaName, MonthName(Month([TDate]),True) As [Period], Count(*) As MonthCount From DM Group By CName, RName, AreaName, MonthName(Month([TDate]),True)","[AreaName]='" & [Territory] & "'",", ") AS Periods
FROM 
    DM
WHERE 
    DJoin() Is Null
GROUP BY 
    DM.CName, 
    DM.RName, 
    DM.AreaName
HAVING 
   Sum(DM.DTP) > 0
ORDER BY 
   DM.CName, 
   DM.RName, 
   DM.AreaName;

Output will be similar to:

Company    Region    Territory    TotalDTP    Periods
GREENLET    HYDERABAD    AGAPURA    2    (Jul 1), (Jun 1)
GREENLET    HYDERABAD    MATLI      9    (Apr 3), (Dec 1), (Jul 1), (Jun 1), (Mar 1), (Nov 1), (Sep 1)

[irsmalik]

Dear Mr cactusdata

Im really thankful for your help.... now the output is as i desired. Thanks for your time and guidance. Hope for the same spirit in future if I need your knowledge base help.

Thanks & Regards
irsmalik

[irsmalik]

Dear Mr cactusdata

Your knowledge base help has reduced the number of records in my report. Before that, there were more than 200 records in my report. But now it almost 50 records and only one line / record can display complete history of any territory.
Thanks
irsmalik

[cactusdata]

You are welcome!
You could now mark the question as answered.

[NeoPa]

I doubt the OP's English is good enough for him to realise what you're asking for Cactus. I'll happily do it for you but I can't easily see which individual post is most applicable for Best Answer. Let me have the post # and I can set it. No problem.

[cactusdata]

Thanks! I think it would be #18

[NeoPa]

Done ;-)