Results 1 to 10 of 10
  1. #1
    frankmanl is offline Novice
    Windows 11 Office 365
    Join Date
    Jan 2023
    Posts
    9

    copy data from one table to another

    I've got two tables A and B, both with indentification field ID. I want to merge them.
    Table B has some fields (say a, b and c) which I want to copy into table A, resulting in a complete table A, after which I can delete table B.
    The data of table B record with ID XXX must of course be copied to the corresponding record in table A with the same ID XXX.

    The SQL statement I try to use is:
    INSERT INTO A (a, b, c)
    SELECT a, b, c
    FROM B
    WHERE B (ID) = A (ID);
    But this causes an error "The expression contains an undifined function B"
    After trying several things, I concluded that the B refers to the B in WHERE B (ID) = A (ID)

    How can I get this done?
    Can it be done in a single query? Or should I use a differrent approach?
    Any help is much appreciated.
    Frank

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    You appear to be missing a full stop for B.ID and A.ID ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    The error is because you must use WHERE B.ID = A.ID

    But I doubt this is what you want. With INSERT you will add new records to table A (causing duplicate ID's). You need an UPDATE query.
    Groeten,

    Peter

  4. #4
    frankmanl is offline Novice
    Windows 11 Office 365
    Join Date
    Jan 2023
    Posts
    9
    Thanks for your replies, guys.
    I changed my statement to

    UPDATE A
    SET a = (SELECT a FROM B WHERE A.ID = B.ID);

    This statement is accepted, but when I run it Access gives a warning "Operation must use an updatable query" ("De bewerking moet worden uitgevoerd op een query die kan worden bijgewerkt").
    I checked my database settings, it's not read only.
    The two tables I use are not connected / dependent in any way ...
    So what's going wrong now?
    Frank

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Are these ID fields both autonumber type? Makes no sense to link on autonumber fields. If tables have no common identifier, how can you link them?

    If you want to do an INSERT of records, why would you link tables? Do you want some records to be updated and some to be inserted? Either run two action SQL statements or do what is called an UPSERT. Review https://stackoverflow.com/questions/...g-in-ms-access
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    frankmanl is offline Novice
    Windows 11 Office 365
    Join Date
    Jan 2023
    Posts
    9
    Hi June7, no, they are not autonumber type. The ID field in both tables is numeric. I imported those tables from another application and want to combine them in one Access table.

    The tables are not linked - I mentioned this because the Access warning refered to https://learn.microsoft.com/nl-nl/of...ueryerror-3073 which talks about tabels in a one-to-many relationship.
    And I don't want to INSERT but UPDATE - as I found out after xps35's reply.

    So if you can help with the problem in my last statement

    UPDATE A
    SET a = (SELECT a FROM B WHERE A.ID = B.ID);

    I'm happy to hear from you.
    Frank

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    From your original description, I'm also unclear whether you should be using INSERT or UPDATE or both.
    To update existing records, use UPDATE.
    However, if there are additional records in B that aren't in A, you will also need to use INSERT

    The subquery isn't needed in your update query. Use
    UPDATE A INNER JOIN B ON A.ID = B.ID SET A.a = [B].[a];

    The [] brackets can probably be omitted.

    However, to fix the updateable query error you may need to specify unique records.
    If so, change the SQL to
    UPDATE DISTINCTROW A INNER JOIN B ON A.ID = B.ID SET A.a = [B].[a];
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    frankmanl is offline Novice
    Windows 11 Office 365
    Join Date
    Jan 2023
    Posts
    9
    Thank you all.
    isladogs's solution was what I needed:
    UPDATE A INNER JOIN B ON A.ID = B.ID SET A.a = [B].[a];

    Frank

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    We were all glad to help
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    The ID fields are common identifier and using INNER JOIN - appears tables have a 1-to-1 relationship.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 08-29-2018, 10:26 AM
  2. Copy data from one table to another
    By sdel_nevo in forum Queries
    Replies: 2
    Last Post: 06-15-2017, 07:43 AM
  3. Copy data in table
    By Tommo in forum Access
    Replies: 3
    Last Post: 09-27-2015, 07:23 AM
  4. How to copy data within the same table?
    By price12 in forum Access
    Replies: 1
    Last Post: 04-23-2014, 08:34 PM
  5. copy data from one table to another
    By Sureshbabu in forum Access
    Replies: 1
    Last Post: 01-08-2012, 01:27 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