If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Double Trouble with Alias Columns & Nested Case When in APD/SQLDatabase
Morning, I have been working on converting, by hand, a Access 2003
standalone database to a Access 2007 adp/SQL database. I've had considerable luck , and assistance from this site, in almost completing my project. However, I have run into two road blocks in my query writing: 1. How to reference an Alias column in the same query as that Alias is created. For example I have a field DateDiff('yy',NLGD,SepCh) As SepYr. I then need to reference SepYr to compare in a Case When statement. Program errs out. 2. How then do I nest Case When statements: in 2003 I used IIf ([SepYr]=35,21,IIf([SepYr]=30,19,IIf([SepYr]=25,17, etc. etc. ))))). However, I can not finmd the proper way to nest Case When Rhen Else End. Appreciate any directions on this. PS: I already attempted to find a way to creat the alias in query and then use that as with 2003, but ADP/SQL does not allow access to quries as with tables ?? Thanks |
#2
|
|||
|
|||
Double Trouble with Alias Columns & Nested Case When in APD/SQL Da
Your best bet is to not attempt to use the alias in the same query that
generates it. Use the same calculations instead. The reason is the chicken and egg dilemma - wich came first. If the SQL tries to procees data using the alais before it is created then it is lost. In my experience, but I would not put much on it, SQL seems to be processed from last to first in the SQL statement. So if you put the alais part near the end and then it's use up front near the SELECT it might work. -- KARL DEWEY Build a little - Test a little " wrote: Morning, I have been working on converting, by hand, a Access 2003 standalone database to a Access 2007 adp/SQL database. I've had considerable luck , and assistance from this site, in almost completing my project. However, I have run into two road blocks in my query writing: 1. How to reference an Alias column in the same query as that Alias is created. For example I have a field DateDiff('yy',NLGD,SepCh) As SepYr. I then need to reference SepYr to compare in a Case When statement. Program errs out. 2. How then do I nest Case When statements: in 2003 I used IIf ([SepYr]=35,21,IIf([SepYr]=30,19,IIf([SepYr]=25,17, etc. etc. ))))). However, I can not finmd the proper way to nest Case When Rhen Else End. Appreciate any directions on this. PS: I already attempted to find a way to creat the alias in query and then use that as with 2003, but ADP/SQL does not allow access to quries as with tables ?? Thanks |
Thread Tools | |
Display Modes | |
|
|