Rewriting mdb queries as views

[el zorro]

I am in the process of migrating an Access mdb database to an adp file linked
to a SQL 2000 server. Of course, I'm finding that some of the things I was
able to do in mdb query-by-design pane dont quite work in the SQL design
environment.

For example, in mdb I was able to define a field in the query based on an
expression and giving the new field a name-- an "alias" in SQL. I could then
use the new field in other calculations by referring to the alias of the
calculated field. SQL doesn't seem to let me do that: "Services per Hour:
[All Services]/[Total Hrs]" would define a new field. I could then refer to
that "alias" in another calculation: Services per Week: [Services per
Hour]*40, where "Services per Week" is the name of a new query field. Not a
great example, but the idea is that I could define new fields in the query,
and refer to those fields in the definition of other fields. In SQL, I would
apparently have to write that last field as "([All Services]/[Total
Hrs])*40." OR.. is there a way to refer to a field by it's alias within the
query?

 

[Bob]

Services per Hour: [All Services]/[Total Hrs]" would define a new field

Select yt.['Services per Hour]
From (
Select 'Services per Hour' = [All Services]/[Total Hrs]
, NextCol1
, NextCol2
From YourTable
Where .....
) YT

I think this answers your question....
hth,
bob.

 

[Norman Yuan]

It is quite similar in SQL Server' View/Store Procedure:

SELECT
[Field 1],
[Field 2],
[All Services]*[Total Hrs] AS ServicePerHour,
...
FROM TheTable

Then you can refer the colum in the View/SP as "ServicePerHour"