Table dependencies - ARGH! (1 Viewer)

[TomH]

Hi all.

For a legal-case management database, I had tables parent OurFirmsTable and child OurLawyersTable. Using the FirmName field in OurFirmsTable, I would choose from a dropdown in a case-related form and pull the personnel list to be viewed in the form. Then, I wanted a table for opposing firms, TheirFirmsTable, in the same layout and format so I copied OurFirmsTable and edited the field names, etc. However, when I view in table view TheirFirmsTable, there is the little plus sign to the left and the OurLawyersTable is linked to the new table. If I add an attorney name to one of the opposing firms, the record including the firm name shows up in the OurLawyersTable as if they worked for us.

There is no relationship between TheirFirmsTable and OurLawyersTable in the Relationships view. The only link I can see is that I use both OurFirmsTable and TheirFirmsTable as sources of dropdowns in the Case-level form, for entering the firms involved in the case.

Can someone direct me to what I've done... or not done... to cause this? Could it be somehow related to the subforms I created to pull in the address information for both ours and their firms? THANKS!!!

 

[Rabbie]

Can you post a stripped down version of your DB so we can see what is going on

 

[Libre]

Easiest way to get rid of the unwanted relationship is to delete the table (I would expect Access to give you a warning that you're going to delete a relationship - which is what you want to do). Then recreate the table from scratch.
I wouldn't dissuade you from experimenting and figuring out what went wrong - but I would address the immediate problem first.

EDIT
Just recalled the following:
In the Relationships screen, right click on the background (not on a table window) and select Show All. Your unwanted relationship SHOULD be there. If it's not, it's perplexing why the relationship exists in the table view but not in the relationships view. Still, do as I suggested above and that SHOULD fix the problem.

EDIT
and one other thing: are these tables linked from a back end? If so then you need to explore and modify the table relationships from the back end. If you look at the relationships screen on the front end you will not see what you are looking for.

 

[TomH]

Nothing additional showed in the Relationships window so I just deleted the table, built one from scratch and formatted each field one at a time.

Not sure what that residual relationship was... I'll have to experiment to see if it recurs. Was hoping someone had seen that problem before.

Thanks for your help.

 

[Sean O'Halloran]

One other thought: I suggest that your major issue is that you have split into two tables data which should be in one: What you should have is a table "Firms", which holds all law firm data. "Ours" and "Theirs" are just conditions for that data, in the same way that you would have a table "People", with a field "Gender", in which you would select the condition "male" or "female". You would not want to have two tables "Female People" and Male People."

Just trying to help...

Sean