Results 1 to 12 of 12

Thread: [RESOLVED] How to find a record with same lastname and firstname and copy their ID Numbers?

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Resolved [RESOLVED] How to find a record with same lastname and firstname and copy their ID Numbers?

    I have a two(2) access databases, one(1) for the source and the other one(1) is for the destination dbase. The table of a source database and destination database have the same three(3) fields or columns(e.g. FirstName,LastName, ID_Number). The source database ID_Number field/column has value while in destination database ID_Number field/column is blank and only the FirstName and LastName has. I just want to find the records in database(source) with that same LastName and FirstName and if found then their ID_Numbers values will be copied to database(destination).The scenario is in just one click of a button all those records(from source) with same names in (destination) ,their ID_Numbers will be copied to database(destination) ID_Number field/column.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: How to find a record with same lastname and firstname and copy their ID Numbers?

    Quote Originally Posted by ronelpisan View Post
    The scenario is in just one click of a button
    It's one click of a button from the user's perspective but it's a multistage process from the developer's perspective. Have you given any thought to what those stages are and how to implement each one or are you just waiting for someone else to do the whole thing for you? Helping with actual problems is one thing but you're just basically asking us to do your work for you. Maybe give it a go and post back when you run into a specific issue. Break the problem down into the smallest parts possible and tackle each part individually. That way, you may be able to handle some parts on your own and you can ask about a part you have a problem with while you get on with other parts.

  3. #3
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,034

    Re: How to find a record with same lastname and firstname and copy their ID Numbers?

    you could Import the DBase File to your Access Database and compare there

    here a Link how to Import, goto Post#6
    http://www.vbforums.com/showthread.p...-to-a-dbf-file
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  4. #4

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Re: How to find a record with same lastname and firstname and copy their ID Numbers?

    Quote Originally Posted by jmcilhinney View Post
    It's one click of a button from the user's perspective but it's a multistage process from the developer's perspective. Have you given any thought to what those stages are and how to implement each one or are you just waiting for someone else to do the whole thing for you? Helping with actual problems is one thing but you're just basically asking us to do your work for you. Maybe give it a go and post back when you run into a specific issue. Break the problem down into the smallest parts possible and tackle each part individually. That way, you may be able to handle some parts on your own and you can ask about a part you have a problem with while you get on with other parts.
    Sorry I did not included my initial input. Here is my code but it only works when they are in the same/inside database. What I want is it will also work in another database. Any suggestion please in my connection string.

    Code:
    Dim connstring As String
            connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=sample.accdb"
            Dim conn As OleDbConnection = New OleDbConnection(connstring)
            Dim updatescardexpasdate1 As String = "UPDATE Table1 INNER JOIN Table2 ON Table1.FirstName = Table2.First_Name AND Table1.LastName = Table2.Last_Name  SET Table1.ID_Number = Table2.ID_Number"
            Dim cmdupdate As New OleDbCommand(update, conn)
            conn.Open()
            cmdupdate.ExecuteNonQuery()
            conn.Close()

  5. #5
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,832

    Re: How to find a record with same lastname and firstname and copy their ID Numbers?

    In MSSQL the databases can be linked and then in the join you would fully qualify the database/table names. Does Access allow for linked databases?
    Please remember next time...elections matter!

  6. #6
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: How to find a record with same lastname and firstname and copy their ID Numbers?

    A more basic question... Can the Last_name, First_name combination be duplicated? If so then how do you decide which one to use?
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  7. #7

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Re: How to find a record with same lastname and firstname and copy their ID Numbers?

    No. All combinations of names are unique in values.

  8. #8

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Re: How to find a record with same lastname and firstname and copy their ID Numbers?

    I will try to link it.

  9. #9
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,034

    Re: How to find a record with same lastname and firstname and copy their ID Numbers?

    Quote Originally Posted by ronelpisan View Post
    I will try to link it.
    why not just use SQL
    Code:
    SELECT * INTO...
    from one Database to the other
    then perform your Update and Delete the Imported Table once you don't need it anymore with
    Code:
    Drop Table...
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  10. #10
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,832

    Re: How to find a record with same lastname and firstname and copy their ID Numbers?

    Quote Originally Posted by ChrisE View Post
    why not just use SQL
    Code:
    SELECT * INTO...
    from one Database to the other
    then perform your Update and Delete the Imported Table once you don't need it anymore with
    Code:
    Drop Table...
    He said with a click of a button so I took that to mean from an application. That solution sounds more like a manual operation to me, albeit it could be done in an application.
    Please remember next time...elections matter!

  11. #11
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,034

    Re: How to find a record with same lastname and firstname and copy their ID Numbers?

    Quote Originally Posted by TysonLPrice View Post
    He said with a click of a button so I took that to mean from an application. That solution sounds more like a manual operation to me, albeit it could be done in an application.
    well I don't think it will matter if you Link a Table to the other Access DB (which can be done with ADOX) or just
    manually copy that Table for the Update and delete it again.

    I'm just wondering why or how the Data got in a mess? or is it no mess and he just want's to sort and rearange the Data once
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  12. #12

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Re: How to find a record with same lastname and firstname and copy their ID Numbers?

    Quote Originally Posted by ChrisE View Post
    you could Import the DBase File to your Access Database and compare there

    here a Link how to Import, goto Post#6
    http://www.vbforums.com/showthread.p...-to-a-dbf-file
    Thanks. Importing and linking the database make it works.

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
  •  



Click Here to Expand Forum to Full Width