Crosstab query - replace values returned

[maxxev]

Hi, I have a cross tab query which is displaying ingredients on rows, allergens on columns and then counting the ingredients containing the allergens in the cells.

So basically is is saying for x ingredient it contains x ingredients of allergen type x.

For the sake of allowing users to filter a list created by the crosstab i'd like for them to simply be able to say, Y or N (i.e. show ingredients containing Gluten Y/N).

So is there a way within the crosstab query to replace any value of "1" or above with a Y and every null value with a N?

Cheers

 

[PHV]

What is the actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[maxxev]

There is a whole load of stuff in the below to do with the list I hope to create, the relevent column is the : "Ingredient" value that is currently set to "count" as the totalling option.

This is the column that I would like to either return Y or N

Please note my programming knowledge is rather basic.

Thank you.

TRANSFORM Count([QAllergens in spec (combined)].Ingredient) AS CountOfIngredient
SELECT tblIng_Spec_Detail.[Ingredient Catagory], tblIngredients_Table.RM_Ingredient_Code AS [RM code], tblIng_Spec_Detail.[PD code], tblIng_Spec_Detail.[Supplier Ing Name], tblIng_Spec_Detail.[Internally Approved?], [QAllergens in spec (combined)].Ing_specID
FROM tblIngredients_Table RIGHT JOIN (([QAllergens in spec (combined)] RIGHT JOIN tblIng_Spec_Detail ON [QAllergens in spec (combined)].Ing_specID = tblIng_Spec_Detail.Ing_specID) LEFT JOIN tblIng_Spec_in_use ON tblIng_Spec_Detail.Ing_specID = tblIng_Spec_in_use.Ing_specID) ON tblIngredients_Table.Ing_codeID = tblIng_Spec_in_use.Ing_codeID
GROUP BY tblIng_Spec_Detail.[Ingredient Catagory], tblIngredients_Table.RM_Ingredient_Code, tblIng_Spec_Detail.[PD code], tblIng_Spec_Detail.[Supplier Ing Name], tblIng_Spec_Detail.[Internally Approved?], [QAllergens in spec (combined)].Ing_specID
PIVOT [QAllergens in spec (combined)].[Allergen Type];

 

[PHV]

Replace this:
TRANSFORM Count([QAllergens in spec (combined)].Ingredient) AS CountOfIngredient
with this:
TRANSFORM IIf(Count([QAllergens in spec (combined)].Ingredient)>0,'Y','N') AS IsContaining

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[maxxev]

Excellent thank you

Seems to work great.