Relationships Problems

[MissTinSC]

I have a db with several tables. Each table has a primary key autonumber. I have created a corresponding field for each table id in my MainData table (ie, Classid, parentid,...) I have used these to create relationships in queries, and in the relationship window)

Problem #1:In the relationships window, I have noticed that tables are self generating "copies" of themselves: MainData, MainData_1, MainData_2...

Problem #2:Also, everytime I create a One-to-Many relationship between MainData and Classtbl, close the relationship window and reopen it, the relationship has been deleted.

What's Happening? I know I have done something terribly wrong, but I have no idea what that is.
please help.

Thanks,

MissTinSC

 

[scriverb]

I am not exactly sure how you have created this but my first impression is to just give advise on the setup.

1. The MainData table should have an AutoNumber Primary key.
2. All subsequent tables should have their own Primary Key which can be an AutoNumber but they should also have a Foreign Key(Number - Long Integer). This is the field that you or ACCESS will store the value of the corresponding AutoNumber value from the MainData table.
3. In most cases in the Relationships windows you will enter all of the tables into the window. The joins between the tables will be a one-to-many from the info in your posting. One being the MainData table and the Many being all of your other tables. One record in MainData can relate to many records in each of your secondary tables.
4. Point, click and drag the PrimaryKey field from the MainData table to the ForeignKey field in each of the secondary tables. This will create the appropriate join between the tables. There should be an arrow pointing toward the Secondary foreign key field.
5. In the relationships window select Join Type button and select the second option which will include all records from the MainData table and only those records from the secondary table that matches. This is called a left join.
6. You may also want to check the Referential Integrety box to assure that you do not orphan a secondary table record.

This is the method of correctly creating your relationship joins. When entering a query when the tables with relationships are added to the Query Design Window these relationships will automatically show up.

Take a look at what I have posted here and go over your relationships and queries. If you still have problems post back with specifics of the problem.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]