Just need some help with how to find items NOT containing.... 1

[maxxev]

Hi, it maybe that I can work out how to do this but i'm honestly not sure where to begin, so i'm after some help with where to start looking...

The quandry is this:

I have a table that lists Ingredient and the allergens in them (e.g.)

Ingredient 1 | Allergen 1
Ingredient 1 | Allergen 2
Ingredient 2 | Allergen 1
Ingredient 2 | Allergen 3

I need to identify all the products that DO NOT contain an allergen and put it into a report.
I've used a concatenate function to list all the ones that DO contain it (e.g. all the products containing Allergen 1), but i'm not sure where to begin with the ones that DON'T....?

When I tried <>Allergen 1 of course the results included products that contain other allergens but ALSO contain Allergen 1.

Any suggestions on where to look for possible resolutions to this?, the only resolution I can think of now means making a query for every allergen (all 15 of them)... which i'd rather not do...

Thank you.

 

[dhookom]

How about:

SELECT DISTINCT Ingredient
FROM YourTable
WHERE Ingredient NOT IN (SELECT Ingredient FROM YourTable WHERE Allergen ="Peanuts")

Duane
Hook'D on Access
MS Access MVP

 

[maxxev]

That code works great on a Table, problem is i'm trying to use it on a Query and I keep getting "query is too complex"

 

[dhookom]

Your reply leaves us at a dead end. We can't see your query SQL and/or its name.

Duane
Hook'D on Access
MS Access MVP

 

[maxxev]

I'm just presuming at this point that this code isn't compatible with the complexity of the database i'm trying to work with...

Here is the query that is then queried by the query i'm trying to run this code on....

SELECT App_RMCognos_Qry.Customer, Active_FG_Qry.SkuCode, Active_FG_Qry.Desc, App_RMCognos_Qry.IngCode, T_AllergensInRM.AllergenID, App_SkuMatrix_Tbl.Unit
FROM App_SkuMatrix_Tbl INNER JOIN ((Active_FG_Qry INNER JOIN App_RMCognos_Qry ON Active_FG_Qry.SkuCode = App_RMCognos_Qry.SkuCode) INNER JOIN T_AllergensInRM ON App_RMCognos_Qry.IngCode = T_AllergensInRM.IngredientID) ON App_SkuMatrix_Tbl.SkuCode = App_RMCognos_Qry.SkuCode
WHERE (((App_RMCognos_Qry.Customer)<>"") AND ((Active_FG_Qry.SkuCode)<>"" And (Active_FG_Qry.SkuCode) Not Like "F.BMK*" And (Active_FG_Qry.SkuCode) Not Like "F.CSM*" And (Active_FG_Qry.SkuCode) Not Like "FC.*") AND ((App_RMCognos_Qry.Status)="Live"))
ORDER BY App_RMCognos_Qry.Customer, Active_FG_Qry.SkuCode, App_RMCognos_Qry.IngCode, T_AllergensInRM.AllergenID;

At the moment i'm trying to see if I can to the information I need in less stages...

Cheers for the help so far I will comeback once I have a simplified query to work on.

C

 

[dhookom]

The query you provided doesn't look too complex but it looks like there are at least two additional queries in the data sources.

Duane
Hook'D on Access
MS Access MVP

 

[maxxev]

Right, i've now built a really simple query that is based entirely on tables to be the source for this query and the Query now runs without an error message... however it doesn't appear to actually do anything... I get the egg timer for a few seconds then the database locks up as if the query is doing something but a min later and it still doesn't do anything, I have teo break to get out?

Here is the core Query:
Allergens_in_SKU_all_Qry

SELSELECT App_RMCognos_Tbl.SkuCode, T_AllergensInRM.AllergenID
FROM T_Allergen_Types INNER JOIN (T_AllergensInRM INNER JOIN App_RMCognos_Tbl ON T_AllergensInRM.IngredientID = App_RMCognos_Tbl.IngCode) ON T_Allergen_Types.AllergenID = T_AllergensInRM.AllergenID
GROUP BY App_RMCognos_Tbl.SkuCode, T_AllergensInRM.AllergenID;

And the code for the "not containing" query

SELECT DISTINCT Allergens_in_SKU_all_Qry.SkuCode
FROM Allergens_in_SKU_all_Qry
WHERE (((Allergens_in_SKU_all_Qry.SkuCode) Not In (SELECT SkuCode FROM Allergens_in_SKU_all_Qry WHERE AllergenID =5)));

Any thoughts please?

Cheers

 

[maxxev]

I had another thought.

Is it possible to write some code in a text box that will concatenate the "SkuCode" based on a "N" appearing in a Crosstab, where the column to check is the same as "Allergen"?

Cheers.

 

[dhookom]

It's difficult to understand without knowledge of the significant tables and fields. It would also help to understand how you want the data displayed.

There is an unmatched query wizard that might be of some help.

Duane
Hook'D on Access
MS Access MVP

 

[maxxev]

Hi, I think i've managed to make it work but I need to change the [Allergen] for the name of the actual allergen...

To try and explain.
The Crosstab columns are the names of the allergen (NUTS, EGG, SESAME, etc.).
I have a report that needs to list all the products at risk (on one page (it's an audit thing, it's no use to anyone but we have to do it)).

Therefore I would like to have the code below look at the column relating to the allergen the report is for (e.g. NUTS). The report already contains the allergen name in a text box called "ALLERGEN".

All I need to do now is replace the [Allergen] in the code below with the ACTUAL name in the "ALLERGEN" text box (e.g. "NUTS") whilst still have it be in [].

[codes]=Concatenate("SELECT [SkuCode] FROM [AllergenStage1_SKU_CrossQry] WHERE [Allergen] = 'N' ")[/code]

 

[maxxev]

E.g. something like this:

(([" & me.Allergen.value & "])<>"")

 

[dhookom]

Hopefully someone else understands your tables and what you are attempting to do and how you are attempting to get there.

Duane
Hook'D on Access
MS Access MVP

 

[maxxev]

I still can't make this work.

Here is the code in the text box that is working:

=Concatenate("SELECT [SkuCode] FROM [AllergenStage1_SKU_CrossQry] WHERE [NUTS] = 'N' ")

I need it to be somthing like this (with the bit in blue being the important change, a text box controlled by a Combo box):

=Concatenate("SELECT [SkuCode] FROM [AllergenStage1_SKU_CrossQry] WHERE [COLOR=blue][b]["& me.Allergentxt.Value &"][/b][/color] = 'N' ")

Anyone have any suggetsions?

 

[dhookom]

If your expression is typed in as a control source, you should try:

=Concatenate("SELECT [SkuCode] FROM [AllergenStage1_SKU_CrossQry] WHERE ["& [Allergentxt] & "] = 'N' ")

Duane
Hook'D on Access
MS Access MVP

 

[maxxev]

Your assumption and suggesion was correct thank you.

All sorted now .