ACCESS ARBITRARILY CHANGES RELATIONSHIPS SAVED QUERRY

[Guest]

I have table, REPORTS with has a one to many relationship with another table, PRODUCTS via a field, Product ID. The REPORTS table has a many-to-many relationship with another table, PATENTS, via join table using the combined primary keys from both, Report ID and Patent ID. I created a select querry which includes these tables and the relationships so that I can pull all the related fields together into one datasheet. After saving the querry, it works fine, at least the first few times. Eventually, Access wil arbitrarily alter relationship between REPORTS and PRODUCTS so that Product Id is now longer related to Product Id but some other field in the REPORTS table, Sometimes Report ID, but also sometimes Year
This alteration usually occures once I tried to create a report or another select using this querry as the source. Can anyone help be figure out how to fix this?

 

[Allen Browne]

First thing to try is to uncheck the Name AutoCorrect boxes under:
Tools | Options | General.
Then compact the databse:
Tools | Database Utilities.

If that does not solve the problem:
1. Delete the problem relationships.
2. Create a new (blank) database.
3. Turn off Name AutoCorrect.
4. Import everything: File | Get External | Import.
5. Recreate the relationships deleted in step 1.

Those steps should stop Access from trying to be too clever and crosslinking
field names, and should also fix any corruption that this has caused in your
database.

For more information on the many problems caused by Name AutoCorrect, see:
http://allenbrowne.com/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have table, REPORTS with has a one to many relationship with another

table, PRODUCTS via a field, Product ID. The REPORTS table has a
many-to-many relationship with another table, PATENTS, via join table using
the combined primary keys from both, Report ID and Patent ID. I created a
select querry which includes these tables and the relationships so that I
can pull all the related fields together into one datasheet. After saving
the querry, it works fine, at least the first few times. Eventually, Access
wil arbitrarily alter relationship between REPORTS and PRODUCTS so that
Product Id is now longer related to Product Id but some other field in the
REPORTS table, Sometimes Report ID, but also sometimes Year.

This alteration usually occures once I tried to create a report or another

select using this querry as the source. Can anyone help be figure out how to
fix this?