Can't create a One to Many relationship

[James]

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?

 

[Lynn Trapp]

Are both tables in the same database file?

 

[James]

Hi Lynn

Same database file.

Are both tables in the same database file?

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

 

[Roger Carlson]

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.

--
--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

 

[Lynn Trapp]

Then check the things Roger suggested next.

 

[John Vinson]

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?

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]