Alias Circular Reference Problem

[Gribley]

Hi

I'm stuggling to fix this query which has a Circular ref alias
problem , any help would be great .

The error message I'm getting is Circular reference caused by alias
"Q3ExcesstoQ2" in query definition's Select List.

SELECT tblManagerSalesTemp.Q1SwareUSD,
tblManagerSalesTemp.Q1ExcessUSD, tblManagerSalesTemp.Q1ExcessUSDAvail,
IIf([Q2ExcessUSD]>0,0,IIf(-[Q2ExcessUSD]<=[Q1ExcessUSDAvail],-
[Q2ExcessUSD],[Q1ExcessUSDAvail])) AS Q1ExcessToQ2, [Q1ExcessUSDAvail]-
[Q1ExcessToQ2] AS Q1ExcessAvaQ34, IIf([Q3ExcessUSD]>0,0,IIf(-
[Q3ExcessUSD]<=([Q1ExcessAvailQ34]),[Q1ExcessAvailQ34])) AS
Q1ExcessToQ3, [Q1ExcessUSDAvail]-[Q1ExcessToQ3] AS Q1ExcessAvaQ4,
IIf([Q4ExcessUSD]>=0,0,IIf(-[Q4ExcessUSD]<=[Q1ExcessAvaQ4],-
[Q4ExcessUSD],[Q1ExcessAvaQ4])) AS Q1ExcessToQ4,
tblManagerSalesTemp.Q2ExcessUSD, tblManagerSalesTemp.Q2ExcessUSDAvail,
IIf([Q1ExcessUSD]>0,0,IIf(-[Q1ExcessUSD]<=[Q2ExcessUSDAvail],-
[Q1ExcessUSD],[Q2ExcessUSDAvail])) AS Q2ExcessToQ1, [Q2ExcessAvail]-
[Q2ExcessToQ1] AS Q2ExcessAvaQ34, IIf([Q3ExcessUSD]>0,0,IIf(-
[Q3ExcessUSD]<=([Q2ExcessAvaQ34]),-[Q3ExcessUSD]-[Q1ExcessToQ3],
[Q2ExcessAvaQ34])) AS Q2ExcessToQ3, [Q2ExcessAvaQ34]-[Q2ExcessToQ3] AS
Q2ExcessAvaQ4, IIf([Q4ExcessUSD]>0,0,IIf(-
[Q4ExcessUSD]<=[Q2ExcessAvailQ4],-[Q4ExcessUSD]-[Q1ExcessToQ4],
[Q2ExcessAvailQ4])) AS Q2ExcessToQ4, tblManagerSalesTemp.Q3ExcessUSD,
tblManagerSalesTemp.Q3ExcessUSDAvail,

IIf([Q2ExcessUSD]>0,0,IIf(-[Q2ExcessUSD]<=[Q3ExcessUSDAvail],-
[Q2ExcessUSD]-[Q1ExcessToQ2]-[Q4ExcessToQ2],[Q3ExcessUSDAvail])) AS
Q3ExcessToQ2,

[Q3ExcessUSDAvail]-[Q3ExcessToQ2] AS Q3ExcessAvaQ14,
IIf([Q1ExcessUSD]>0,0,IIf(-[Q1ExcessUSD]<=[Q3ExcessUSDAvail],-
[Q1ExcessUSD]-[Q2ExcessToQ1]-[Q4ExcessToQ1])) AS Q3ExcessToQ1,
[Q3ExcessAvaQ14]-[Q3ExcessToQ1] AS Q3ExcessAvaQ4,
IIf([Q4ExcessUSD]>0,0,IIf(-[Q4ExcessUSD]<=[Q3ExcessAvaQ4],-
[Q4ExcessUSD]-[Q1ExcessToQ4]-[Q2ExcessToQ4],([Q3ExcessAvaQ4]))) AS
Q3ExcessToQ4, tblManagerSalesTemp.Q4ExcessUSD,
tblManagerSalesTemp.Q4ExcessUSDAvail, IIf([Q3ExcessUSD]>0,0,IIf(-
[Q3ExcessUSD]<=[Q4ExcessUSDAvail],-[Q3ExcessUSD],[Q4ExcessUSDAvail]))
AS Q4ExcessToQ3, [Q4ExcessUSDAvail]-[Q4ExcessToQ3] AS Q4ExcessAvaQ21,
IIf([Q2ExcessUSD]>0,0,IIf(-[Q2ExcessUSD]<=[Q4ExcessAvailQ21],-
[Q2ExcessUSD]-([Q1ExcessToQ2]-[Q3ExcessToQ2]),[Q4ExcessAvailQ21])) AS
Q4ExcessToQ2, [Q4ExcessAvail21]-[Q4ExcessToQ2] AS Q4ExcessAvailQ1,
IIf([Q1USDExcess]>0,0,IIf(-[Q1USDExcess]<=[Q4ExcessAvailQ1],-
[Q1USDExcess]-[Q2ExcessToQ1]-[Q3ExcessToQ1],[Q4ExcessAvailQ1])) AS
Q4ExcessToQ1
FROM tblManagerSalesTemp;

Thanks

 

[rolaaus]

Queries can be a bit tricky, especially getting sytnax just right and using
variables like what you are doing.

I tried to take a quick look at what you posted, and even though you
seperated out where you are referencing the cause of the error, I wonder if
anyone else is going to do much more than I did.

My suggestion would be to try re-writing the whole thing tryinig to use
functions, if possible.

It appears you might be trying to carry forward an available balance from
one quarter to the next (Q1 excees, Q2 excess = q1 excess - q2 expenses - or
something like that).

