[RESOLVED] [Access2000] Return multiple fields/categories in only one field/cell

[RS_Arm]

Hello guys.
I'm using an Access2000 db and I just can't change to another db to do this.

So this is my current problem:
I have a product table. Each product belongs to one or more category.

DB Diagram

Product		| Categories	| Prod_Cat
------------------------------------------
*ID_Prod	| *ID_Cat	| *ID_Cat
Desc		| Desc_Cat	| *ID_Prod

So, what I want to do is:
In only one , I want to show all categories that a product belongs.

This is what I'm getting now:

ID	|Desc	|Categories
--------------------------------
12	|UPS	|Power
12	|UPS	|Accessories
12	|UPS	|Obsolete

This is how I want it to be:

ID	|Desc	|Categories
-----------------------------------------------
12	|UPS	|[Power].[Accessories].[Obsolete]

I now denormalizing is not very correct but I need to do this due to integration/export.
Does Access SQL has any capabilities to do this, since it is some sort of limited?
If so, can anyone give me a tip?

Thank you

[RS_Arm]

If not, please tel me that it is not possible.
Thank you very much

[anhn]

If number of Categories is limited, you can do it with a Crosstab query as subquery (Query1) with ID_Prod and Desc as RowHeadins, Desc_Cat as ColumnHeading and First(Desc_Cat) as Value, then in the main query (Query2) use formula to concatenate string values of Categories.

Otherway is to define a function to concatenate all category names of the same product then use this function in the query but it will be slower to run.

[anhn]

Query1:
TRANSFORM First(Categories.Desc_Cat) AS FirstOfDesc_Cat
SELECT Product.ID_Prod, Product.Desc
FROM (Product INNER JOIN Prod_Cat ON Product.ID_Prod = Prod_Cat.ID_Prod)
INNER JOIN Categories ON Prod_Cat.ID_Cat = Categories.ID_Cat
GROUP BY Product.ID_Prod, Product.Desc
PIVOT Categories.Desc_Cat In ("Power","Accessories","Obsolete");

Query2:
SELECT Query1.ID_Prod, Query1.Desc,
"[" & Replace(Trim([Power] & " " & [Accessories] & " " & [Obsolete])," ","].[") & "]" AS Categories
FROM Query1;

[RS_Arm]

Thank you for your reply.

I doubt it will work because there are unlimited categories. For instance, right now, I have about 5000 categories (but tomorrow it can be 5100, the user can create and delete categories). Can I use the PIVOT method without knowing the exact content of categories from a product?

Thank you once again.

[anhn]

No, if with that many and unknown categories, you cannot use Crosstab (Pivot) query. Access allows only maximum of 255 columns/fields.
In this case, you have to write your own UDF (user defined function) such as:
Function CombineCategories(ThisIDProd) As String
In this function, use a query that joins Prod_Cat with Categories tables and filterd on passed ID_Prod.
SQL = "SELECT Categories.Desc_Cat FROM Prod_Cat INNER JOIN Categories
ON Prod_Cat.ID_Cat = Categories.ID_Cat WHERE Prod_Cat.ID_Prod = " & ThisIDProd
Open a recordset based on this SQL, traverse through recordset to grab all Categories and join them into a string.

Otherway may be quicker is to create a Static recordset at the beginning based on query:
SQL = "SELECT Categories.Desc_Cat FROM Prod_Cat INNER JOIN Categories
ON Prod_Cat.ID_Cat = Categories.ID_Cat"
Each time the function run, use recordset's Find method (ADO) (or FindFirst & FindNext for DAO) to find all Categories based on passed ID_Prod.

This is another easier solution with sophisticated thought: I developed this method myself 3 years ago when I wanted to have a function equivalent with Sum() function for String data type in Access but couldn't find a clue after searching everywhere. This kind of function existed in "Brio" (a powerful database for reporting that I used 7 years ago).

* Add another field in Product table, named it "AllCategories" with data type Memo.

* Create an update query, named it "qryUpdateAllCategories", based on SQL:

UPDATE (Product INNER JOIN Prod_Cat ON Product.ID_Prod = Prod_Cat.ID_Prod) 
INNER JOIN Categories ON Prod_Cat.ID_Cat = Categories.ID_Cat
SET Product.AllCategories = "[" & Categories.Desc_Cat & "] " & Product.AllCategories
WHERE (((InStr(Nz(Product.AllCategories),"[" & Categories.Desc_Cat & "]"))=0));

* Whenever needed (for reporting), run this query before open table Product to see AllCategories string of each product.

*** Noted that: In case Categories are allowed to be deleted, prior to run the above query, you have to run another update query to reset/clear the field AllCategories first.

[RS_Arm]

anhn, the last suggestion is very cool, but I just can't create a field on my database, i'm not allowd to do that.

I've used the recordset.find method.
It is running pretty good.

I really appreciate your attention.
Thank you very much for your advices.