Database linking referenced procedures/actors

[Altin]

I'm trying to design a database which will list our operating procedures (X) in work and all the other operating procedures referenced in the that procedure (Y). Then I want to be able to select the referenced procedures (Y) and see which procedures (X) reference to it.

A good analogy would be movie actors, and all the actors they have worked with. If I have a database showing: Tom Cruise (X) has worked with Dustin Hoffman (Y) & Jamie Foxx (Y), and Robin Williams (X) has worked with Matt Damon (Y) & Dustin Hoffman (Y).

Thats fine, but then afterwards, I want to be able to select Dustin Hoffman and show that he has worked with Tom Cruise & Robin Williams.

I can achieve the first aim using two tables, parent procedures/actors (X) & referenced procedures/actors (Y) and link them in the one form using a subform, but do not know yet how to achieve the second aim. I looked at IIF queries, but ultimately I want this to be simple enough for a non techy user to be able to select any (Y) procedure and see which procedures reference back to it.

Im relatively (read very!) new to access (2003) and looking forward to learning to use it.

 

[gemma-the-husky]

you probably need a junction table - as I think you are trying to model a many - to - many relationship - which you cant do in Access

think in terms of the movies

rainman had both Crusie and Hoffman (and others)

============
so you store the movies, the actors, and another table to show the actors IN the movie

============
table movies
table stars
table appearedin (movieID, starID)

and now you can search to find the sort of things you are interested in.


============
and in terms of forms/subforms

given a movie, you can see the actors in the subform

given an actor, you can see his movies in a subform

but its hard to see a way of visualising BOTH at the same time - the view needs to be from 1 side or the other

 

[Altin]

I dont think I really have another table to think in terms of. Going back to the original use for this, procedure Proc-001 (X) references Proc-039 & Proc-045 (Y), and Proc-002 references Proc-039 & others. I want it to be so that when I am updating Proc-039 I can see that both Proc-001 & 002 mention it, & so I know to go & check them and see how the updates affect them.

If I was the only one working with this I could have a two field table, Parent & Referenced Procedures, and use the Criteria query on the Referenced Procedures field (Like Proc-039), showing the respective Parent Procedures. I want ultimately though to open it up to other people, and was hoping there was some way to do this using a friendler UI, like a drop down menu in a form, which would select the Referenced procedure & show the respective Parent Procedure?

I dont mind at all if there is more than 1 form used, 1 for entering the information, & the other for searching it.