converting switch to lookup tbl?

[xz]

I've had this lingering question for a while and am now finally trying
to address it. We know switch statements' sizes are limited. I have
this switch statement below, and I need to add more but am bumping up
against the size limitation:

SELECT Switch(
[TCODE]="4030","Purchase - abcd" ,
[TCODE]="3021","Dividend - abcd",
[TCODE]="8081","FIFO Sale - Subtract CDSC",
[TCODE]="7011" And IsNull([TBL1.SACCTNUM])<>True,"Not purchase",
[TCODE]="7011" And IsNull([TBL1.SACCTNUM])=True,"Purchase",
[TCODE]="7512" And IsNull([TBL1.SACCTNUM])<>True,"No Sale ",
[TCODE]="7512" And IsNull([TBL1.SACCTNUM])=True,"FIFO Sale",
[TCODE]="6011" And IsNull([TBL1.SACCTNUM])<>True,"Not purchase",
[TCODE]="6011" And IsNull([TBL1.SACCTNUM])=True,"Purch",
[TCODE]="6512" And IsNull([TBL1.SACCTNUM])<>True,"No sale",
[TCODE]="6512" And IsNull([TBL1.SACCTNUM])=True,"FIFO sale",
[TCODE]="7551","FIFO Sale - unmatched xchange",
[TCODE]= "6600","FIFO Sale by xyz",
[TCODE]="1010","FIFO pos adjust")
FROM TBL1;

It was suggested to me to try a lookup table, and I have done that and
populated it. It looks like this:

ID - autonumber
TCODE - Text
DESC - Text
NoSACCTNUM - Yes / No


Now I'm trying to figure out the mechanics of pulling that DESC field
into my query with a condition that sometimes the TCODE's are the
same, and the correct DESC (description) is determined by checking if
the SACCTNUM field from the main query has a value - if so, I want to
grab the DESC where NoSACCTNUM is yes. Otherwise, I want to grab the
DESC where NoSACCTNUM is no. I can change NoSACCTNUM to a text field,
I don't mind - whatever anyone suggests. I am just wondering how to
do the above logic, when I'm not a VBA expert. In Oracle I would
create a function, but I'm not clear in Access how to do this. I've
thought of doing it at the Join level, but I'm not sure that will work
either.

Thanks for any help!
JJ

 

[Michel Walsh]

Get two fields to lookup at:


code nullNum description ' fields
4030 true "Purchase - abcd"
4030 false "Purchase - abcd"
....
7011 true "Not purchase"
7011 false "Purchase"
....


and make the inner join (or the lookup) on the two first field, to be
associated to (or to read) the third one.



Hoping it may help,
Vanderghast, Access MVP

 

[Marshall Barton]

I've had this lingering question for a while and am now finally trying
to address it. We know switch statements' sizes are limited. I have
this switch statement below, and I need to add more but am bumping up
against the size limitation:

SELECT Switch(
[TCODE]="4030","Purchase - abcd" ,
[TCODE]="3021","Dividend - abcd",
[TCODE]="8081","FIFO Sale - Subtract CDSC",
[TCODE]="7011" And IsNull([TBL1.SACCTNUM])<>True,"Not purchase",
[TCODE]="7011" And IsNull([TBL1.SACCTNUM])=True,"Purchase",
[TCODE]="7512" And IsNull([TBL1.SACCTNUM])<>True,"No Sale ",
[TCODE]="7512" And IsNull([TBL1.SACCTNUM])=True,"FIFO Sale",
[TCODE]="6011" And IsNull([TBL1.SACCTNUM])<>True,"Not purchase",
[TCODE]="6011" And IsNull([TBL1.SACCTNUM])=True,"Purch",
[TCODE]="6512" And IsNull([TBL1.SACCTNUM])<>True,"No sale",
[TCODE]="6512" And IsNull([TBL1.SACCTNUM])=True,"FIFO sale",
[TCODE]="7551","FIFO Sale - unmatched xchange",
[TCODE]= "6600","FIFO Sale by xyz",
[TCODE]="1010","FIFO pos adjust")
FROM TBL1;

It was suggested to me to try a lookup table, and I have done that and
populated it. It looks like this:

ID - autonumber
TCODE - Text
DESC - Text
NoSACCTNUM - Yes / No


Now I'm trying to figure out the mechanics of pulling that DESC field
into my query with a condition that sometimes the TCODE's are the
same, and the correct DESC (description) is determined by checking if
the SACCTNUM field from the main query has a value - if so, I want to
grab the DESC where NoSACCTNUM is yes. Otherwise, I want to grab the
DESC where NoSACCTNUM is no. I can change NoSACCTNUM to a text field,
I don't mind - whatever anyone suggests. I am just wondering how to
do the above logic, when I'm not a VBA expert. In Oracle I would
create a function, but I'm not clear in Access how to do this. I've
thought of doing it at the Join level, but I'm not sure that will work
either.

Let's see how far this gets you:

SELECT L.DESC
FROM TBL1 As T LEFT JOIN lookuptable As L
ON T.TCODE = L.TCODE
AND L.NoSACCTNUM = ([T.SACCTNUM] Is Null)