Results 1 to 8 of 8
  1. #1
    kstyles is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2010
    Posts
    24

    duplicating records

    I am making an append query to update the name of the relationship manager in my notes table based on the relationship manager in an annual review table. The tables are related by annual review number. When I use the append query to insert the relationship manager into the notes table it does just that - but the fields where it once was blank are still in the table. So I have doubled the number of records in the table. Now I have the original records with the blank relationship manager, plus the record with the relationship manager field now updated. Is there a way to do this without doubling the records?



    INSERT INTO [Note Table] ( [Relationship Manager] )
    SELECT [Note Table].[Relationship Manager]
    FROM [Annual Review Table] INNER JOIN [Note Table] ON [Annual Review Table].[Annual Review Number] = [Note Table].[Annual Review Number];

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    fyi, an append query simply adds records to a table. if you're looking to update information that's already there, use an UPDATE query.

  3. #3
    kstyles is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2010
    Posts
    24
    what do you put in the update to field for the query?

  4. #4
    kstyles is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2010
    Posts
    24
    thank you - I got this one figured out!

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    How about posting your final solution for others that read this thread to see? You might also use the Thread tool and mark this thread as Solved.

  6. #6
    kstyles is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2010
    Posts
    24
    Good Idea.... here's what I used to update my table.

    UPDATE [Annual Review Table] INNER JOIN [Note Table] ON ([Note Table].Borrower=[Annual Review Table].Borrower) AND ([Annual Review Table].[Annual Review Number]=[Note Table].[Annual Review Number]) SET [Note Table].[Relationship Manager] = [Annual Review Table].[Relationship Manager];

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Thanks you and Happy New Year.

  8. #8
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    did I actually do anything helpful here? Hmm...kudos to me!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. labels duplicating themselves
    By RedGoneWILD in forum Forms
    Replies: 2
    Last Post: 08-17-2010, 07:42 AM
  2. Report duplicating itself many many times
    By TrudyD1474 in forum Reports
    Replies: 1
    Last Post: 06-24-2010, 11:59 AM
  3. duplicating and editing the record
    By Airis in forum Forms
    Replies: 1
    Last Post: 04-12-2010, 07:41 AM
  4. Problem with "join" duplicating records
    By Zukster in forum Queries
    Replies: 0
    Last Post: 08-25-2009, 09:00 AM
  5. Duplicating lines in an invoice report
    By GordonEdinburgh in forum Forms
    Replies: 0
    Last Post: 04-21-2007, 12:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums