Solved

access if-statement

Posted on 2008-10-16
5
832 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!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Same ID From Multi-Record To One Record 4 27
Add different cell to otherwise similiar row 4 37
SQL Syntax 5 33
T-SQL: New to using transactions 9 18
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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.​
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

816 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now