Self Join?? and How to

[hall312]

I have a table that contains case numbers. In my job, each case number could be related to another case number.

Example: 08-123456 has the same suspect as 09-555666...so it needs to be noted that they are related.

So I wanted a portal with a dropdown list on the main case number layout that I could simply pick one or ten other case numbers that are related. All the case numbers are in the table so they are all available to link to. Then I would simply use a button to go to the related case number.

Any suggestions...I have searched the forums and can't figure out how to do this.

[T-Square]

I'm not completely sure based on your description, but the scenario does not suggest a self join; I think you'd be better off thinking about the different entities you are trying to track, and creating tables to represent them.

For example, you mention cases and suspects. Perhaps you would want to track suspects as well? If so, then create a separate table for them. Then, to link suspects to cases, you'd create a join table, where you would at a minimum store the CaseID and the SuspectID, which would allow you to join those two entities together. Then, you can always use the Power of The Database to see all the cases related to a suspect, all the suspects related to a case, and how many slices of cold cuts make the ideal sandwich.

Just kidding on the last one.

HTH,

David

[hall312]

Tsquare,

Thanks for the quick response. What you describe is currently being done in the database. I also just need a simple view of all related cases within the case number layout. There are 15 tables as of now and I was hoping to have a simple dropdown list to select related cases. Sometimes the suspects are not the same, but the method of operation is, etc... it gets into a whole big thing. So any other help would be great. Now I'm off to make a sandwich :)

[hall312]

Back to looking at this problem. I've attached an example to what I need. Seems like a simple relationship but not working.

When I pick a case number in the portal on one record, I need it to show up on the related record's portal as well.

test.zip

[comment]

See if this helps:

http://fmforums.com/forum/showtopic.php?tid/171427/