Results 1 to 9 of 9
  1. #1
    llgtjb001 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Sep 2015
    Posts
    33

    how can I overwrite the previous database?

    Currently, I want to update my database for the newest one. I have two databases. One is A2, and another is A3.
    Click image for larger version. 

Name:	A2.png 
Views:	31 
Size:	17.0 KB 
ID:	23629
    A2 is the newest database, and it has 3 records.
    Click image for larger version. 

Name:	A3.png 
Views:	31 
Size:	21.0 KB 
ID:	23630
    A3 is the previous database, and it has 4 records.

    How can I overwrite the A3 to as same as A2(same record numbers)?
    Thank you.
    I use SQL as
    Code:
    INSERT INTO A3 ( Type, Name, Strength, Production, Code, Sales)SELECT DISTINCT A2.Type, A2.Name, A2.Strength, A2.Production, A2.Code, A2.Sales
    FROM A2 INNER JOIN A3 ON A2.Code=A3.Code
    WHERE A2.Code Is Null;


  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    MS Access does *not* have "record Numbers" so not real sure what you mean. It also appears that you are referring to *tables* in your database rather than different databases.

  3. #3
    llgtjb001 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Sep 2015
    Posts
    33
    Quote Originally Posted by RuralGuy View Post
    MS Access does *not* have "record Numbers" so not real sure what you mean. It also appears that you are referring to *tables* in your database rather than different databases.
    Well, do I say data?
    Because I often external data as the new database, A2 will be the new one. You know, that is why there are two tables instead of database.

  4. #4
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Would you overwrite all duplicate data? Not knowing the structure of the database It would look like theres no Unique key for the tables. You could Update one with the contents of the other, but on what grounds.
    You might be able to Union the first table to the last table. and then only return the last occurences of a record in aggregate functions. Maybe send that data to a make table query.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    llgtjb001,

    Readers do not understand your post. Please tell us in plain English WHAT you are trying to do.
    Give sample data showing before and after if it helps.

    A database contains Tables which contain Fields. Fields can be assigned values....

  6. #6
    llgtjb001 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Sep 2015
    Posts
    33
    Quote Originally Posted by Perceptus View Post
    Would you overwrite all duplicate data? Not knowing the structure of the database It would look like theres no Unique key for the tables. You could Update one with the contents of the other, but on what grounds.
    You might be able to Union the first table to the last table. and then only return the last occurences of a record in aggregate functions. Maybe send that data to a make table query.
    I want to the A3 table as same as A2 table, and I think the "Code" is the unique key. When I update, only data will be update, the total number cannot be same. That is why I want to solve.

  7. #7
    llgtjb001 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Sep 2015
    Posts
    33
    Quote Originally Posted by orange View Post
    llgtjb001,

    Readers do not understand your post. Please tell us in plain English WHAT you are trying to do.
    Give sample data showing before and after if it helps.

    A database contains Tables which contain Fields. Fields can be assigned values....
    A3 table will be the same as A2 table.
    What functions do I use?

  8. #8
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    could it be you want to add new records and update the existing ones to be the same as the newly imported? In this case you'll need an outer join in your insert code (...FROM A2 RIGHT OUTER JOIN A3 ON ...) and after executing this statement create a second update statement using an inner join.

  9. #9
    llgtjb001 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Sep 2015
    Posts
    33
    Quote Originally Posted by NoellaG View Post
    Hi,

    could it be you want to add new records and update the existing ones to be the same as the newly imported? In this case you'll need an outer join in your insert code (...FROM A2 RIGHT OUTER JOIN A3 ON ...) and after executing this statement create a second update statement using an inner join.
    ok. I will try that, as you say, I want to add new records to existing table.
    Thx.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-17-2013, 08:53 PM
  2. Replies: 2
    Last Post: 03-14-2013, 04:43 AM
  3. Replies: 8
    Last Post: 11-12-2012, 11:10 AM
  4. Alumni database: Storing previous employers
    By Yogibear in forum Database Design
    Replies: 2
    Last Post: 10-30-2012, 04:58 PM
  5. Getting the Previous Data from the database.
    By rjbautista20 in forum Programming
    Replies: 2
    Last Post: 09-17-2012, 10:14 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