One to Many Relationships

[Pointy Haired Boss]

I am trying to build a one to many relationship where the One is a normal
field named 'UserID' within the first table while the Many is a Primary Key
named 'UserID' in the second table. I also want to enforce Referential
Integrity at the same time. The problem is that Access immediately tries to
do reverse the relationship due to the field in the table that I would like
to be the Many is a Primary Key.

How can I avoid this issue.

Thanks

 

[Ken Snell [MVP]]

You can't. By definition, the "one" side needs to be a primary or unique
index key, and the "many" side cannot be unique nor a primary key.

I think your table structure should be reconsidered. If you can post some
info, I'm sure we can provide suggestions.

 

[Jeff Boyce]

I support Ken's request that you post more info for more relevant advice.

I'll point out that the one-to-many relationship refers to the tables, not
the fields. Are you saying that the first table has a (unique) UserID
(i.e., is a primary key), and your table2 can have zero, one or many rows in
which there is a field that points back to table one?

A real world example of this is an Order (the one-side), which can have
zero, one or many related Items (I included the "zero" case for foolish
consistency -- an Order without at least one Item isn't an order!).

 

[Pointy Haired Boss]

Basically it goes like this. Table1 has fields of HRIS and UserID with HRIS
as the Primary Key. Table2 has fields UserID, Question1, Question2 and
Question3 with UserID as the Primary Key.

Basically the user inputs their existing HRIS and then creates a UserID in
Table1. This UserID is then entered along with the answers to Questions 1-3
in Table2.

For anonymity, I don't want management seeing the connection of someones
HRIS along with the agent's responses. Yes, I could accomplish this with a
flat database by leaving that field out of queries but want to try this
method instead.

 

[Jeff Boyce]

If the purpose is to prevent someone from connecting an HRIS (?some kind of
ID?) with an anonymizing ID (?UserID), don't give them the table that holds
the connection. If they only see the second table, they can't make the
connection between HRIS and UserID, right?

What am I missing?

 

[Pointy Haired Boss]

That isn't the issue. Please read my first post. Thanks

 

[Jeff Boyce]

I re-read your initial post, but didn't see anything about preventing
management from seeing the connection (your second post). What I saw was
your intent to create a one-to-many relationship.

As Ken pointed out, you can't do that, given what you described as your
table structure.

Perhaps you could post "what" you are trying to accomplish, rather than
"how", for more feedback from the 'group...

 

[Pointy Haired Boss]

No, thanks. That has alread been commuicated as well.

 

[David Seeto via AccessMonster.com]

If you changed your Questionnaire table structure from UserID, Question1, Question2, Question3 to UserId, QuestionNo, AnswerText (with the unique key being the first two fields), then you should be able to define the one to many relationship to enforce referential integrity just fine.

So you're saying that when a person leaves the organisation, their questionnaire results should also be deleted? Personally, I'd think the results should still be relevant even after they've left - after all, if you had an Overall Employee Satisfaction score of 40% in May and Unhappy Employee X leaves in June, do you really want to the Overall Employee Satisfaction score for May to increase to 50%?

Although maybe you do, since you're the Pointy Haired Boss

*****************************************
* A copy of the whole thread can be found at:
* http://www.accessmonster.com/Uwe/Forum.aspx/access-tablesdbdesign/6311
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abuse.aspx?aid=4e8cf2d6f7d04f51bf3951d164da810a
*****************************************