Unanswered: Make Table query that replaces table with relationships
Got hopefully a quick question...
I've got a db where I get most of my info from an odbc and I have a query that takes select info from the odbc and makes a table with all the necessary info. I should possibly note it's a left outter join with Ref integrity (cascade update & Delete) set.
I haven't updated the table in a while and have set some relationships with the data from this table and others and didn't realize I could update until now because of the relationships I've implemented.
Is there any way to do my make query and have the relationship stay the same instead of me having to delete the relationship run the make table query and re-create the relationships every time I update this (as when I'm done this may be a daily thing).
Also trying to have the make table query assign a primary key to the new table and that's not working either.
here's my sql code:
SELECT [Agent Data].*, "CREATE UNIQUE INDEX AGENT_ID ON [Agent Hierarchy] (AGENT_ID) WITH PRIMARY" AS Expr1 INTO [Agent Hierarchy] IN 'M:\CE Database\2009 LE Database.accdb'
FROM [Agent Data]
WHERE ((([Agent Data].LOB) Not In ("ADM","ANA","CAT","MGT","WFC")));
The more and more I think about it the more I think I'm going to have to go without a primary key or relationships on my main table.
The reason I need to do a make table is to add newly hired people and delete the people who have left the company. This would be my hierarchy table.
The way my db is set up is as follows: I have 3 tables
Agent Data (Agent ID is PK)
Training Data (Course Number is PK)
Agent Training (the table that tied the two together)
I manually update the Training Data table with all pertinent info however the Agent Data table comes from an ODBC. I wanted to have a pk with the two main tables that way if we need to remove an agent or training it would remove it in the other tables as well.
However it seems that I cannot perform any type of action query on a table with relationships and I can't just delete everyone (which I guess is what would happen with a make as well) as it would delete valid training associations I don't want to delete.
So unless anyone has a better suggestion then I think I'll have to manually delete anyone that leaves the company and do a query that compares the data from the odbc and appends any new agents. That's the best idea I can come up with after four hours of trying to find a better solution.
Sorry If I didn't explain it correctly previously but what I was thinking I could do was drop and recreate the table which would remove all termed employees and add any new one plus a few things I forgot to mention which was this would update any employee that was out on short term or leave as well as departmental changes to name a few.
so doing a make table to replace the existing one prevents me from having to manually update information from other departments (terms come from hr, short term and leave comes from our short term/family medical leave person and so forth). Doing all of this manually would take up all of my time (it takes half a minute on my work computer just to save a record and it compacts and closes each time i save).
I hope this explains things a little better. Also now that I've typed this out I've realized another issue which is I can't just do an append to add agents as I won't get the status changes for all the existing ones. Guess I will have to drop the PK and relationships.