Concatenation of categories - stuck

[C.D]

Hey,
I'm trying to find a way to combine categories in a many-to-many enviroment and make a single sentence, listing them up. I've looked around the MS web and googled, but got no solid results yet..


Here's the DB

Anime			
=====
AnimeID
AnimeTitle
		AnimeCategoryList
		=============
		CetegoryID
		CategoryName	AnimeCategory
					===========
					AnimeCategoryID
					AnimeID
					CategoryID

Each anime has several different categories

AnCatID	AnimeID	        CategoryID
9	Cowboy Bebop	Action
10	Cowboy Bebop	Future
11	Cowboy Bebop	Gunfights
12	Cowboy Bebop	Space Travel
13	Cowboy Bebop	SciFi
14	Cowboy Bebop	Violence
15	Elfen Lied	Action
16	Elfen Lied	Gore
17	Elfen Lied	Horror
18	Elfen Lied	Seinen
19	Elfen Lied	Violence
20	Fullmetal Alchemist	Action
21	Fullmetal Alchemist	Adventure
22	Fullmetal Alchemist	Fantasy
23	Fullmetal Alchemist	Gunfights
24	Fullmetal Alchemist	Magic
25	Fullmetal Alchemist	Shounen

Ultimately, the sum of 'Cowboy Bebop' would be: "Action, Future, Gunfights, Space Travel, Scifi, Violence"

Anything will do.. Even a resource link would be greatly appriciated

 

[Uncle Gizmo]

I'm not sure if it's quite what you are looking for but I would suggest you have a look at this Thread here:

 

[C.D]

I'm not sure if it's quite what you are looking for but I would suggest you have a look at this Thread here:

Thanks, that seems to be exactly what I'm after! - but I fail to rewrite the code, since I basically have no idea how
I understand some of logic in it, but that's all.. I would really appriciate you could explain how to rewrite it.

 

[Uncle Gizmo]

Basically what you need to do is replace the SQL in the example with your own SQL statement.

I would guess it would read something like this:
Select CategoryID from tblMyTable Where AnimeID = "Cowboy Bebop"

the best way is to create your own query with the query builder which just returns the records "Cowboy Bebop" and then switch to SQL mode of the query, and copy the SQL straight out from there.

 

[C.D]

Hi, thanks for replying. I've been doing some tests lately, and I'm pretty close.
By setting strSQL = "SELECT qryAnimeCategory.SjangerType FROM qryAnimeCategory", it lists up ALL records, so it looks like this:

ID Title Episodes Categories
1 Fullmetal Alchemist 51 Action, Action, Action, SciFi, Shounen, Future, Gunfights, Gunfights, Seinen, Space Travel, Gore
2 Cowboy Bebop 26 Action, Action, Action, SciFi, Shounen, Future, Gunfights, Gunfights, Seinen, Space Travel, Gore
3 Elfen Lied 0 Action, Action, Action, SciFi, Shounen, Future, Gunfights, Gunfights, Seinen, Space Travel, Gore
4 Hellsing 13 Action, Action, Action, SciFi, Shounen, Future, Gunfights, Gunfights, Seinen, Space Travel, Gore
7 Last Exile 0 Action, Action, Action, SciFi, Shounen, Future, Gunfights, Gunfights, Seinen, Space Travel, Gore

So, what I need is the WHERE - as you wrote in the previous post. However, I can't find the right sentence.
What's wrong with this sentence?
"SELECT qryAnimeCategory.CategoryType FROM qryAnimeCategory WHERE qryAnimeCategory.AnimeID = qryAnime.AnimeID"
The "qryAnime" is the query I'm using the function with. The WHERE should include the CategoryType only if the AnimeID is matched.. How do I do that?

 

[Uncle Gizmo]

Are you familiar with the query builder? Select from the menu bar along the top >>> insert >>>> query >>>> Design view

 

[C.D]

Yes, but I'm not sure where you are going with that.

 

[Uncle Gizmo]

How many "category" columns do you have?

 

[Uncle Gizmo]

Oh, I see... Basically you've got it working but it's returning to many categories? Why is it returning "Action" three times?

 

[Uncle Gizmo]

Could you post the database here?

 

[C.D]

Certainly!
When you look at "tblAnimeCategory", you may see that I need some sort of a WHERE expression to filter out those categories that don't match the anime.

Thanks alot for looking into this.

I had to split the database up with winrar..
Just change the extension of part01 to .EXE, and the others to .RAR, and launch the exe file.

 

[Uncle Gizmo]

New qryAnime:

