6 Replies Latest reply on Mar 14, 2012 6:00 AM by jayankurian

    How do I compare the records contained in two identical databases?

    jayankurian

      Hi all,

      I do have two identical databases.(ditto). Database A has 12,987 records and Database B has 14,280 records.

      What I want to do is, to produce a list of records from Database B those are not contained in Database A.

       

      In other words:

      Database B has all the records contained in Database A, but need to generate a list of records those are not part of Database A.

       

      In my example I should get a list of 1293 records if and when I run a script.

       

      I don't mind creating another table called Database C to hold the reocords which generate by running the script.

       

       

      THERE IS A UNIQUE FIELD IN THE DATABASE

       

      Thank you

        • 1. Re: How do I compare the records contained in two identical databases?
          woytovich

          Define a relationship from a to b by your unique key field. To a got to related records in B. Show omitted in B and you'll have the "missing" records.

          • 2. Re: How do I compare the records contained in two identical databases?
            jayankurian

            Thank you Woytovich. It sounds like a good idea.

            What you meant by "To a got to related records in B"?

            Do you mind clarifying this part for me?

             

            Do I need to construct a script for this?

             

            Thanks

            Jayan

            1 of 1 people found this helpful
            • 3. Re: How do I compare the records contained in two identical databases?
              jimb01

              A solution would depend on whether or not the "UNIQUE Field" is "real" and not just sequential numbers generated independently within each database.

               

              If the Table in Database B is just a later version of Table A - there wouldn't be a problem - just use B -- although this doesn't sound like your situation.

               

              When I say Real, I really mean that the key should exist externally and should somehow be identifiable as unique to the object that exists outside the context of your database. For example a person's SSN or a serial number stamped on a computer would be universally observable and you might reasonably expect that two databases recording information about the object, would use the same key to identify a specific object.

               

              Jim

              • 4. Re: How do I compare the records contained in two identical databases?
                woytovich

                Yes... a script that performs a "Go to related record" step.

                • 5. Re: How do I compare the records contained in two identical databases?
                  michaelward

                  If Database A and Database B have a unique identifier associated with each record, and Database B is simply a later version of Database A, then I would suggest:

                   

                  1. Create Database C with two fields: "Unique ID Database B" and "Unique ID Database A" (define this second field to Look-up the value from Database A if Database B's Unique ID matches Database A's Unique ID). Make sure that the mode of the fields ("Text" or "Number") matches the Unique ID field definition in both Database A and Database B.

                   

                  2. Import all the Unique IDs from Database B (all 14,280 records). Check the dialogue box which says perform look-ups etc. as you are importing the records.

                   

                  3. All the records with blanks in Database C's field: "Unique ID Database A" are new records. As you say, there should be 1,293 of them (14,280 - 12,987 = 1,293).

                   

                  If there are not, then something else is up and you should double check that each record has indeed been assigned a unique identifier (sort the records in Database A numerically or alphabetically so that blank records rise to the top; perform the same operation on Database B.)

                   

                  Of course, this doesn't mean that the records in the 2 databases "match" one another, only their unique identifiers. But that's another problem.

                   

                  If the two databases are identical, but have been run independently of one another, you'll need to compare each database field by field.

                  • 6. Re: How do I compare the records contained in two identical databases?
                    jayankurian

                    Thank you woytovich and michaelward. Both methods worked for me.

                     

                    Thanks again