By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,730 Members | 1,500 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,730 IT Pros & Developers. It's quick & easy.

Combine Months Name in One Row

P: 85
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:

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM DM
  3. GROUP BY DM.CName, DM.RName, DM.AreaName, DM.TDate, Format([TDate],"mmm")
  4. HAVING (((Sum(DM.DTP))>0))
  5. 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

Attached Images
File Type: jpg Report.jpg (55.9 KB, 141 views)
4 Weeks Ago #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. ' The source can also be an SQL Select string:
  2. '
  3. '   Select
  4. '       KeyField,
  5. '       DJoin("[ValueField]", "Select ValueField From SomeTable Order By SomeField", "[KeyField] = " & [KeyField] & "") As Values
  6. '   From
  7. '       Table
  8. '   Group By
  9. '       KeyField
That could turn out like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     DM.CName AS Company, 
  3.     DM.RName AS Region, 
  4.     DM.AreaName AS Territory, 
  5.     Sum(DM.DTP) AS TotalDTP, 
  6.     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
  7. FROM 
  8.     DM
  9. WHERE 
  10.     DJoin() Is Null
  11. GROUP BY 
  12.     DM.CName, 
  13.     DM.RName, 
  14.     DM.AreaName
  15. HAVING 
  16.    Sum(DM.DTP) > 0
  17. ORDER BY 
  18.    DM.CName, 
  19.    DM.RName, 
  20.    DM.AreaName;
Then, I guess, you could mark this as answered.

Share this Question
Share on Google+
27 Replies


P: 65
You can use my DJoin function in this query:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     DM.CName AS Company, 
  3.     DM.RName AS Region, 
  4.     DM.AreaName AS Territory, 
  5.     Sum(DM.DTP) AS TotalDTP, 
  6.     DJoin("StrConv(Format(TDate,'mmm'),3)","DM","",", ") AS Periods
  7. FROM 
  8.     DM
  9. GROUP BY 
  10.     DM.CName, 
  11.     DM.RName, 
  12.     DM.AreaName
  13. HAVING 
  14.     Sum(DM.DTP)>0
  15. ORDER BY 
  16.     DM.CName, 
  17.     DM.RName, 
  18.     DM.AreaName;
Output: See the attached file, please.

This is the function:

