Total query which concatenates text values

  • Thread starter Thread starter Larry
  • Start date Start date
L

Larry

This is what I would call a parts list query. The table
queried looks like:

Ref.Des. Part No. Value
-------- -------- -----
R1 ABC 50
R2 ABD 100
R3 ABC 200
R4 ABC 50

Note that there is a duplicate combination: ABC 50, which
occurs for both Ref. Des. entries R1 and R4.

The total query I am after would group the Part No. and
Value fields, and concatenate all the Ref. Des. entries
that are found in a unique group on those fields. Thus
the output would be:

Ref.Des. Part No. Value
-------- -------- -----
R1, R4 ABC 50
R2 ABD 100
R3 ABC 200

If, in the total query, I select:

Ref.Des. Part No. Value
-------- -------- -----

COUNT GROUP BY GROUP BY

Then I get the output:

Count Ref.Des. Part No. Value
-------------- -------- -----
2 ABC 50
1 ABD 100
1 ABC 200

I need to know how go get from this to the list of Ref.
Des. entries, in addition to or instead of the COUNT.

Larry
 
Hi Larry,

You could use a function to concatenate,
but I would use Michel's method of using a Temp
table.

Here might be one way using DAO (be sure reference to DAO
library is set). I don't like periods in field names, plus "Value"
is a reserved word, so assumed a table "Parts" with your
field names changed, and defined a table Temp as below.

Private Sub cmdConcat_Click()
On Error GoTo Err_cmdConcat_Click

Dim strSQL As String
Dim db As DAO.Database

Set db = CurrentDb

'clear table Temp
' Fields:
' ConcatRefDes Text(255) or Memo?
' PartNo Text(50)
' PartValue Long
db.Execute "DELETE * FROM Temp", dbFailOnError

'fill Temp with distinct fields
'from table Parts
' Fields:
' RefDes Text(10)
' PartNo Text(50)
' PartValue Long
strSQL = "INSERT INTO Temp (PartNo,PartValue) " _
& "SELECT DISTINCT PartNo, PartValue FROM Parts"
db.Execute strSQL, dbFailOnError

strSQL = "UPDATE Temp As T INNER JOIN Parts As P " _
& "ON T.PartNo = P.PartNo AND T.PartValue = P.PartValue " _
& "SET T.ConcatRefDes = (T.ConcatRefDes + ',') & P.RefDes"
db.Execute strSQL, dbFailOnError

MsgBox "Done concatenating RefDes's into table Temp."

'open report or form based on table Temp

db.Close

Exit_cmdConcat_Click:
Set db = Nothing
Exit Sub

Err_cmdConcat_Click:
MsgBox Err.Description
Resume Exit_cmdConcat_Click

End Sub

Please respond back if I have misunderstood,
or something was not clear.

Good luck,

Gary Walter
 
Hi Gary,

Thanks for the recommendations.

I am a novice ACCESS user, and have done no programming in
using ACCESS, so it will take me some time to digest what
you suggest. At this point I believe, at least, that you
are addressing the right problem. I was hopeful, of
course, that the concatenation I need would be somehow
about as simple as asking for "COUNT" or "SUM", using
the "EXPRESSION" format, in a simple Total Query where all
but the one field are "Group By" fields.

Larry
 
Back
Top