Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2015
    Posts
    16
    Provided Answers: 1

    Answered: Moving records to another identical table using SQL keeping relationships

    Hi there everyone,
    I have two tables one with prospect data [Prospects] and another that tracks call results [Results]. When the result of a call to prospect ends with a decline or bad number, those prospects are moved to a table called Rejections.

    Since the Call Results form uses a combo to select the prospect from the [Prospects]table and therefore has a relationship, how do I move unwanted prospects to the other table [Rejections] while keeping the relationship with the [call result] table in tact using an SQL Query?

    Thanks for all your help.
    I am a complete Novice...:-)

    Darren

  2. Best Answer
    Posted by DDEWILDE

    "
    Quote Originally Posted by Missinglinq View Post
    Not really sure what you mean by 'keeping the relationship intact,' but most developers would not have a 'Rejections' table; instead they would have a Yes/No Field named something like...Rejected...and simply tick it if a call is rejected. You'd then filter Forms or Reports to no longer display any Records where Rejected = True.

    Linq ;0)>
    Dear Missingling,

    Strangely enough I done exactly what you suggested before getting this reply. I created a Yes/No column called rejected then in the row source of the combo I amended the criteria to not display any true values in that column. Much easier. Is there any code I can use in the AfterUpdate event on a combo on the result form that will check the box in the Prospecting table automatically..

    For instance the combo Results contains possible call results so if I select "Not Interested" in the combo how can I then cause the Rejected box in the Prospects Table to change to true? Also if I did this do I use requery to cause the current form Results to update with the prospects that were rejected so they won't show up in the Prospect combo or do I always have to close and reopen the form?.

    Many thanks indeed for your help.
    Darren"


  3. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Not really sure what you mean by 'keeping the relationship intact,' but most developers would not have a 'Rejections' table; instead they would have a Yes/No Field named something like...Rejected...and simply tick it if a call is rejected. You'd then filter Forms or Reports to no longer display any Records where Rejected = True.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #3
    Join Date
    Apr 2015
    Posts
    16
    Provided Answers: 1

    Wink

    Quote Originally Posted by Missinglinq View Post
    Not really sure what you mean by 'keeping the relationship intact,' but most developers would not have a 'Rejections' table; instead they would have a Yes/No Field named something like...Rejected...and simply tick it if a call is rejected. You'd then filter Forms or Reports to no longer display any Records where Rejected = True.

    Linq ;0)>
    Dear Missingling,

    Strangely enough I done exactly what you suggested before getting this reply. I created a Yes/No column called rejected then in the row source of the combo I amended the criteria to not display any true values in that column. Much easier. Is there any code I can use in the AfterUpdate event on a combo on the result form that will check the box in the Prospecting table automatically..

    For instance the combo Results contains possible call results so if I select "Not Interested" in the combo how can I then cause the Rejected box in the Prospects Table to change to true? Also if I did this do I use requery to cause the current form Results to update with the prospects that were rejected so they won't show up in the Prospect combo or do I always have to close and reopen the form?.

    Many thanks indeed for your help.
    Darren

Tags for this Thread

Posting Permissions

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