Prev: Changing recordsource.. query in comboboxes
Next: update query
From: KARL DEWEY on 25 Mar 2010 10:29
>>Is there a way to get one concise answer that will state all present
allergens?
Search on Concatenate by Duane Hookom.
If that does not help then post back what your desired output would look like.

--
Build a little, test a little.


"tasha" wrote:

> I'm trying to design an allergy statement query. Some of our products have 4
> different batters so I made 4 separate queries using this statement:
> SELECT [tblComponent_Table (Raw_material_BOM)].lngzSKU, [tblComponent_Table
> (Raw_material_BOM)].chr1st_Batter, [tblComponent_Table
> (Raw_material_BOM)].chr1st_Batter_Name, tblIngredients.[blnNon-Allergens],
> tblIngredients.blnWheat, tblIngredients.blnMilk, tblIngredients.blnEgg,
> tblIngredients.blnSoy, tblIngredients.[blnCrustacean(ing)],
> tblIngredients.blnTree_Nuts
> FROM tblIngredients INNER JOIN [tblComponent_Table (Raw_material_BOM)] ON
> tblIngredients.chrBatter = [tblComponent_Table
> (Raw_material_BOM)].chr1st_Batter;
>
> I then did a union query which works great except when two or more
> ingredients have different allergen statements. Say one has wheat and
> another has wheat AND milk (or one has wheat and another has soy), but they
> are both being used in the same product. Is there a way to get one concise
> answer that will state all present allergens?
>
From: KARL DEWEY on 25 Mar 2010 16:14
I repeat -- what your desired output would look like.

--
Build a little, test a little.


"tasha" wrote:

> Thanks Karl. I realize that I was trying to get a quick & easy answer when
> it may be more complicated. Let me try this question again.
>
> I have 4 individual queries set up to give a Yes/No result using these fields:
> tblComponents.lngzSKU, tblComponents.chr(some batter #),
> tblComponents.chr(some batter #)_Name, tblIngredients.blnNon-Allergen,
> tblIngredients.blnWheat, blnMilk, blnEgg, blnSoy, blnCrustacean(ing),
> blnTree_Nuts
>
> Then I created a union query to do the same thing but without the
> tblComponents.chr(some batter #) and tblComponents.chr(some batter #)_Name
> fields. However, it is only searching for distinct allergen statements.
> (ex: 38505=Non-Allergen as true and 38505=Wheat,Milk as true or
> 410143=Wheat,Milk,Egg,Soy as true and 410143=Wheat,Milk as true and
> 410143=Wheat as true)
>
> What I want is one statement per SKU that includes all allergens except
> Non-Allergens unless it is the only allergen for that SKU.
 | 
Pages: 1
Prev: Changing recordsource.. query in comboboxes
Next: update query