Solved

access if-statement

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to fix this error 14 57
total hours between two times grouped by type 6 43
How to simplify my SQL statement? 14 50
SQL Server Update Query Streamline 4 14
'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 …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

895 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

17 Experts available now in Live!

Get 1:1 Help Now