Access Automatically Creating Relationship I don't want

[Lord_Vader]

I have a database and I constructed all the tables and was going about adding relationships to them.

I tried to add those weird relationships where it puts the number after the table name (someone please tell me what that's called )

Maybe I shouldn't add those sorts of tables in the relationship setup and just do it in queries as they're needed?

But yea, when I set up the relationships like this:

Close the Window and then reopens it, it ends up like this:

That seems to me that access is creating a different relationship on its own that I don't want as well as un-linking one I DO want. Those fields share no relation other than both using a persons ID.


Any Ideas what's going on?

P.S. If you see any bad things I did with my design-feel free to holler at me.

Thanks

 

[HiTechCoach]

I think part of the issue is that you table is probably not properly normalized since you have lots of repeating fields that cause the People table to be related to the same table more that once.

I have found that in a properly normalized database I do NOT have to relate the same lookup table to multiple fields in another table.

If you normalized the data properly, you probably will not need the People table more that once to the relationship window.

 

[Lord_Vader]

I think part of the issue is that you table is probably not properly normalized since you have lots of repeating fields that cause the People table to be related to the same table more that once.

I have found that in a properly normalized database I do NOT have to relate the same lookup table to multiple fields in another table.

If you normalized the data properly, you probably will not need the People table more that once to the relationship window.

Do you have a specific table in mind that needs normalized, or should I revamp the whole thing ?

I can see where it would seem "unnormalized" with the Major Member 1,2,3 etc. so that could be put to a separate table.

Regardless of that though it still seems that I would need to relate it back to the People Table because each major member and adviser and mentor relate to a person. They should be look ups at the very least.

Thanks for your help

 

[gemma-the-husky]

the force of the access relationships is too strong, lord vader. It is more complex than you could possibly imagine

seriously, its hard to see exactly what is going on -

what are you trying to model exactly?

 

[KathyL]

I have this feeling, if I was called in to maintain your database, I would delete all of the relationship model. Believe it or not, after many years as a designer, I use a relationship model very minimally. A relationship model can get in the way of several things, particularly if you do imports, or maketables, batch processes. It's too long to explain here, but I've also gone into other IT shops and found other IT professionsal did the same thing. But you have to know what you're doing, with or without a model.