Recursive lookup in a table

[kamil.jedrzejewski]

Hi.
Is it possible to create a field in a table that lookups to a
different field in the same table?

for example I have an Employees table with fields:
ID
Name
Boss_ID

and as you can imagine Boss is also an employee and Boss_ID is related
to ID.
Can I do that in MS Access?

Another example:
Contracts table:
ID
Type
Subject
App_ID

Assumption - appendix to the contract is also a contract (I don't want
to create appendix table).
So:
If Type="Appendix" then App_ID should point to the Contract.ID of the
main contract.

[Contracts example is my real problem]

Best regards,
Kamil

 

[Ken Sheridan]

This is the classic 'bill of materials problem'. You cannot query
recursively to an arbitrary depth, only to a fixed depth by successive
self-joins. However, you can simulate recursive querying with procedural
code. I'm mailing you at your address below with a solution of mine which
was published some years ago in a magazine column which a contact of mine
writes. The Zip file also contains a database showing how a report can be
created showing the hierarchy in 'tree' form. The basis of this is in fact
a query using self-joins to a fixed level.

I'll be away fro the rest of this week, so my apologies in advance if you
get back to me and I don't reply for a while.

Ken Sheridan
Stafford, England

 

[REYES CRUZ]

HOLA

in
@f63g2000hsf.googlegroups.co
m:

Hi.
Is it possible to create a field in a table that lookups to a
different field in the same table?

for example I have an Employees table with fields:
ID
Name
Boss_ID

and as you can imagine Boss is also an employee and Boss_ID is
related to ID.
Can I do that in MS Access?

Yes, you can; but beware of the multiple issues caused by lookup
fields in tables. See http://www.mvps.org/access/lookupfields.htm

Better would be to design a form that uses a subform to show the
parent information. You would still have the Boss_ID column, in the
table but do the lookup elsewhere, as required.

Q

Another example:
Contracts table:
ID
Type
Subject
App_ID

Assumption - appendix to the contract is also a contract (I don't
want to create appendix table).
So:
If Type="Appendix" then App_ID should point to the Contract.ID of
the main contract.

[Contracts example is my real problem]

Best regards,
Kamil

 

[Ken Sheridan]

Kamil:

You might also like to take a look at Joe Celko's nested set model:


http://www.intelligententerprise.com/001020/celko.jhtml


This can be used for employees and bosses as the model is a tree
(mathematically speaking) with only one path between each node. In fact Joe
uses employees and bosses as the example in the above link. Consequently it
would work for your contracts if a contract can be an appendix to only one
contract. If a contract can be an appendix to more than one contact however
(, the adjacency list model (which is the basis of the demo 'BoM' file I
sent you) would be more appropriate.

Ken Sheridan
Stafford, England