Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    100

    Unanswered: copying info from one table to another...but missing records

    I am copying data from one table to another using:

    UPDATE [ArchiveForecastRevenue] INNER JOIN ForecastRevenue ON [ArchiveForecastRevenue].pr_ppProjectID = ForecastRevenue.pr_ppProjectID SET [ArchiveForecastRevenue].RevenueForecast16 = [ForecastRevenue].[RevenueForecast0];

    The problem I am facing is that when the original table has a primary key item not listed in the new table it simpy ignores it. How can I either get it to add it or check for this ahead of time and then add it?

    Thanks for the help.

    Norm
    Last edited by norm801; 03-04-04 at 12:03.

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Re: copying info from one table to another...but missing records

    Originally posted by norm801
    I am copying data from one table to another using:

    UPDATE [ArchiveForecastRevenue] INNER JOIN ForecastRevenue ON [ArchiveForecastRevenue].pr_ppProjectID = ForecastRevenue.pr_ppProjectID SET [ArchiveForecastRevenue].RevenueForecast16 = [ForecastRevenue].[RevenueForecast0];

    The problem I am facing is that when the original table has a primary key item not listed in the new table it simpy ignores it. How can I either get it to add it or check for this ahead of time and then add it?

    Thanks for the help.

    Norm
    Apologies ahead of time if I misunderstand your predicament.

    With the inner join, the result set is only going to include items where the joined fields are equal so it's not surprising that you're not getting everything.

    Do you need to limit this to SQL or can you use some code?

    I'm not an SQL expert, just an as needed user but one thought I had would be to use an "Insert Into" statement first consisting of all of the records in the New table where the joining field is null and adding the joining field values from the original table. Then the inner join would pick up all of the records.

    Just some thoughts.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  3. #3
    Join Date
    Jan 2004
    Posts
    100

    Re: copying info from one table to another...but missing records

    Originally posted by basicmek
    Apologies ahead of time if I misunderstand your predicament.

    With the inner join, the result set is only going to include items where the joined fields are equal so it's not surprising that you're not getting everything.

    Do you need to limit this to SQL or can you use some code?

    I'm not an SQL expert, just an as needed user but one thought I had would be to use an "Insert Into" statement first consisting of all of the records in the New table where the joining field is null and adding the joining field values from the original table. Then the inner join would pick up all of the records.

    Just some thoughts.
    Thanks for breaking down the logic. It worked!
    Last edited by norm801; 03-04-04 at 13:35.

Posting Permissions

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