cannot change relationship to one to many

[Guest]

I have a table "Security Data" with SSN being the primary key. I also have a
table "SIR" with SSN being the primary key. I want to link these two tables
by SSN with a one (Security Data) to many (SIR) relationship. SIR being
Serious Incident Report. One individual can have many SIRs. When I go to
create my relationship, it automatically creates it "one to one" and I cannot
change it. Even after deleting all records in the SIR table, I still can't
create this relationship. Please HELP!!!

ynj

 

[SusanV]

If the SIR table has SSN as a Primary key, it can only have one of each SSN
entered - it's the unique identifier. You need to add a new field to be the
primary key for the SIR table. You can still index the SSN field, but you
will need to set it to allow duplicates. Once you do this, you'll be able to
create the one-to-many relationship.

 

[Barry Gilbert]

Your SIR table should have a different primary key. Using SSN as the
primary key here guarantees that each employee can have only one SIR.
If there is no other logical candidate for a primary key, create an
autonumber field. You should then be able to link the two SSN fields in
a one-to-many.

HTH,
Barry

 

[strive4peace]

If SSN in "SIR" is the primary key, how can you have
multiple records with the same SSN?

In order to allow "many" SSNs into the table, there cannot
be an index on it with Unique or Primary set to true. Check
the indexes window --> View, Indexes from the menu

It is best not to use SSN to link tables anyway. Use the
Autonumber ID field from the "Security Data" table to link
related tables.

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Guest]

Thank you, everyone! This solved my problem!!

ynj

 

[strive4peace]

you're welcome happy to help

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Guest]

Crystal,

After removing the primary key from SSN in my SIR table and setting the
index to NO....I was able to edit the relationshipt to One to Many. Thanks!

But this created another problem. I have a Security Data Form with all
personal info. On it I have a command button to open the SIR form either to
view an existing SIR or create a new one for this particular person. My SIR
form only opens for people who already have an SIR. I cannot see a blank
form to create an SIR for someone who's never had one.

I edited the relationship to all three types in JOIN TYPE and I still get
the same problem. What am I missing?

ynj

 

[strive4peace]

What is the RecordSource for your Security Data Form? You
main form should be based on the Security Data table. You
should have a subform to display, edit, and add information
to the SIR table.


Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Guest]

The main form is based on the Security Data table. I don't have a subform
for my SIR. Instead I have a command button on the Security Data form that
opens the SIR form.

My SIR form is not blank anymore. I was able to solve it by making SIRid my
Pkey.

The problem now is that I cannot create a new SIR because it's linking back
to the SSN and since it already exists, it thinks I'm duplicating it. My
relationship is still joining SSNs.

Are you saying I should link them with SIRid and another Id in my Security
Data form?

Hope my explanation makes sense.

 

[Guest]

Susan,

This did resolve my problem...thanks!

I created another field in the SIR table (SirID) and made that the pKey. I
was able to join the two tables with SSN (one to many).

The new problem is that I cannot create an SIR (a command button on the main
(Security Data) form that opens the SIR form. I get an error saying that the
SSN already exists. I know that. I want to create more info. for that
record.

Should I not join the tables with SSN? It is not indexed in the SIR table.

 

[strive4peace]

You should have an ID field in your Security table

SecurityID, Autonumber

In the Sirs table, you should have

SecurityID, Long Integer

The SecurityID is what you should use to link. You will
also have SIRid (autonumber) in your SIR table. SSN should
ONLY be in the Security table.

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Guest]

OK, I will try that. Thanks!

You should have an ID field in your Security table

SecurityID, Autonumber

In the Sirs table, you should have

SecurityID, Long Integer

The SecurityID is what you should use to link. You will
also have SIRid (autonumber) in your SIR table. SSN should
ONLY be in the Security table.

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day