Expand|Select|Wrap|Line Numbers
  1. ' Returns the joined (concatenated) values from a field of records having the same key.
  2. ' The joined values are stored in a collection which speeds up browsing a query or form
  3. ' as all joined values will be retrieved once only from the table or query.
  4. ' Null values and zero-length strings are ignored.
  5. '
  6. ' If no values are found, Null is returned.
  7. '
  8. ' The default separator of the joined values is a space.
  9. ' Optionally, any other separator can be specified.
  10. '
  11. ' Syntax is held close to that of the native domain functions, DLookup, DCount, etc.
  12. '
  13. ' Typical usage in a select query using a table (or query) as source:
  14. '
  15. '   Select
  16. '       KeyField,
  17. '       DJoin("[ValueField]", "[Table]", "[KeyField] = " & [KeyField] & "") As Values
  18. '   From
  19. '       Table
  20. '   Group By
  21. '       KeyField
  22. '
  23. ' The source can also be an SQL Select string:
  24. '
  25. '   Select
  26. '       KeyField,
  27. '       DJoin("[ValueField]", "Select ValueField From SomeTable Order By SomeField", "[KeyField] = " & [KeyField] & "") As Values
  28. '   From
  29. '       Table
  30. '   Group By
  31. '       KeyField
  32. '
  33. ' To clear the collection (cache), call DJoin with no arguments:
  34. '
  35. '   DJoin
  36. '
  37. ' Requires:
  38. '   CollectValues
  39. '
  40. ' 2019-06-24, Cactus Data ApS, Gustav Brock
  41. '
  42. Public Function DJoin( _
  43.     Optional ByVal Expression As String, _
  44.     Optional ByVal Domain As String, _
  45.     Optional ByVal Criteria As String, _
  46.     Optional ByVal Delimiter As String = " ") _
  47.     As Variant
  48.  
  49.     ' Expected error codes to accept.
  50.     Const CannotAddKey      As Long = 457
  51.     Const CannotReadKey     As Long = 5
  52.     ' SQL.
  53.     Const SqlMask           As String = "Select {0} From {1} {2}"
  54.     Const SqlLead           As String = "Select "
  55.     Const SubMask           As String = "({0}) As T"
  56.     Const FilterMask        As String = "Where {0}"
  57.  
  58.     Static Values   As New Collection
  59.  
  60.     Dim Records     As DAO.Recordset
  61.     Dim Sql         As String
  62.     Dim SqlSub      As String
  63.     Dim Filter      As String
  64.     Dim Result      As Variant
  65.  
  66.     On Error GoTo Err_DJoin
  67.  
  68.     If Expression = "" Then
  69.         ' Erase the collection of keys.
  70.         Set Values = Nothing
  71.         Result = Null
  72.     Else
  73.         ' Get the values.
  74.         ' This will fail if the current criteria hasn't been added
  75.         ' leaving Result empty.
  76.         Result = Values.Item(Criteria)
  77.         '
  78.         If IsEmpty(Result) Then
  79.             ' The current criteria hasn't been added to the collection.
  80.             ' Build SQL to lookup values.
  81.             If InStr(1, LTrim(Domain), SqlLead, vbTextCompare) = 1 Then
  82.                 ' Domain is an SQL expression.
  83.                 SqlSub = Replace(SubMask, "{0}", Domain)
  84.             Else
  85.                 ' Domain is a table or query name.
  86.                 SqlSub = Domain
  87.             End If
  88.             If Trim(Criteria) <> "" Then
  89.                 ' Build Where clause.
  90.                 Filter = Replace(FilterMask, "{0}", Criteria)
  91.             End If
  92.             ' Build final SQL.
  93.             Sql = Replace(Replace(Replace(SqlMask, "{0}", Expression), "{1}", SqlSub), "{2}", Filter)
  94.  
  95.             ' Look up the values to join.
  96.             Set Records = CurrentDb.OpenRecordset(Sql, dbOpenSnapshot)
  97.             CollectValues Records, Delimiter, Result
  98.             ' Add the key and its joined values to the collection.
  99.             Values.Add Result, Criteria
  100.         End If
  101.     End If
  102.  
  103.     ' Return the joined values (or Null if none was found).
  104.     DJoin = Result
  105.  
  106. Exit_DJoin:
  107.     Exit Function
  108.  
  109. Err_DJoin:
  110.     Select Case Err
  111.         Case CannotAddKey
  112.             ' Key is present, thus cannot be added again.
  113.             Resume Next
  114.         Case CannotReadKey
  115.             ' Key is not present, thus cannot be read.
  116.             Resume Next
  117.         Case Else
  118.             ' Some other error. Ignore.
  119.             Resume Exit_DJoin
  120.     End Select
  121.  
  122. End Function
  123.  
  124. ' To be called from DJoin.
  125. '
  126. ' Joins the content of the first field of a recordset to one string
  127. ' with a space as delimiter or an optional delimiter, returned by
  128. ' reference in parameter Result.
  129. '
  130. ' 2019-06-11, Cactus Data ApS, Gustav Brock
  131. '
  132. Private Sub CollectValues( _
  133.     ByRef Records As DAO.Recordset, _
  134.     ByVal Delimiter As String, _
  135.     ByRef Result As Variant)
  136.  
  137.     Dim SubRecords  As DAO.Recordset
  138.  
  139.     Dim Value       As Variant
  140.  
  141.     If Records.RecordCount > 0 Then
  142.         While Not Records.EOF
  143.             Value = Records.Fields(0).Value
  144.             If Records.Fields(0).IsComplex Then
  145.                 ' Multi-value field (or attachment field).
  146.                 Set SubRecords = Records.Fields(0).Value
  147.                 CollectValues SubRecords, Delimiter, Result
  148.             ElseIf Nz(Value) = "" Then
  149.                 ' Ignore Null values and zero-length strings.
  150.             ElseIf IsEmpty(Result) Then
  151.                 ' First value found.
  152.                 Result = Value
  153.             Else
  154.                 ' Join subsequent values.
  155.                 Result = Result & Delimiter & Value
  156.             End If
  157.             Records.MoveNext
  158.         Wend
  159.     Else
  160.         ' No records found with the current criteria.
  161.         Result = Null
  162.     End If
  163.     Records.Close
  164.  
  165. End Sub
Code is also at GitHub: VBA.DJoin

Full documentation is here:
Join (concat) values from one field from a table or query
Attached Images
File Type: png DJoinDM.PNG (3.5 KB, 10 views)
4 Weeks Ago #2

P: 85
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
4 Weeks Ago #3

P: 65
You probably need to compile and save the module.
4 Weeks Ago #4

P: 85
Yes it is already done.. but the still giving error.

irsmalik
4 Weeks Ago #5

P: 65
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
4 Weeks Ago #6

P: 85
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
4 Weeks Ago #7

P: 65
Check your inbox, please.
4 Weeks Ago #8

P: 85
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
4 Weeks Ago #9

P: 85
Dear cactusdata

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

irsmalik
4 Weeks Ago #10

P: 65
Yes, reset the cache before running the query again:

Expand|Select|Wrap|Line Numbers
  1. ' To clear the collection (cache), call DJoin with no arguments:
  2. '
  3. '   DJoin
4 Weeks Ago #11

P: 85
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
4 Weeks Ago #12

