Prob with MSAccess query using 'AND' operator

• • Reply

  13 years ago

  by EMJLD02

  Joanne Coates

  , United Kingdom

  Joined 13 years ago

  Im abit stuck on a query using 'AND' operator... and wondered if someone could point out where Im going wrong.

  I have a table that looks abit like this (Id is a counter field) as other fields are not unique:-

  Id         Transaction         Profile

               DOG                      X

               CAT                       X

               FROG                    X

               DOG                      Y

               CAT                       Y

               FROG                     Y

               DOG                       Z

               CAT                        Z

   

  I want to do a query to bring me out the results of all profiles which contain specific Transactions and only ones that contain all the ‘Transactions’ I specify.

   

  I thought this was simple (ha ha) and I could do this using the AND operator ie. 

   

  SELECT Table1.counter, Table1.Transaction, Table1.Profile

  FROM Table1

  WHERE (((Table1.Transaction)="dog" And (Table1.Transaction)="cat"));

   

  Unfortunately I get no results for this?  Any ideas anybody?    

   

  Am I wasting my time doing it this way via SQL - should I be trying to write some VBA to get these results do you think?

  Appreciate any help anyone could give us... have got brain ache from looking at it now!!

  Thanks!

   

           

   

  Report abuse
• • Reply

  13 years ago

  by addi82

  Aditya Singh

  Lucknow, India

  Joined 14 years ago

  Dear Joanne,

  I think your query syntax is correct. but the outcome of your query (transaction & profilev fields)  is wrong and what is the use of operator AND here.

  SELECT Table1.counter, Table1.Transaction, Table1.Profile

  FROM Table1

  WHERE (((Table1.Transaction)="dog" And (Table1.Transaction)="cat"));

   Redefine your query again for good results.

  Regards,

  Aditya Singh

  Report abuse
• • Reply

  13 years ago

  by EMJLD02

  Joanne Coates

  , United Kingdom

  Joined 13 years ago

  Thanks for the response, but it hasn't really helped as I didn't really understand.

  The only field I really need in results is 'Profile', I left the others in so I could see whether result is correct.   Im expecting to see X,Y & Z as DOG and CAT are common elements to these profiles - which is what Im aiming for. 

  The reason for using AND was because OR wouldn't suit my purposes as I want results containing all the Transactions I specify.  

  I don't understand why if I specify AND NOT in a query on a field (for it to exclude specifics) it works but when I use AND (to include specifics) it doesn't work?

  Think the error is probably my logic?

  Maybe Im looking at this too simply.

  Report abuse
• • Reply

  13 years ago

  by UncleDave

  Dave Fisher

  , United Kingdom

  Joined 13 years ago

  You are asking Access to select records where transaction is dog AND cat, so yes the problem is in your logic, transaction holds either cat OR dog.

  There are a number of ways to write the WHERE clause to bring back both

  The commonest two would be

  WHERE Transaction = "dog" OR Transaction = "cat"

  WHERE Transaction IN ("dog", "cat")

   

  I prefer the second option as it, IMHO, is simpler to read,

   

   

  Report abuse
• • Reply

  13 years ago

  by EMJLD02

  Joanne Coates

  , United Kingdom

  Joined 13 years ago

  Thanks for the advice but I don't want to use OR in my query as Im after specific results. I want the query to only give me results where the profile contains both/all the specific criteria I ask it for, in this case DOG And CAT.  I want... either a true or false result. Only True if contains both criteria or false if not.

  Im not interested in all records that have DOG Or CAT as it would give me all instances of each which aren't specific enough for what Im trying to do.
  
  The ultimate aim is to give me the facility to type transactions into a form then it to go away and extract only the profiles that contain all the transactions Im looking for.
  
  Sorry if I've not been very clear. I just really need to know if its possible to do this via SQL/Access Query or whether I need to look at trying to write a piece of code to do it.
  
  
  
  

  The results Im expecting to see are (transactions field) X,Y & Z as contain both elements CAT and DOG.
  
  I've had abit of success using IIF but as only gives me facility to use 2 criteria it means that I will have to make a number of subqueries to obtain the results I need. Eventually I want to query on five transactions.
  
  Sorry if I've confused everyone!
  
  
  
  
  
  
  
  

  Report abuse