By trying to use an expression, I mean, create a simple function in
procedure modules

GetExcess(inPrevBal as long, inCurrExpenses as long) as long
getExcess = inPrevBal - inCurreExpenses

Then you can go through each "quarter" and get the excess in your query

Select getExcess(Q1Bal, Q1Expenses) as Q2Excess, getExcess(Q2Bal,
Q3Expenses) as Q3Excess

I am not sure if you can just copy and paste this without doing any sort of
tweaks, but hopefully this helps as a starting point and let us know how it
turns out.

Hi

I'm stuggling to fix this query which has a Circular ref alias
problem , any help would be great .

The error message I'm getting is Circular reference caused by alias
"Q3ExcesstoQ2" in query definition's Select List.

SELECT tblManagerSalesTemp.Q1SwareUSD,
tblManagerSalesTemp.Q1ExcessUSD, tblManagerSalesTemp.Q1ExcessUSDAvail,
IIf([Q2ExcessUSD]>0,0,IIf(-[Q2ExcessUSD]<=[Q1ExcessUSDAvail],-
[Q2ExcessUSD],[Q1ExcessUSDAvail])) AS Q1ExcessToQ2, [Q1ExcessUSDAvail]-
[Q1ExcessToQ2] AS Q1ExcessAvaQ34, IIf([Q3ExcessUSD]>0,0,IIf(-
[Q3ExcessUSD]<=([Q1ExcessAvailQ34]),[Q1ExcessAvailQ34])) AS
Q1ExcessToQ3, [Q1ExcessUSDAvail]-[Q1ExcessToQ3] AS Q1ExcessAvaQ4,
IIf([Q4ExcessUSD]>=0,0,IIf(-[Q4ExcessUSD]<=[Q1ExcessAvaQ4],-
[Q4ExcessUSD],[Q1ExcessAvaQ4])) AS Q1ExcessToQ4,
tblManagerSalesTemp.Q2ExcessUSD, tblManagerSalesTemp.Q2ExcessUSDAvail,
IIf([Q1ExcessUSD]>0,0,IIf(-[Q1ExcessUSD]<=[Q2ExcessUSDAvail],-
[Q1ExcessUSD],[Q2ExcessUSDAvail])) AS Q2ExcessToQ1, [Q2ExcessAvail]-
[Q2ExcessToQ1] AS Q2ExcessAvaQ34, IIf([Q3ExcessUSD]>0,0,IIf(-
[Q3ExcessUSD]<=([Q2ExcessAvaQ34]),-[Q3ExcessUSD]-[Q1ExcessToQ3],
[Q2ExcessAvaQ34])) AS Q2ExcessToQ3, [Q2ExcessAvaQ34]-[Q2ExcessToQ3] AS
Q2ExcessAvaQ4, IIf([Q4ExcessUSD]>0,0,IIf(-
[Q4ExcessUSD]<=[Q2ExcessAvailQ4],-[Q4ExcessUSD]-[Q1ExcessToQ4],
[Q2ExcessAvailQ4])) AS Q2ExcessToQ4, tblManagerSalesTemp.Q3ExcessUSD,
tblManagerSalesTemp.Q3ExcessUSDAvail,

IIf([Q2ExcessUSD]>0,0,IIf(-[Q2ExcessUSD]<=[Q3ExcessUSDAvail],-
[Q2ExcessUSD]-[Q1ExcessToQ2]-[Q4ExcessToQ2],[Q3ExcessUSDAvail])) AS
Q3ExcessToQ2,

[Q3ExcessUSDAvail]-[Q3ExcessToQ2] AS Q3ExcessAvaQ14,
IIf([Q1ExcessUSD]>0,0,IIf(-[Q1ExcessUSD]<=[Q3ExcessUSDAvail],-
[Q1ExcessUSD]-[Q2ExcessToQ1]-[Q4ExcessToQ1])) AS Q3ExcessToQ1,
[Q3ExcessAvaQ14]-[Q3ExcessToQ1] AS Q3ExcessAvaQ4,
IIf([Q4ExcessUSD]>0,0,IIf(-[Q4ExcessUSD]<=[Q3ExcessAvaQ4],-
[Q4ExcessUSD]-[Q1ExcessToQ4]-[Q2ExcessToQ4],([Q3ExcessAvaQ4]))) AS
Q3ExcessToQ4, tblManagerSalesTemp.Q4ExcessUSD,
tblManagerSalesTemp.Q4ExcessUSDAvail, IIf([Q3ExcessUSD]>0,0,IIf(-
[Q3ExcessUSD]<=[Q4ExcessUSDAvail],-[Q3ExcessUSD],[Q4ExcessUSDAvail]))
AS Q4ExcessToQ3, [Q4ExcessUSDAvail]-[Q4ExcessToQ3] AS Q4ExcessAvaQ21,
IIf([Q2ExcessUSD]>0,0,IIf(-[Q2ExcessUSD]<=[Q4ExcessAvailQ21],-
[Q2ExcessUSD]-([Q1ExcessToQ2]-[Q3ExcessToQ2]),[Q4ExcessAvailQ21])) AS
Q4ExcessToQ2, [Q4ExcessAvail21]-[Q4ExcessToQ2] AS Q4ExcessAvailQ1,
IIf([Q1USDExcess]>0,0,IIf(-[Q1USDExcess]<=[Q4ExcessAvailQ1],-
[Q1USDExcess]-[Q2ExcessToQ1]-[Q3ExcessToQ1],[Q4ExcessAvailQ1])) AS
Q4ExcessToQ1
FROM tblManagerSalesTemp;

Thanks