Calculation w/a field created w/in the same query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul (ESI) - 16 Nov 2005 20:15 GMT

I'm using office 2003. I know it is possible to name a field, then use that
field for a calculation within the same query. I've done it before. I have a
new query, and it isn't working. Can anybody tell me why. Let me give an
example of what I mean:

Say each employee has an average attendance, then an average adherence. They
are then scored on each of those. For example, in Query2:

AttScore: [Query1].[Attendance] * 0.5
AdhScore: [Query1].[Adherence] * 0.5

This would calculate the scores. What I next want to do would be the
following:

TotalScore: AttScore + AdhScore

I've tried it many different ways in case the formatting mattered, such as
the above and:

TotalScore: [AttScore] + [AdhScore]

and:

TotalScore: [Query2].[AttScore] + [Query2].[AdhScore]

Nothing is working. Every time I try to run the query, it asks me to define
the fields, but if I leave the total score out, it works fine (meaning it
isn't a problem with where the fieldnames were defined in the first place)
So, using the above example, I'd get a pop up asking me to enter a value for
AttScore and one for AdhScore. Can anybody tell me what I could be doing
wrong and how I may be able to fix the problem.

Signature

Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

Reply to this Message

Jeff Boyce - 16 Nov 2005 20:44 GMT

Paul

How did you come to the conclusion that it IS possible? I don't recall ever
being able to do that.

A work-around would be to create your first query with the new fields, then
build a second query, based on the first, that uses the new fields THERE to
do the additional calculation.

Regards

Jeff Boyce
<Office/Access MVP>

> I'm using office 2003. I know it is possible to name a field, then use
> that
[quoted text clipped - 32 lines]
> AttScore and one for AdhScore. Can anybody tell me what I could be doing
> wrong and how I may be able to fix the problem.

Reply to this Message

John Spencer - 17 Nov 2005 17:53 GMT

Jeff,

You can refer to an alias in the Select clause of an aggregate query.

Rare case, but it does occur. As far as I know you can only get away with
it in the SELECT clause of an aggregate (totals) query.
This works

SELECT Sum([fid]) AS Expr1,
Count([fid]) AS Expr2,
[Expr1]+[expr2] AS Expr3
FROM FAQ

This fails
SELECT Sum([fid]) AS Expr1,
Count([fid]) AS Expr2,
[Expr1]+[expr2] AS Expr3
FROM FAQ
ORDER BY Expr3

It might also work with a crosstab. I'm not sure. Since it is an
exception, I tend to write all my Access SQL statements without using
aliases for other than naming purposes.

> Paul
>
[quoted text clipped - 48 lines]
>> AttScore and one for AdhScore. Can anybody tell me what I could be doing
>> wrong and how I may be able to fix the problem.

Reply to this Message