Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009

    Question 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")));
    Last edited by jmantn; 03-21-09 at 15:34.

  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    Is there a reason you don't just empty the table and repopulate it, rather than delete/recreate it?

  3. #3
    Join Date
    Mar 2009
    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.

  4. #4
    Join Date
    Mar 2009
    Provided Answers: 14
    This does not explain why you have to drop and recreate the table. What are the reasons for doing so?

    Have a nice day!

  5. #5
    Join Date
    Mar 2009
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts