Are recursive queries possible?

  • Thread starter Thread starter Todd A. Anderson
  • Start date Start date
T

Todd A. Anderson

TABLE A consists of fields B and C, both strings. B is primary key.
TABLE D consists of field E, a string, and field F, an integer. E is
primary key.

I need to write a query that has the following semantics.

foreach row in D {
if D.E does not match any A.B {
insert into table G a row D.E as G.H, D.F as G.I
}
else {
insert into table G a row D.E, (D.F + (SELECT G.I from G where G.H =
D.E))
}
}

I don't believe this can be expressed in SQL. I think this is necessarily
iterative. First,
you have to do all entries that don't have a match in table A and then all
those entries
that only depend on those already in G and iterate until every entry in D
has a corresponding
entry in G.

any thoughts?
 
Hi,


INSERT INTO g(h, i)
SELECT d.e, d.f+Nz(k.i)
FROM (d LEFT JOIN a ON d.e=a.b)
LEFT JOIN g As k ON d.e=k.h


should do.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top