Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #10577 > unrolled thread

Concatenate Thru Join Table (?)

Back to article view | Back to comp.databases.ms-access

Contents

  Concatenate Thru Join Table (?) croy <croy@invalid.net> - 2015-04-06 12:03 -0700
    Re: Concatenate Thru Join Table (?) "Phil" <phil@stantonfamily.co.uk> - 2015-04-06 22:04 +0100
    Re: Concatenate Thru Join Table (?) Cindy Krist <cindy@smbpartners.com> - 2015-04-07 06:49 -0700
      Re: Concatenate Thru Join Table (?) croy <croy@invalid.net> - 2015-04-08 06:10 -0700
        Re: Concatenate Thru Join Table (?) "Phil" <phil@stantonfamily.co.uk> - 2015-04-08 18:21 +0100
    Re: Concatenate Thru Join Table (?) "Phil" <phil@stantonfamily.co.uk> - 2015-04-07 14:54 +0100

#10577 — Concatenate Thru Join Table (?)

Three tables, one is a joiner.

tblx
	tblxId

tblJoin
	tblxId
	tblzId

tblz
	tblzId
	ItemName

Goal:  a query that outputs one row for each tblxId and
tblyId combination, and concatenates the ItemName(s),
separated by commas (if there are more than one).  I've been
trying to go off of Hookum's simpler example, but just can't
seem to get it working.  As soon as I pull tblJoin into the
query, I get multiple rows for each tblx record.

-- 
croy

[toc] | [next] | [standalone]

#10579

On 06/04/2015 20:03:24, croy wrote:
> Three tables, one is a joiner.
> 
> tblx
> tblxId
> 
> tblJoin
> tblxId
> tblzId
> 
> tblz
> tblzId
> ItemName
> 
> Goal:  a query that outputs one row for each tblxId and
> tblyId combination, and concatenates the ItemName(s),
> separated by commas (if there are more than one).  I've been
> trying to go off of Hookum's simpler example, but just can't
> seem to get it working.  As soon as I pull tblJoin into the
> query, I get multiple rows for each tblx record.
> 
Firstly, I think your question is wrong as I assume there is only one
ItemName for each TblZID Therefor I think you want a row for each TblXID and
it's corresponding concatenated ItemNames. To the best of my knowledge, this
can only be done using VBA to read the table and add a comma space after each
ItemName.  At the end of the loop remove the final comma space

Phil

---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com

[toc] | [prev] | [next] | [standalone]

#10580

I had to do this for a client.  tblx has a field, named ConcatItem that I update.  I tried not adding the field, but doing all via queries and code, but the db response time was too slow.

You need to call the following function for each record you want concatenate.
------------------------------------------------------------------

Function fConcatType(lngID As Long) As String
...
  Dim strConcat as string
  strConcat = ""
...
  strSQL = "SELECT ItemName FROM tblx INNER JOIN tblz ON tblx.tblxID = tblzID.tblzID WHERE tblxID = " & lngID

  Set rs = db.OpenRecordset(strSQL)
  
  rs.movelast     'to get RecordCount
 
  With rs
    If .RecordCount > 0 Then
       .MoveFirst
       Do While Not .EOF
           strConcat = strConcat & !ItemName & ", "
           .MoveNext
       Loop
    End If
  End With
    
  If strConcat <> "" Then
     fConcatType = Left(strConcat, Len(strConcat) - 2)    'strip off trailing comma
  End If
...
End Function

------------------------------------------------------------------

Calling Function:

Public Function UpdateAllConcats
...
   Dim strSQL as String

   strSQL "UPDATE tblx SET ConcatItem = ''"         'Start clean
   CurrentDb.Execute strSQL, dbFailOnError

   strSQL = "SELECT tblxID, ConcatItem FROM tblx"

   Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

   rs.MoveFirst

   While Not rs.EOF
      rs.Edit
         rs!ConcatItem = fConcatItem(rs!tblxID)
      rs.Update
      rs.MoveNext
   Wend
...
End Function

Hope this helps
   

[toc] | [prev] | [next] | [standalone]

#10590

On Tue, 7 Apr 2015 06:49:42 -0700 (PDT), Cindy Krist
<cindy@smbpartners.com> wrote:

>I had to do this for a client.  tblx has a field, named ConcatItem that I update.  I tried not adding the field, but doing all via queries and code, but the db response time was too slow.


Thanks for the very nice response.  I'll give it a try, but
it's really way above my skill level.

-- 
croy

[toc] | [prev] | [next] | [standalone]

#10592

On 08/04/2015 14:10:10, croy wrote:
> On Tue, 7 Apr 2015 06:49:42 -0700 (PDT), Cindy Krist
> <cindy@smbpartners.com> wrote:
> 
>>I had to do this for a client.  tblx has a field, 
named ConcatItem that I update.  
I tried not adding the field, but doing all via queries and code, 
but the db response time was too slow.
> 
> 
> Thanks for the very nice response.  I'll give it a try, but
> it's really way above my skill level.
> 

If you get stuck, come back, but is sounds as if you've tried 
the method I would have used.

Give some details of the code and I will see if I can help.

Phil

---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com

[toc] | [prev] | [next] | [standalone]

#10582

On 06/04/2015 20:03:24, croy wrote:
> Three tables, one is a joiner.
> 
> tblx
> tblxId
> 
> tblJoin
> tblxId
> tblzId
> 
> tblz
> tblzId
> ItemName
> 
> Goal:  a query that outputs one row for each tblxId and
> tblyId combination, and concatenates the ItemName(s),
> separated by commas (if there are more than one).  I've been
> trying to go off of Hookum's simpler example, but just can't
> seem to get it working.  As soon as I pull tblJoin into the
> query, I get multiple rows for each tblx record.
> 
Firstly, I think your question is wrong as I assume there is only one
ItemName for each TblZID Therefor I think you want a row for each TblXID and
it's corresponding concatenated ItemNames. To the best of my knowledge, this
can only be done using VBA to read the table and add a comma space after each
ItemName.  At the end of the loop remove the final comma space

Phil

---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web