How can I relate data within one table. 1

[mab43]

In my risk mgmt database, there is a main risk table that has a RiskID, RiskTitle, etc. I was asked to set up a way that we can identify related risk and show these related list on a form. For example, Risk 1 is related to Risk 2, Risk 1 is related to Risk 3, etc. but all the risks are in the same table. I have unsuccessfully tried queries and a separate related risk table. Any ideas?

 

[PHV]

Do a search for Self join

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[psemianonymous]

Yeah, there are two good ways I can think of to do this:

1. Add a 'category' or 'riskgroup' field, and assign similar risks the same value, i.e. "Type I/AB-etc". Then you can pull a query with all the 'similar' risks at the same time. This may not be specific enough though, so...


2. Have a table that ONLY stores 'risk-relations'. You have two RiskID's - the source and the destination. This way you can manually say Risk1->Risk2, and Risk2->Risk3, but
you wouldn't then have Risk1->Risk3 or whatever. This would work, you'd just find all relations by querying this 'risk-relations' table.


Or maybe you're asking for something else.