Can't create a One to Many relationship

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

James - 28 Jan 2005 16:17 GMT

I have 2 tables and I can't make a 1 to many relationship.
Everytime I create a new relationship, access doesn't give the option for
One to Many. I have tried to use enforce ref entegrity and it doesn't change
anything. Table 1 has an AutoNo. for its Primary Key. Table 2 has a number
for the Foreign Key.

What am I missing?

Reply to this Message

Lynn Trapp - 28 Jan 2005 16:23 GMT

Are both tables in the same database file?

Signature

Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

>I have 2 tables and I can't make a 1 to many relationship.
> Everytime I create a new relationship, access doesn't give the option for
[quoted text clipped - 4 lines]
>
> What am I missing?

Reply to this Message

James - 28 Jan 2005 16:57 GMT

Hi Lynn

Same database file.

> Are both tables in the same database file?
>
[quoted text clipped - 6 lines]
> >
> > What am I missing?

Reply to this Message

Lynn Trapp - 28 Jan 2005 18:00 GMT

Then check the things Roger suggested next.

Signature

Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

> Hi Lynn
>
[quoted text clipped - 12 lines]
>> >
>> > What am I missing?

Reply to this Message

Roger Carlson - 28 Jan 2005 17:43 GMT

Make sure the Foreign Key in Table 2 is NOT also the Primary Key (I've seen
databases where it was) and also make sure the the Foreign Key does NOT have
a unique index set.

Signature

--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

> I have 2 tables and I can't make a 1 to many relationship.
> Everytime I create a new relationship, access doesn't give the option for
[quoted text clipped - 3 lines]
>
> What am I missing?

Reply to this Message

John Vinson - 28 Jan 2005 18:52 GMT

>I have 2 tables and I can't make a 1 to many relationship.
>Everytime I create a new relationship, access doesn't give the option for
[quoted text clipped - 3 lines]
>
>What am I missing?

Is the referential integrity button greyed out? Do you get only one to
one relationships, or only indefinite relationships? If it's only
giving you one to one, that means that the foreign key field has a
unique Index (which you *don't* want).

The indexes might be messed up. Try opening the "one" side table;
remove the Primary Key; open the Indexes menu (the icon looks like
lightning hitting a datasheet) and delete all indexes. Compact the
database, then reestablish the primary key and re-index any fields
that need indexing.

John W. Vinson[MVP]

Reply to this Message