A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Extra relationship



 
 
Thread Tools Display Modes
  #1  
Old February 13th, 2010, 09:07 AM posted to microsoft.public.access.tablesdbdesign
guangdew via AccessMonster.com
external usenet poster
 
Posts: 14
Default Extra relationship

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201002/1

  #2  
Old February 13th, 2010, 11:46 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Extra relationship

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"guangdew via AccessMonster.com" u58044@uwe wrote in message
news:a38fdca27af9d@uwe...
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


  #3  
Old February 13th, 2010, 04:25 PM posted to microsoft.public.access.tablesdbdesign
guangdew via AccessMonster.com
external usenet poster
 
Posts: 14
Default Extra relationship

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

Allen Browne wrote:
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


--
Message posted via http://www.accessmonster.com

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:26 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.