SQL for Microsoft Access

1240 pts.
Tags:
Microsoft Access
SQL
I have crated the below SQL statement. I works fine. and it gives me the required "RTCDate" I need, that which is difined at the Where statement of 3 - 2010. The other constraints are that I need all my 79 product line numbers. and the "1" is those product line numbers that are active and current. So all the above is correct and I receive what I need.  The problem is what I add more product lines to the Where statement.  If I want to see all the product lines for 72,72,74,75,76,77,78,and 79.  So I added to the where statement the following    Where INMSP100.MSCLS ='72' or MSCLS = '73'... MSCLC = '79'.  When I ran the query with the multiple MSCLS qualifiers I got all different "RTCDate" dates, why would that happen if the Where state is still the same for the INMSP100.MSRDTY = '10' and INMSP100.MSRDTM = '03'?

Select DIstinct INMSP100.MSCLS  as "Class", INMSP100.MSPRD as "PN", INMSP100.MSRDTM || '/'  ||  INMSP100.MSRDTD || '/' || INMSP100.MSRDTY as "RTCDate", INMSP100.MSAPP as "Appltn", INMSP100.MSMPC as "Phase", INMSP100.#MSTSP as "TstSpec", INMSP100.#MSPSF as "PilotRun", INMSP100.#MSRFS as "FactSht", INMSP100.MSSAM  as "OEinHuse", INMSP100.MSPHO as "FG-Img", INMSP100.MSASM as "AsmSmpl", INMSP100.MSPSM as "PackSmpl", INMSP100.MSBOM as "BOM", INMSP100.MSCSG  as "ConsldSg", INMSP100.MSCNV   as "ConvSg", INMSP100.MSWGT  as "Wght", INMSP100.MSCDS  as "CatDec", MSPMP100.PMBPC as "PopCode", MSPMP100.PMPFAM as "PrdFmlyCode", MSPMP100.ACTIV as "Active" From rdb.CARF1.INMSP100 INMSP100 right join rdb.CARF1.MSPMP100 MSPMP100 on MSPRD  =  PRDNO Where INMSP100.MSCLS ='79' and INMSP100.MSRDTY = '10' and INMSP100.MSRDTM = '03' and MSPMP100.ACTIV = '1'



Software/Hardware used:
MS Access 2000,

Answer Wiki

Thanks. We'll let you know when a new response is added.

You might need to use parenthesis to group all your ‘OR’ conditions. When you use the OR logical operator, if one of the conditions is met, the result will be true whether the other condition is met or not.

Something like this:

<pre>Where <b>(</b>INMSP100.MSCLS =’72’ or MSCLS = ’73’… MSCLC = ’79′<b>)</b>
AND INMSP100.MSRDTY = ’10’
AND …</pre>

You can also do something like this:

<pre>Where INMSP100.MSCLS <b>in (’72’,’74’,’75’,’76’,’77’,’78’,’79’)</b>
AND INMSP100.MSRDTY = ’10’
AND …</pre>

-CarlosDL

CarlosDL is correct. By not using parenthesis, once the condition in an ‘OR’ statement is met, the rest of the parameters are ignored. In this case, you have to force your WHERE condition to check against all parameters by enclosing your ‘OR’ condition in parenthesis.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • CompEng
    Thanks...All I know is I have a lot to learn. In the past 9 to 10 months of learning this, all you guys have been a great support system!!! I can't thank you enough!!!
    1,240 pointsBadges:
    report
  • CompEng
    [...] SQL for Microsoft Access [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following