Many to Many Database - Displaying query results as one entry with multiple categorie

[SAL6579]

I have a database that is based on having recipes, I have a Many to Many relationship set up. I have following:

RecipeTable:
RecipeID
RecipeName

RecipeCategoryTable:
RecipeID
CategoryID

CategoryTabe:
CategoryID
CategoryName

When I run a query of RecipeName I get multiple results, the name is still the same but each result will have a different category next to it.
For example:

Recipe Name
BBQ Pork
Category Name
Chinese

Recipe Name
BBQ Pork
Category Name
BBQ

Recipe Name
BBQ Pork
Category Name
Dinner

Is there a way to join these to display as one result as below?? Any help would be greatly appreciated. :)

Recipe Name
BBQ Pork
Category Name
Chinese, BBQ, Dinner

[PhilOfWalton]

It will require a bit of VBA which I will let you have this evening.
In the mean time, I think you need an additional field SortOrder in your RecipeCategory Table because if you want the result
Chinese, BBQ, Dinner
not
BBQ, Dinner, Chinese
we need to tell the program the order to print these out.
So those 3 fields need to be a unique key.

Phil

[PhilOfWalton]

I hope I get this right, as my version is using an almost identical layout but I have to change names to coincide with your tables.

First we must create a query.

SELECT Recipe.RecipeName, Recipe.RecipeID, Ccat(Recipe.RecipeID,[CategoryName]) AS InterestStg
FROM CategoryTable INNER JOIN (Recipe INNER JOIN RecipeCategoryTable ON Recipe.RecipeID = RecipeCategoryTable.RecipeID) ON Recipe.RecipeID = RecipeCategoryTable.RecipeID
GROUP BY Recipe.RecipeName, Recipe.RecipeID,Ccat(Recipe.RecipeID,[CategoryName])
ORDER BY RecipeName;
 

Now for the VBA. What we are doing here is to read all the categories that apply to each recipe, and concatenating them (adding them together) with a comma and space.

Function Ccat(ID As Long, StgToConcat As String)
 
    Dim MyDb As Database
    Dim Rst As Recordset
    Dim SQLStg As String
    Dim OutStg As String
 
   SELECT Recipe.RecipeName, Recipe.RecipeID
   FROM CategoryTable INNER JOIN (Recipe INNER JOIN
   RecipeCategoryTable ON Recipe.RecipeID = 
 
RecipeCategoryTable.RecipeID) ON Recipe.RecipeID = RecipeCategoryTable.RecipeID
GROUP BY Recipe.RecipeName, Recipe.RecipeID,Ccat(Recipe.RecipeID,[CategoryName])
ORDER BY RecipeName;
    Set MyDb = CurrentDb
    Set Rst = MyDb.OpenRecordset(SQLStg)
 
    With Rst
        Do Until .EOF
            OutStg = OutStg & !Interest & ", "          ' Agg the strings with a comma & space
            .MoveNext
        Loop
        .Close
        Set Rst = Nothing
        Ccat = Left(OutStg, Len(OutStg) - 2)            ' Remove last Comma space
 
    End With
 
End Function
 

[PhilOfWalton]

Whoops, sorry, for some reason the reply was posted before I had finished it.
Please ignore the previous VBA, I will try again.

Function Ccat(ID As Long, StgToConcat As String)
 
    Dim MyDb As Database
    Dim Rst As Recordset
    Dim SQLStg As String
    Dim OutStg As String
 
   SELECT Recipe.RecipeName, Recipe.RecipeID
   FROM CategoryTable INNER JOIN (Recipe INNER JOIN
   RecipeCategoryTable ON Recipe.RecipeID =  
   RecipeCategoryTable.RecipeID) ON Recipe.RecipeID = 
   RecipeCategoryTable.RecipeID
   ORDER BY SortOrder;
 
    Set MyDb = CurrentDb
    Set Rst = MyDb.OpenRecordset(SQLStg)
 
    With Rst
        Do Until .EOF
            OutStg = OutStg & !Category & ", "          ' Add the strings with a comma & space
            .MoveNext
        Loop
        .Close
        Set Rst = Nothing
        Ccat = Left(OutStg, Len(OutStg) - 2)            ' Remove last Comma space
 
    End With
 
End Function
 

Phil