Allowing a Circular reference

[InstructionWhich7142]

Updated: Allowing a Circular reference

UPDATE:
I have made this work by making a second query and linking it to the first, it ran once and as far as I could tell gave the correct results down levels!!

However once I saved it and tried to run it again it gave a circular reference error, why did it do this when it has already worked once?!

----------------------------------

Hi,

I need to use a circular reference in a query, I can do it currently with an update query [below] where I reference the field being updated in the update statement but I dont want to have to make a temp table to do it (at some point a single instance of the temp table will exceed access's 2gb limit!)

UPDATE sndbasis LEFT JOIN sndbasis AS sndbasis_1 ON (sndbasis.cldate = sndbasis_1.cldate) AND (sndbasis.ijn = sndbasis_1.ijn) AND (sndbasis.pa_irn = sndbasis_1.irn) SET [B]sndbasis.requirement[/B] = IIf([sndbasis].[pa_irn]<>"",[sndbasis].[multiple]*[B][sndbasis_1].[requirement][/B],[sndbasis].[requirement]);

Above is the update query, how can I do this with a select query?

 

[InstructionWhich7142]

I think this is what I need (it describes my problem):
http://msdn.microsoft.com/en-us/library/ms186243.aspx
but I'm afraid I dont really understand it,

I'm also wondering if I can somehow trick the query into running on itself or something, any ideas?