Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...I also believe that we all can contribute to each other's growth by sharing knowlege and experiences. I would love to take my skills and help people around the world solve problems..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft: Access Queries and JET SQL Forum

Just need some help with how to find items NOT containing....Helpful Member! 

thread701-1689342
Read More Threads Like This One
maxxev (TechnicalUser)
27 Jul 12 7:22
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 (Programmer)
27 Jul 12 8:40
How about:

CODE --> sql

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 (TechnicalUser)
27 Jul 12 9:06
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 (Programmer)
27 Jul 12 10:39
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 (TechnicalUser)
27 Jul 12 10:46
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....

CODE

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 (Programmer)
27 Jul 12 11:22
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 (TechnicalUser)
30 Jul 12 5:08
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

CODE

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

CODE

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 (TechnicalUser)
30 Jul 12 6:23
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 (Programmer)
30 Jul 12 9:33
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 (TechnicalUser)
30 Jul 12 11:46
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 (TechnicalUser)
30 Jul 12 11:58
E.g. something like this:

CODE

(([" & me.Allergen.value & "])<>"")
dhookom (Programmer)
30 Jul 12 12:26
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 (TechnicalUser)
1 Aug 12 12:01
I still can't make this work.

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

CODE

=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):

CODE

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

Anyone have any suggetsions?
Helpful Member!  dhookom (Programmer)
1 Aug 12 12:43
If your expression is typed in as a control source, you should try:

CODE --> ControlSource

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

Duane
Hook'D on Access
MS Access MVP

maxxev (TechnicalUser)
2 Aug 12 8:59
Your assumption and suggesion was correct :) thank you.

All sorted now :).
Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Queries and JET SQL

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close