Avatar of marian68
marian68
 asked on

QUERY IN ACCESS 2003

Attached you can find a sample of what I would like to have. It is about a query which returns only the records which have ou don't have certain values in another field.
Thank you,
SAMPLE1.xls
Microsoft AccessSQL

Avatar of undefined
Last Comment
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

8/22/2022 - Mon
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

You mean this:

SELECT Table5.PRODUCT
FROM Table5
WHERE (((Table5.Values)=5));

mx
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

OOPS

SELECT Table5.PRODUCT
FROM Table5
WHERE (((Table5.Values)=7));
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Why all that ?  

"I need a query which gives me the products who has in the field "VALUES" the value 7 but are not equal to 5 and 9."

mx
Capture1.gif
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Patrick Matthews

MX,

You and interpreted the requirements differently.  We'll see which of us got it right :)

Patrick
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Well ... I'm just reading what the XLS text says :-)
Although the post text is slightly different.

mx

marian68

ASKER
SORRY FOR DELAY
FOR DATABASE MX:
I THINK I DIDN'T EXPRESSED MYSELF VERY WELL.
THE SAME PRODUCT CAN TAKE MORE THAN 1 VALUE IN THE FIELD VALUE.
FOR EXAMPLE "PRODUCT1" CAN HAVE THE VALUES 5,7 AND 9.
I WOULD LIKE TO KEEP ONLY THE PRODUCTS WHICH ARE EQUAL TO 7 AND TO ELIMINATE THE PRODUCTS WHICH ARE EQUAL WITH 7 AND 5 OR 7 AND 9. IF A PRODUCT WHICH HAS THE VALUE 7 BUT IN THE SAME TIME THE VALUE 5 MUST BE ELIMINATED AND SO ON. IN OTHER WORDS PRODUCTS HAVE TO HAVE ONLY A UNIQUE VALUE OF 7
YOUR SQL DON'T WORK
NOW I WILL TEST THE SQL OF MATTHEWPATRICK
THANK YOU
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Patrick ... interesting how the statement changes from Design view (image below) to SQL view

SELECT tP1.PRODUCT, tP1.Values
FROM Table5 AS tP1
WHERE (((tP1.Values)=7 And Not (tP1.Values)=Exists (SELECT tP2.Values FROM Table5 AS tP2 WHERE tP2.PRODUCT= tP1.PRODUCT  AND tP2.Values Not In (5,9)  )));
Capture1.gif
marian68

ASKER
THANK YOU GUYS,
YOU ARE THE BEST
MY KNOWLEDGE IN SQL ARE VERY SMALL
I'LL BE BACK SOON WITH ANOTHER 3 QUERIES OF A CLOSE TYPE
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Thanks ... but, I should have noted 'No Points for me".  I was just confirming his does work.

mx
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy