Solved

access if-statement

Posted on 2008-10-16
5
842 Views
Last Modified: 2012-08-14
please give me the complete SQL to make an if-statement (with the field name "T/C") based on the following criteria and add it to  the attached code snippet
1.  if the field called "cust_ID" is not null in both "table C and table T, then I want the record to say "C/T"
2.  if  "cust_ID" is null in table C, but not null  in table T, then I want it to say "T"
3. if  "cust_ID" is null in table T, but not null  in table C, then I want it to say "C"

thank you very much
SELECT M.cust_ID
FROM qryConverterTransCustomers_01 AS C RIGHT JOIN (qryConverterTransCustomers_02 AS T RIGHT JOIN tmpMasterTable AS M ON T.cust_ID = M.cust_ID) ON C.cust_ID = M.cust_ID;

Open in new window

0
Comment
Question by:gringotani
  • 3
  • 2
5 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22732715
Will c.cust_id AND t.cust_id ever be null?  If so, neither will be displayed with this query.  Regardless, this should handle C, T or T/C.

SELECT M.cust_ID
,iif(c.cust_id is not null, iif(m.cust_id is not null, 'T/C', 'C'), iif(m.cust_id is not null,'T','NEITHER'))
FROM qryConverterTransCustomers_01 AS C RIGHT JOIN (qryConverterTransCustomers_02 AS T RIGHT JOIN tmpMasterTable AS M ON T.cust_ID = M.cust_ID) ON C.cust_ID = M.cust_ID;

0
 

Author Comment

by:gringotani
ID: 22739635
Hi, thanks for answering my question.  Your SQl doesnt exactly match my conditons.  pls fix.  
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22740272
Well if you tell me what it is/isn't doing that would make it a lot easier?
0
 

Author Comment

by:gringotani
ID: 22740744
the purpose of the if statement is to tell me if csut-ID is present in either table T or C. the if statement should not contain table M.  
1.  if the field called "cust_ID" is not null in both "table C and table T, then I want the record to say "C/T"
2.  if  "cust_ID" is null in table C, but not null  in table T, then I want it to say "T"
3. if  "cust_ID" is null in table T, but not null  in table C, then I want it to say "C"

thank you very much
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 50 total points
ID: 22743027
Got it.  And do you want t/c or c/t.  Because you said t/c at first and c/t now.  Or doesn't it matter?

This should do it.
SELECT M.cust_ID
,iif(c.cust_id is not null, iif(t.cust_id is not null, 'T/C', 'C'), iif(t.cust_id is not null,'T','NEITHER'))
FROM qryConverterTransCustomers_01 AS C RIGHT JOIN (qryConverterTransCustomers_02 AS T RIGHT JOIN tmpMasterTable AS M ON T.cust_ID = M.cust_ID) ON C.cust_ID = M.cust_ID;

Open in new window

0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql server function help 15 38
Currency in SQL? 2 32
Access join syntax when converting to T-SQL query 4 36
Finding gaps or missing dates in a date range using TSQL 3 27
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question