SELECT tblAnimeCategory.AnimeCategoryID, tblAnimeCategory.AnimeID, tblAnimeCategory.CategoryID, tblCategoryList.CategoryType
FROM tblCategoryList INNER JOIN tblAnimeCategory ON tblCategoryList.SjangerID = tblAnimeCategory.CategoryID;

New Function fConListh :

Public Function fConListh(intAnimeID As Integer) As String
Dim DB As DAO.Database
Dim RS As DAO.Recordset
    
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String

Dim strText As String

strSQL1 = "SELECT AnimeID, CategoryID, CategoryType "
strSQL2 = "FROM tblCategoryList INNER JOIN tblAnimeCategory ON tblCategoryList.SjangerID = tblAnimeCategory.CategoryID "
strSQL3 = "WHERE (((tblAnimeCategory.AnimeID)="
'2
strSQL4 = "));"
'SELECT AnimeID, CategoryID, CategoryType
'FROM tblCategoryList INNER JOIN tblAnimeCategory ON tblCategoryList.SjangerID = tblAnimeCategory.CategoryID
'WHERE (((tblAnimeCategory.AnimeID)=
'2
'));"
strSQL = strSQL1 & strSQL2 & strSQL3 & intAnimeID & strSQL4
Set DB = CurrentDb

    'Open a Recordset and loop through it to fill the text box txtTest
    
    Set RS = DB.OpenRecordset(strSQL, dbOpenForwardOnly)
    Do Until RS.EOF
            If strText = "" Then 'This If statment prevents a  single comma at the begining of the text","
                strText = RS!CategoryType
                Else
                strText = strText & ", " & RS!CategoryType
            End If
        RS.MoveNext
    Loop
    
    RS.Close
    Set RS = Nothing
    Set DB = Nothing

Let fConListh = strText
End Function

 

[C.D]

That worked perfectly, thanks alot Gizmo!
It has much more code than I would've thought was needed, which proves I have much to learn..
Though I only understand about half of the code, I can see what it does much easier, since it's based on my own DB.
Thanks again, I have atleast a dozen places where I could use this code.

 

[Uncle Gizmo]

Glad to Help...

BTW Re: >>>> I had to split the database up with winrar.. <<<<<

There is an option you can select to compact your database, go to:
Tools >>>> Options >>>> General >>>> compact on close.

If you set that option and close your database you should notice a significant compression.

 

[Demring]

I have almost the exact same problem as C.D, but I dont understand where to put the code

And I cant dl the db to go look for it, because my computer says file no. 4 is infected by virus.

And so now I resort to asking directly for help. (I could just ask where to put the code, but there is a probability i wont know what to do next... Thats right, I am new to Access, and may in fact need baby-feeding.)

I have a qryRiskPlaces with two fields, Risk and Place. It looks like this:

Fire - Oslo
Fire - Bergen
Derail - Trondheim
Crash - Oslo
Crash - Trondheim

From this i want to generate a tblConcRiskPlaces like this:

Fire - Oslo, Bergen
Derail - Trondheim
Crash - Oslo, Trondheim

(This is of course a half nonsensical simplification. What Im really doing is making a db for preparedness-status for a railway system.)

I know that generation of a tbl like this is possible, because of this tutorial, which sadly for me concatenates two separate tbls, rendering me a -head.

This is the last important piece (for now at least) for my db puzzle. When I get it right, it will finally be possible to use! Im very excited about it, and I will probably become emotionally attached to anyone helping me a step further.

 

[C.D]

I have almost the exact same problem as C.D, but I dont understand where to put the code

And I cant dl the db to go look for it, because my computer says file no. 4 is infected by virus.

And so now I resort to asking directly for help. (I could just ask where to put the code, but there is a probability i wont know what to do next... Thats right, I am new to Access, and may in fact need baby-feeding.)

I have a qryRiskPlaces with two fields, Risk and Place. It looks like this:

Fire - Oslo
Fire - Bergen
Derail - Trondheim
Crash - Oslo
Crash - Trondheim

From this i want to generate a tblConcRiskPlaces like this:

Fire - Oslo, Bergen
Derail - Trondheim
Crash - Oslo, Trondheim

(This is of course a half nonsensical simplification. What Im really doing is making a db for preparedness-status for a railway system.)

I know that generation of a tbl like this is possible, because of this tutorial, which sadly for me concatenates two separate tbls, rendering me a -head.

This is the last important piece (for now at least) for my db puzzle. When I get it right, it will finally be possible to use! Im very excited about it, and I will probably become emotionally attached to anyone helping me a step further.

A slightly late reply from me here - but if you still need help, post a reply or send me a PM