Rename two fields and calculate the difference using the new etiqu

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sverre - 25 Apr 2008 13:34 GMT

Hi

I have the following query:

SELECT 1402 AS ACCOUNT, [YY Saldo 1402].SummaförAMOUNT AS HB, [YY Saldo
1402].ACQ_AV_ACCRUED_INT AS SESAM, [HB]-[SESAM] AS [Diff HB SESAM]
FROM [YY Saldo 1402]
GROUP BY 1402, [YY Saldo 1402].SummaförAMOUNT, [YY Saldo
1402].ACQ_AV_ACCRUED_INT, [HB]-[SESAM];

What I would like to add is to use the function sum for the fields:
1. SummaförAMOUNT (renamed to HB)
2. ACQ_AV_ACCRUED_INT AS SESAM (renamed to SESAM)
3. Diff HB SESAM

When I use the function SUM instead of group by on these fields ACCESS
returns a 3 Boxes and asks me for parameter value for each field.

Can I avoid this and why does Access ask for parameter value?

Many thanks in advance

Reply to this Message

Duane Hookom - 25 Apr 2008 15:45 GMT

I always try to avoid re-using an alias in another calculation when creating
queries in Access.

SELECT 1402 AS ACCOUNT, [YY Saldo 1402].SummaförAMOUNT AS HB,
[YY Saldo 1402].ACQ_AV_ACCRUED_INT AS SESAM,
[SummaförAMOUNT]-[ACQ_AV_ACCRUED_INT] AS [Diff HB SESAM]
FROM [YY Saldo 1402]
GROUP BY 1402, [YY Saldo 1402].SummaförAMOUNT,
[YY Saldo 1402].ACQ_AV_ACCRUED_INT,
[SummaförAMOUNT]-[ACQ_AV_ACCRUED_INT];

Signature

Duane Hookom
Microsoft Access MVP

> Hi
>
[quoted text clipped - 17 lines]
>
> Many thanks in advance

Reply to this Message