P: 85
I mean where to above mentioned sentence....
4 Weeks Ago #13

P: 65
Just call DJoin in VBA:

Expand|Select|Wrap|Line Numbers
  1. DJoin
Or you can modify the query for an automatic reset of DJoin:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     DM.CName AS Company, 
  3.     DM.RName AS Region, 
  4.     DM.AreaName AS Territory, 
  5.     Sum(DM.DTP) AS TotalDTP, 
  6.     DJoin("StrConv(Format(TDate,'mmm'),3)","DM","",", ") AS Periods
  7. FROM 
  8.     DM
  9. WHERE
  10.     DJoin() Is Null
  11. GROUP BY 
  12.     DM.CName, 
  13.     DM.RName, 
  14.     DM.AreaName
  15. HAVING 
  16.     Sum(DM.DTP)>0
  17. ORDER BY 
  18.     DM.CName, 
  19.     DM.RName, 
  20.     DM.AreaName;
4 Weeks Ago #14

P: 65
> 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:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     DM.CName AS Company, 
  3.     DM.RName AS Region, 
  4.     DM.AreaName AS Territory, 
  5.     Sum(DM.DTP) AS TotalDTP, 
  6.     DJoin("StrConv(Format(TDate,'mmm'),3)","DM","[AreaName]='" & [Territory] & "'",", ") AS Periods
  7. FROM 
  8.     DM
  9. WHERE 
  10.     DJoin() Is Null
  11. GROUP BY 
  12.     DM.CName, 
  13.     DM.RName, 
  14.     DM.AreaName
  15. HAVING 
  16.     Sum(DM.DTP)>0
  17. ORDER BY 
  18.     DM.CName, 
  19.     DM.RName, 
  20.     DM.AreaName;
4 Weeks Ago #15

Rabbit
Expert Mod 10K+
P: 12,383
An alternative way to do this without VBA is to use a crosstab query
4 Weeks Ago #16

P: 85
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
3 Weeks Ago #17

P: 65
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:

Expand|Select|Wrap|Line Numbers
  1. ' The source can also be an SQL Select string:
  2. '
  3. '   Select
  4. '       KeyField,
  5. '       DJoin("[ValueField]", "Select ValueField From SomeTable Order By SomeField", "[KeyField] = " & [KeyField] & "") As Values
  6. '   From
  7. '       Table
  8. '   Group By
  9. '       KeyField
That could turn out like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     DM.CName AS Company, 
  3.     DM.RName AS Region, 
  4.     DM.AreaName AS Territory, 
  5.     Sum(DM.DTP) AS TotalDTP, 
  6.     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
  7. FROM 
  8.     DM
  9. WHERE 
  10.     DJoin() Is Null
  11. GROUP BY 
  12.     DM.CName, 
  13.     DM.RName, 
  14.     DM.AreaName
  15. HAVING 
  16.    Sum(DM.DTP) > 0
  17. ORDER BY 
  18.    DM.CName, 
  19.    DM.RName, 
  20.    DM.AreaName;
Then, I guess, you could mark this as answered.
3 Weeks Ago #18

P: 85
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
3 Weeks Ago #19

P: 65
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.
3 Weeks Ago #20

P: 85
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
2 Weeks Ago #21

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

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     DM.CName AS Company, 
  3.     DM.RName AS Region, 
  4.     DM.AreaName AS Territory, 
  5.     Sum(DM.DTP) AS TotalDTP, 
  6.     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
  7. FROM 
  8.     DM
  9. WHERE 
  10.     DJoin() Is Null
  11. GROUP BY 
  12.     DM.CName, 
  13.     DM.RName, 
  14.     DM.AreaName
  15. HAVING 
  16.    Sum(DM.DTP) > 0
  17. ORDER BY 
  18.    DM.CName, 
  19.    DM.RName, 
  20.    DM.AreaName;
Output will be similar to:

Expand|Select|Wrap|Line Numbers
  1. Company    Region    Territory    TotalDTP    Periods
  2. GREENLET    HYDERABAD    AGAPURA    2    (Jul 1), (Jun 1)
  3. GREENLET    HYDERABAD    MATLI      9    (Apr 3), (Dec 1), (Jul 1), (Jun 1), (Mar 1), (Nov 1), (Sep 1)
2 Weeks Ago #22

P: 85
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
2 Weeks Ago #23

P: 85
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
2 Weeks Ago #24

P: 65
You are welcome!
You could now mark the question as answered.
2 Weeks Ago #25

NeoPa
Expert Mod 15k+
P: 31,606
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.
2 Weeks Ago #26

P: 65
Thanks! I think it would be #18
2 Weeks Ago #27

NeoPa
Expert Mod 15k+
P: 31,606
Done ;-)
2 Weeks Ago #28

Post your reply

Sign in to post your reply or Sign up for a free account.