Extra relationship

[guangdew via AccessMonster.com]

In my database, I have a Suppliers table and a Product table.

A main/sub form is made based on these two tables. Supplier_ID is the primary
key in Suppliers table, Product table also contain this Supplier_ID field but
not as primary key.

My problem is that after I setup the relationship, MS Access produces another
Supliers_1 table and setup a many to one relationship. I tried to delete it,
but MS Access keeps on adding it.

Any one has an idea about what's wrong?

I tried to attach a screen shot of the relationship but don't know how to do
it. I can email it to anyone if you are interested.

Guangdew

 

[Allen Browne]

How did you try to delete it? By deleting Supplier_1 from the Relationships
window? If so, that's not actually deleting the relationship.

What I'd suggest would be this:
1. If these are attached tables (in another Access database), open the back
end (the file that actually contains the tables.) If they are not linked
tables, you are probably already in the right database.

2. Right-click the *line* joining Suppliers_1 to Product, and choose Delete.
Confirm that you want to delete this relationship.

3. Right-click the *line* joining Suppliers to Product, and choose Delete.
Confirm you want to delete this one too.

4. Delete the Suppliers_1 table from the Relationships window, and Save.

5. Make sure you have the Name AutoCorrect option turned off. For an
explanation of how/why, see:
Failures caused by Name Auto-Correct
at:
http://allenbrowne.com/bug-03.html

6. Compact the database.

7. Open the relationships window.
Click the Show All Relationships button.
Verify that there is no Suppliers_1 table shown, and no relationship at all
between Suppliers and Product.

8. Create the relationship you do want between Suppliers and Product tables.

 

[guangdew via AccessMonster.com]

Allen,

Thank your for responding to my post.

You are right that the problem is caused by Name Autocorrect and also I
didn't really delete the relationship, I deleted only the Supplier_1 table.
After I deleted the relationship, the Supplier_1 table is gone.

Thank you again for your help,

Guangdew

How did you try to delete it? By deleting Supplier_1 from the Relationships
window? If so, that's not actually deleting the relationship.

What I'd suggest would be this:
1. If these are attached tables (in another Access database), open the back
end (the file that actually contains the tables.) If they are not linked
tables, you are probably already in the right database.

2. Right-click the *line* joining Suppliers_1 to Product, and choose Delete.
Confirm that you want to delete this relationship.

3. Right-click the *line* joining Suppliers to Product, and choose Delete.
Confirm you want to delete this one too.

4. Delete the Suppliers_1 table from the Relationships window, and Save.

5. Make sure you have the Name AutoCorrect option turned off. For an
explanation of how/why, see:
Failures caused by Name Auto-Correct
at:
http://allenbrowne.com/bug-03.html

6. Compact the database.

7. Open the relationships window.
Click the Show All Relationships button.
Verify that there is no Suppliers_1 table shown, and no relationship at all
between Suppliers and Product.

8. Create the relationship you do want between Suppliers and Product tables.

In my database, I have a Suppliers table and a Product table.

[quoted text clipped - 17 lines]

Guangdew