Help with Relationships and Linking

[markey]

I've got an issue I can't seem to get around. I've got a linked table from an ODBC (tbl_1). I then have a make-table query (qry_1) that takes the fields, formats them, and creates a static table (tbl_2). I then have a one-to-many relationship between tbl_2 and another table (tbl_3). When I try to run the make-table query which overrides tbl_2, I get the following error: "You can't delete the table tbl_2; it is participating in one or more relationships". How do I get around this? Any help is much appreciated, thanks.
ps. The main goal is to link a form to the two tables (tbl_2 & tbl_3) for updating purposes.

 

[Moniker]

It sounds like you have referential integrity involved with all three tables, and with the second table between tables 1 and 3, you cannot remove it without breaking that referential integrity. A Make-Table Query erases and then creates a table, so you're trying to remove the middle, and you cannot do that.

Go to the Database Window (press F11) and then click on the Relationship toolbar icon at the top (looks like three small forms in a triangle -- hold the mouse over the toolbar icon and see what it says if unsure). If you have a relationship between tbl_1 and tbl_2, and then between tbl_2 and tbl_3, there's your problem. You need to delete the referential integrity between tbl_1 and tbl_2.

It's that relationship between tbl_1 and tbl_2 that's causing your "participating in one or more relationships" issue. You don't need that. With that relationship in the "relationship" window, you're essentially saying that any deletions, changes, etc. in tbl_1 that affect tables tbl_2 or tbl_3 will cascade, and that's not good design since tbl_1 is linked.

Really, you don't want referential integrity between local and linked tables as that's extremely hard to maintain. You can still use your make-table query without this since it's deleting and recreating tbl_2 each time, and you can continue forward from there.

 

[markey]

Thanks, may I ask for additional help

Thanks, this all makes sense. Can I ask for your advice on overall design? I've got a table from an ODBC (tbl1). I created another table with additional fields (tbl2). Both have a common field Employee ID (EmpID). I need to create a form that has combo boxes. Lets say you select a leader from the combo box.... I need all of that leaders employees to show (maybe in a subform) etc with static data from (tbl1). I also need the ability to add info into text boxes that update (tbl2). I'm assuming the subform needs to be tabular. Hope this question makes sense. I tried creating a query that pulls everything through, but I'm not able to enter data into the (tbl2). I've got somewhat of a visual below. Any direction, advice, or links to examples is much appreciated. THanks again!!!

- The Newb

LEADER (select from combo box)

Emp1 tbl1 Data tbl1 Data tbl2 Data (ability to update tbl2 data)
Emp2 tbl1 Data tbl1 Data tbl2 Data (ability to update tbl2 data)
Emp3 tbl1 Data tbl1 Data tbl2 Data (ability to update tbl2 data)

 

[markey]

Thanks, may I ask for additional help

Thanks, this all makes sense. Can I ask for your advice on overall design? I've got a table from an ODBC (tbl1). I created another table with additional fields (tbl2). Both have a common field Employee ID (EmpID). I need to create a form that has combo boxes. Lets say you select a leader from the combo box.... I need all of that leaders employees to show (maybe in a subform) etc with static data from (tbl1). I also need the ability to add info into text boxes that update (tbl2). I'm assuming the subform needs to be tabular. Hope this question makes sense. I know its pretty simple stuff, but I can't figure it out. I tried creating a query that pulls everything through a form, but I'm not able to enter data into the (tbl2). I've got somewhat of a visual attatched. Any direction, advice, or links to examples is much appreciated. THanks again!!!

- The Newb

 

[cpremo]

Theoretically, you shouldn't have any problem. However some more details are needed before specific help/guidance can be given.

1. Will any data be entered or edited in the ODBC tbl1 table?
2. Does the data in the ODBC tbl1 table only relate to the Leader?
3. If the ODBC tbl1 table also contains "employees" data, you have to determine what field(s) you are going to use to restrict who can be added as an "employee" of the leader.

 

[Moniker]

See if this post helps out at all as your problem is rather common.

Check here

I responded a lot in that thread and there's an example I provided in there (starting at response #4), followed by a lot of detail.

 

[markey]

Moniker: Thanks, I'm looking at it and trying to figure out how to apply the logic to my db.
CPREMO: Thank you as well....
Q1. Will any data be entered or edited in the ODBC tbl1 table?
A1: No, tbl1 is autolinked to a SQL database. All fields dump into tbl1.

Q2. Does the data in the ODBC tbl1 table only relate to the Leader?
A2: No, tbl1 has data in the following order: Senior Leader, Team Leader, Employee, Emp ID, Hire Date, etc.

Q3. If the ODBC tbl1 table also contains "employees" data, you have to determine what field(s) you are going to use to restrict who can be added as an "employee" of the leader.
A3: Not sure on this one. I was going to use a query to filter the Team Leader/Employee. There is no way to add fields to tbl1.