Link to home
Start Free TrialLog in
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
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

You mean this:

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

mx
OOPS

SELECT Table5.PRODUCT
FROM Table5
WHERE (((Table5.Values)=7));
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
MX,

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

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

mx

Avatar of marian68
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
Thanks ... but, I should have noted 'No Points for me".  I was just confirming his does work.

mx