.adp Query with a function?

[jyeaman]

Hi,
I am working with an access data project from a SQL 2005 database. I have a
query that pulls two different letter grades for a student and then using a
link to another table, I insert a coulmn with a numeric value for the letter
grades. I would also like a column that will calculate the difference
between the two number value coulmns. I inserted a row in the query and
entered an expression in the column, but the results then just show the
contents of the column as text, rather than the result of the calculation.

My question is, it there a way to insert a coulmn with an expression in an
Access data project query that does a calculation on two other columns of the
query? I do not see an expression builder button when I click on the
property sheet of the query.

Thanks!

 

[Michel Walsh]

You can, but MS SQL Server does not support expressions involving 'alias'
like Jet does.

SELECT a+1 AS aPlus, aPlus+1 AS aPlusPlus
FROM somewhere


is not allowed since the second expression involves an alias, aPlus. You
have to either substitute back the whole expression:


SELECT a+1 AS aPlus, (a+1)+1 AS aPlusPlus
FROM somewhere


either to make a virtual table:

SELECT aPlus, aPlus+1 AS aPlusPlus
FROM ( SELECT (a+1) AS aPlus FROM somewhere) As x



since then the virtual table, x, supplies aPlus as a field, not as an
expression, anymore.


Jet does not have that problem, though: the first query runs fine under Jet.



Vanderghast, Access MVP