Recursive Query or Iteration - Whats it to be ?

[Harry]

I am in the process of designing an Access application that helps
predict the promotion prospects and likely date(s) of successful
promotions of Army personnel. The application is supposed to carry out
predictive processing by extracting people meeting promotion criteria
from a table and iterate the process to predict subsequent promotions
upto three ranks ahead. Each promotion is based on existence of a
vacancy, and in turn creates a chain of sub-ordinate promotions. So I
should be able to select an individual, Private X, and ask the
application to predict when can he become a Staff Sergeant after
fulfilling criteria that I specify. Moreover, the application should be
able to report other chain promotions that occur in the wake of Pvt X's
promotion to Corporal, Sergeant and Staff Sergeant. Any advice on the
database design or is it magic with queries ? My main doubt is where do
I store the result of each query iteration ?

 

[Michel Walsh]

Sounds like a flow problem over a graph, where some steps are probabilistic
(such will there be a vacancy, at time = t(given) ) with possibility to
replay the node if condition is not fulfilled, even, maybe, with
alternatives (like for a film asking for an exterior requiring a snow storm,
if, at a given date, there is no snowstorm, can wait next day, doing
something else that day, or can decide to simulate the storm, etc). The
graph of nodes (states) may become quite complex, and the goal is generally
reachable in a probabilistic distribution (ex. possibility to end the
filming in 6 days is 0.001; in 7 days is 0.04; in 8 days, is 0.167, ... )
rather than purely deterministic. The probabilities themselves are generally
pump from actually collected data and are sensible to change of procedures
that administration can decide to implement.

I doubt a simple query could solve such a complex problem, unless you can
simplify the nature of the problem, in a specific case. Sorry. Sure, I may
have miss-read your intention.

Vanderghast, Access MVP