iif statement to report specific item or report default

[sawilliams]

I am querying a table of salutations. I want to pull signor (salutation type) 2 if it exists or choose the default salutation if no signor 2 exists. I tried an iif statement sort of like this:

SELECT IIf([signor]=2,[esal1_desc],IIf([default_ind]="Y",[esal1_desc])) AS Expr1
FROM TX_CUST_SAL;

And I get what I want, but I get multiple rows unless I group by and even then I get a blank row. I feel I must be going about this the hard way. Would anyone care to enlighten me? Many thanks.

 

[dhookom]

Can you explain better what you are attempting to do? I don't see anything that would limit your results to less rows than are in TX_CUST_SAL. Is this a table or query or what?

Duane
Hook'D on Access
MS Access MVP

 

[sawilliams]

My TX_CUST_SAL table has entries that look like this:

signor customer_no esal1_desc default_ind
1 00001 Mr. and Mrs. John R. Jones Y
3 00001 Mr. John Jones
5 00001 Mrs. Phyllis Jones
1 00002 Mr. and Mrs. Ed Smith
2 00002 Ed and Janet Smith
4 00002 The Smith Family Y

I want to report for each customer the signor 2 but if he has no signor 2, I want to report the salutation where the default_ind = "Y". My SQL is as follows:

SELECT TX_CUST_SAL.customer_no,
IIf([signor]=2,[esal1_desc],IIf([default_ind]="Y",[esal1_desc])) AS Expr1
FROM T_LIST_CONTENTS LEFT JOIN TX_CUST_SAL ON T_LIST_CONTENTS.customer_no = TX_CUST_SAL.customer_no
WHERE (((T_LIST_CONTENTS.list_no)=177))
GROUP BY TX_CUST_SAL.customer_no, IIf([signor]=2,[esal1_desc],IIf([default_ind]="Y",[esal1_desc]));

and returns this:

customer_no Expr1
00001
00001 Mr. and Mrs. John R. Jones
00002
00002 Ed and Janet Smith
00003
00003 Mr. Tim Tompkins

It doesn't really group and I have blank fields associated with customer_no's.

 

[dhookom]

I think you need a subquery

SELECT  T.*, 
(SELECT TOP 1 S.esal1_desc 
 FROM TX_CUST_SAL S
 WHERE T.customer_no = W.Customer_No
 ORDER BY signor=2, default_ind = "Y", Signor) As GoodColumnName
FROM T_LIST_CONTENTS T

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Create the following query:

SELECT customer_no, esal1_desc
FROM TX_CUST_SAL C
WHERE signor=2 OR (
default_ind='Y' AND NOT EXISTS(SELECT * FROM TX_CUST_SAL WHERE customer_no=C.customer_no AND signor=2)
)

Now you may join the T_LIST_CONTENTS table with the above query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886