Aggragate (?) help

[Poohbear0471]

I need assistance with a query, most likely because I'm not sure of the correct terminology to search for.


I need to select tblDispute.DisputeID and tblDisputeDetail.LnSeq as a single row of results. The tables are connected correctly. My query produces the results I expect, but I need a single row per tblDispute.DisputeID.



SQL:
SELECT TblACDV.ACDVTaskID, tblACDVDetail.ACDVDetailID, tblACDVDetail.ACDVLNSeq


FROM TblACDV INNER JOIN tblACDVDetail ON TblACDV.[ACDVTaskID] = tblACDVDetail.[ACDVTaskID];

TIA!

Ed

[orange]

It seems your 2 tables are related 1 to Many
TblACDV-->> tblACDVDetail

You said the tables were tblDispute.DisputeID and tblDisputeDetail.LnSeq , but your sql doesn't show those tables????

[Poohbear0471]

Yes, I should not try to type during meetings, especially on Fridays.


The correct table names are tblACDV and tblACDVDetail, connected on field ACDVTaskID. You are correct about the one to many relationship.
I need to display each TaskID from tblACDV with all ACDVLnSeq from tblACDVDetail.

So, instead of this:



I need a comma separated list like.


Values do not need to be sorted.

[orange]

Check out Allen Browne's ConcatRelated function.
Good luck.

[orange]

Mockup
using table

ID	TaskId	LNSeq
1	34	12
2	34	2
3	34	3
4	34	14
5	34	96
6	34	13
7	40	6
8	40	87
9	40	19

SELECT DISTINCT tbldemoConcat.Taskid
, Concatrelated("LNSEQ","tbldemoConcat","taskid=" & taskid  ) AS ConcatValues
FROM tbldemoconcat;

Taskid	ConcatValues
34	12 , 2 , 3 , 14 , 96 , 13
40	6 , 87 , 19

Good luck with your project.

[Poohbear0471]

Thank you so much!

[orange]

You are welcome. Happy to help.

[Poohbear0471]

Since the ConcatRelated code was written in 2008, there isn't anything different about adding the module to Access 2010, is there? I copy & pasted the code, compiled everything, and it shows up in my list of modules. If I try to use it in a query, I get the error "Undefined function 'ConcatRelated' in expression". I've double and triple checked to make sure it's spelled correctly in both places.
I deleted the module and added it again, compiled, Compact & Repair... Closed the database and went back in again... It's still giving me the same error.

[orange]

I am not aware of anything that has changed.

Here is a copy of the ConcatRelated function I used in my sample that I have been using for years.
Originally 2003, then 2010 and now O365.

'---------------------------------------------------------------------------------------
' Procedure : ConcatRelated
' Author    : AllenBrowne
' Date      : 12-11-2011
' Purpose   :
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Public Function ConcatRelated(strField As String, _
    strTable As String, _
    Optional strWhere As String, _
    Optional strOrderBy As String, _
    Optional strSeparator = " , ") As Variant  '  was " , "
10    On Error GoTo Err_Handler
          'Purpose:   Generate a concatenated string of related records.
          'Return:    String variant, or Null if no matches.
          'Arguments: strField = name of field to get results from and concatenate.
          '           strTable = name of a table or query.
          '           strWhere = WHERE clause to choose the right values.
          '           strOrderBy = ORDER BY clause, for sorting the values.
          '           strSeparator = characters to use between the concatenated values.
          'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
          '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
          '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
          '           4. Returning more than 255 characters to a recordset triggers this Access bug:
          '               http://allenbrowne.com/bug-16.html
          Dim rs As DAO.Recordset         'Related records
          Dim rsMV As DAO.Recordset       'Multi-valued field recordset
          Dim strSQL As String            'SQL statement
          Dim strOut As String            'Output string to concatenate to.
          Dim lngLen As Long              'Length of string.
          Dim bIsMultiValue As Boolean    'Flag if strField is a multi-valued field.
          
          'Initialize to Null
20        ConcatRelated = Null
          
          'Build SQL string, and get the records.
30        strSQL = "SELECT  " & strField & " FROM " & strTable   'distinct jed@@@@@
40        If strWhere <> vbNullString Then
50            strSQL = strSQL & " WHERE " & strWhere
60        End If
70        If strOrderBy <> vbNullString Then
80            strSQL = strSQL & " ORDER BY " & strOrderBy
90        End If
100       Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
          'Determine if the requested field is multi-valued (Type is above 100.)
110       bIsMultiValue = (rs(0).Type > 100)
          
          'Loop through the matching records
120       Do While Not rs.EOF
130           If bIsMultiValue Then
                  'For multi-valued field, loop through the values
140               Set rsMV = rs(0).Value
150               Do While Not rsMV.EOF
160                   If Not IsNull(rsMV(0)) Then
170                       strOut = strOut & rsMV(0) & strSeparator
                          
180                   End If
190                   rsMV.MoveNext
200               Loop
210               Set rsMV = Nothing
220           ElseIf Not IsNull(rs(0)) Then
230               strOut = strOut & rs(0) & strSeparator
240           End If
250           rs.MoveNext
260       Loop
270       rs.Close
          
          'Return the string without the trailing separator.
280       lngLen = Len(strOut) - Len(strSeparator)
290       If lngLen > 0 Then
300           ConcatRelated = Left(strOut, lngLen)
310       End If

Exit_Handler:
          'Clean up
320       Set rsMV = Nothing
330       Set rs = Nothing
340       Exit Function

Err_Handler:
350       MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
360       Resume Exit_Handler
End Function

[Poohbear0471]

Yep. Line for line the same. Must be something on my end.

[isladogs]

The SQL appears to have nothing to do with the two tables/fields you want.
If you are getting repeated results in your query output, try changing the query properties to unique values =yes,
The SQL will start with SELECT DISTINCT ….

[Poohbear0471]

I found my problem and it's working correctly. I had the module named incorrectly. Thank you very much for your help!

[orange]

Glad you have it resolved.