Question how are update queries processed?

[InstructionWhich7142]

I have an update query, it updates a field with a calculated value which is based on the same field (but of the level above)

UPDATE sndbasis LEFT JOIN sndbasis AS sndbasis_1 ON

(sndbasis.ijn=sndbasis_1.ijn) AND (sndbasis.pa_irn=sndbasis_1.irn)

SET sndbasis.requirement = IIf([sndbasis].[pa_irn]<>"",[sndbasis].[multiple]*[sndbasis_1].[requirement],[sndbasis].[requirement]);

Surprisingly it actually does what I want! which is:

From a given top level value (where [sndbasis].[pa_irn]="") , calculate the value for the level 1s, then from those values the level 2s and so on,

For roughly 160k records it does over 800k updates, which I guess is how it manages this?

I would like to know if this is an acceptable outcome and also how/why it is able to do this ?
and how it knows to recalculate some values when their source values change?

 

[Beetle]

and how it knows to recalculate some values when their source values change?

It doesn't, which is why you should not be doing this in the first place. With very few exceptions, calculated values should not be stored in tables. See here for more on the topic.

 

[InstructionWhich7142]

Apologies, I know the mantra of no calculated values and I should have explained my purpose a little more to start with:

This table is itself only temporary, generated from some linked tables to do some complex calculations before generating a report and being deleted.


While we are on the subject though, the ERP system of a company I used to work for used calculated values all through its tables, generally for storing "Totals" of this and that for "At a glance" use, was it just badly written?

 

[Beetle]

This table is itself only temporary, generated from some linked tables to do some complex calculations before generating a report and being deleted.

The calculations are done in the query to begin with, so why not just create a Select query and use that as the record source of your report. I don't see the need to create a temporary table here.

While we are on the subject though, the ERP system of a company I used to work for used calculated values all through its tables, generally for storing "Totals" of this and that for "At a glance" use, was it just badly written?

I couldn't really comment on pros or cons of an application I haven't seen, but if this was an Access (Jet) database and they were storing a lot of calculated values in tables, then I would say that at least that part of it may not have been well thought out.

 

[InstructionWhich7142]

The calculations are done in the query to begin with, so why not just create a Select query and use that as the record source of your report. I don't see the need to create a temporary table here.

I thought the same and set it up, but I need to do some recursive stuff that I can't seem to get to work on a standard select query

I asked specifically about the recersive bit here:
http://www.access-programmers.co.uk/forums/showthread.php?t=223117

 

[InstructionWhich7142]

I'm still really stuck on this, anyone got any thoughts?