Reference to Alias Columns

[Pete]

I have a query in which I have several Alias Columns I must use in other
columns. However, every time I attempt to refer to an Alias the sql errs the
procedure. Any suggestions, I have a very complicated Leave Query that needs
to determine leave updates for each month and refers to Alias columns
continually.

Thansk for any assistance.

 

[KARL DEWEY]

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 - which came first. If the SQL
tries to process data using the alias 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 alias part near
the end and then its use up front near the SELECT it might work.

 

[Sylvain Lafontaine]

Usually, these complex expressions should be computed on the client side and
not on the server; otherwise, your only option would be to enclose the query
as a subquery if you want to re-use them:

select A, B, C = A + B from (
select 1 as A, 10 as B
union all select 2, 20
union all select 3, 30
) as Q

 

[pete.trudell]

This makes no sense that Microsoft- the Great Provider of Excellent
Software (?) can't design a Access Frontend / SQL Backend that can use
all the available functions in a database, including referencing an
Alias Column in a function. What has happened here is I have wasted 5
months of work to design a Databasde I can not use.

I need options here, My directions from our State Office was to
upgrade our 2003 stand alone database to a modern Access frontend/SQL
backend..... What did I do wrong? Where do I get directions, instead
of struggling for months, and were are the references to doing it
right? By the way, State Office doesn't know either.

Thank You.

PS: Sorry that I am a little upset, but closed up in a cubbicle for 5
months and having the results turn to mush.... doesn't help my normal
cherrful self.

 

[Sylvain Lafontaine]

I'm now glad for you because surely you must feel a lot more better after
all this venting. However, I do not work for Microsoft and I do not work
for your State Office either. This is also the case for almost everyone
else here; so if you have any complaint to make to MS or if you have any
problem with your employer that you would want to litigate; please use the
appropriate channel to do so.

Second, if you have a technical problem, your best course of action would be
to describe it here with all the details necessary for someone else to be
able to understand it; ie. to understand what you are trying to do and -
possibly - suggest one or more solutions for solving this. Don't forget to
take into account the fact that people here are not located at the front of
your desktop and cannot see what you can see and cannot read back or
remember what you have wrote these past five months.

 

[Finnman]

Hi, I'm having same kind of problem as Pete so maybe it's better not to start
a new thread.
I didn't quite understand the idea how to get around this problem, so here
is my situation: In Excel 2003 Microsoft Query the following SQL-clause gives
me an error saying: "Unknown column hours.hourssum in 'field list'"

Query:
SELECT orders.id AS id,
hours.hourssum AS hourssum
FROM
orders LEFT JOIN
(SELECT job.id AS id, sum(job.hours1) AS hourssum FORM job GROUP BY job.id)
AS hours ON orders.id=hours.id
WHERE orders.id = '123';

I know that the query above can be done a bit differently, but it's just an
example of the problem I have. It seems that MS Query doesn't see the table
alias "hourssum" and gives me a complaint that it doesn't exist.

Is there a way around this, maybe a different syntax int SQL-caluse or
something else?

 

[Sylvain Lafontaine]

First, it's usually better to start a new thread than to revive an old one.

In your case, it's not clear if your query is running on SQL-Server or on
MS-Access because you are making a mention of Excel and of Microsoft Query.

In your case, it's probably you're using the graphical designer of MS-Query.
All graphical designers are particularly weak when it comes to subqueries
and other complicated stuff. Switch to the SQL view (click on the "SQL"
button?) and keep working in text mode from there.

It's also possible that there is confusion between the word "HOURS" and some
reserved word in Excel; so you should try with something else than "HOURS".

Also, you have wrote "FORM" instead of "FROM"; so I'm not sure up to what
point your example is a true representation of your original problem.

Finally, this question would be best answered in a newsgroup dedicated to
Excel.

 

[Finnman]

Ok, I started a new thread to Excel zone.

I had a few errors in my last post. Word "FORM" should be written "FROM".
I don't think that word "hours" is a reserved word in Excel. This example
query is just an example of the problem to you. In my real SQL-clause, all
the field names are in finnish so I think that this makes easyer for you to
understand what I am trying to do here.

Let's continue this conversation here
http://www.microsoft.com/office/com...5d2-0f9dc16e9055&cat=&lang=en&cr=US&sloc=&p=1