Make table with primary and possibly Referential integrity?

Hi all!

I'm probably way off base here but I've been using access for about a month and have read some books to ease the pain and from time to time I hit a roadblock.

My problem:
I have a backend database that creates a make table query from an odbc. This has to be updated regularly as it has information such as employee id's, names, leave status, terms, etc. I use the table that is created from the make query (Hierarchy) and use it with a (Training) table which has training info (such as location, trainer, course number) and a third table (AgentTraining) that associates the primary keys from the "Hierarchy" table and "Training" table.

I need the relationship for when an employee is termed it will delete all records instead of me having to hunt down orphaned records. I've also built some form objects that (cascading combo's and such) I believe depend on these relationships. As right now everything works but the make query due to relationships.

To show how this is set up:
Table Hierarchy with PK AGENT_ID is tied to AgentTraining FK AGENTID I'll leave the association of other table out as I don't think it's necessary for this explanation.

Since I'm still a novice I didn't realize I couldn't do a make table with relationships so I'm at an dead end. I have been deleting the relationship between "Hierarchy" and "AgentTraining" and running the make table query on my backend, running a query to find the orphaned records in my main db then re-establishing the relationships.

So I was wondering if maybe I could somehow bypass the restriction with relationships and doing a make query or if I could possibly put some type of constraint code into my make to work outside the box. At this point I'm open to suggestions.

Here's my SQL code for the make query:

Please Login or Register  to view this content.

and specific help would be appreciated as I started learning VBA and SQL three weeks ago.