Filtered Colum To Single Comma Delimited Value??

[kchatel]

Is it possible in Access to do the following.
I filtered my table to have the folowing values

ProdID Color
------------------------
2 Black
2 Red
2 Green

Now is it possible to create a view that only displaces 1 unique row
with the color column as comma separater like the following
ProdID Color
2 Black, Red, Green

I need help on this. Anything would be appreciated.
Thank
K

[CDMAPoster]

kchatel wrote:

Is it possible in Access to do the following.
I filtered my table to have the folowing values

ProdID Color
------------------------
2 Black
2 Red
2 Green

Now is it possible to create a view that only displaces 1 unique row
with the color column as comma separater like the following
ProdID Color
2 Black, Red, Green

I need help on this. Anything would be appreciated.
Thank
K

I'll give a brute force example that seems to work using DAO and should
suffice until others suggest more elegant methods.

tblColors
ColorID AutoNumber PK
ColorName Text
ColorID ColorName
1 Black
2 Red
3 Green
4 Blue
5 Yellow

tblProducts
ProductID AutoNumber PK
ProductName Text
SKU Text (Indexed, No Duplicates)
ProductID ProductName SKU
1 Product1 SKUC
2 Product2 SKUB
3 Product3 SKUA

tblProductColors
PCID AutoNumber PK
ProductID Long FK
ColorID Long FK
SortOrder Long
PCID ProductID ColorID SortOrder
1 2 1 1
2 2 2 2
3 2 3 3
4 3 5 2
5 3 4 1

'-----Begin Module Code
'These are my Module Options:
'Option Compare Database
'Option Explicit

Public Function GetProductColors(lngProductID As Long) As String
Dim MyDB As DAO.Database
Dim ColorRS As DAO.Recordset
Dim strSQL As String
Dim strTemp As String
Dim lngColorCount As Long
Dim lngI As Long

strTemp = ""
strSQL = "SELECT ColorName FROM (tblColors INNER JOIN tblProductColors
ON tblColors.ColorID = tblProductColors.ColorID) INNER JOIN tblProducts
ON tblProductColors.ProductID = tblProducts.ProductID WHERE
tblProductColors.ProductID = " & CStr(lngProductID) & " AND ColorName
IS NOT NULL ORDER BY tblProductColors.ProductID,
tblProductColors.SortOrder;"
Set MyDB = CurrentDb
Set ColorRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If ColorRS.RecordCount > 0 Then
ColorRS.MoveLast
lngColorCount = ColorRS.RecordCount
ColorRS.MoveFirst
For lngI = 1 To lngColorCount
If strTemp = "" Then
strTemp = ColorRS("ColorName")
Else
strTemp = strTemp & ", " & ColorRS("ColorName")
End If
If lngI <> lngColorCount Then ColorRS.MoveNext
Next lngI
End If
ColorRS.Close
Set ColorRS = Nothing
Set MyDB = Nothing
GetProductColors = strTemp
End Function
'-----End Module Code

qryShowProductColors:
SELECT ProductName, SKU, GetProductColors(ProductID) AS Colors FROM
tblProducts ORDER BY ProductName;

!qryShowProductColors:
ProductName SKU Colors
Product1 SKUC NullString
Product2 SKUB Black, Red, Green
Product3 SKUA Blue, Yellow

Use the Variant type instead of the String type if you want the
GetProductColors function to be able to return a Null value. You can
include ProductID in qryShowProductColors if you need it.

I hope this helps,

James A. Fortune
CD********@FortuneJames.com