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
CDMAPoster@FortuneJames.com