3 Replies Latest reply on Jun 16, 2011 10:47 AM by denno

    matching records from 2 different data sources

    denno
    denno Jun 16, 2011 10:15 AM

      Title

      matching records from 2 different data sources

      Post

      I am setting up a database to track patients admitted to the hospital with pneumonia. There are 2 different data sources that I need to match up to find patients who are missing a specific piece of data.

      Datasource #1 is a listing of the admitted pneumonia patients and contains patient name, ID#, and [bloodtest] date/time.

      Datasource #2 is a list of all patients who had [bloodtest] perfomed and contains name, ID# and [bloodtest] date/time.

      I need to find patients who exist in datasource #1 and have no entry for [bloodtest] date/time but who have that information listed in datasource #2.

      I have separate tables set up for each datasource and but am not sure if there's a way to set up a relationship that will display what I'm looking for.

      • 78 Views
      • Tags:
        • 1. Re: matching records from 2 different data sources
          philmodjunk
          philmodjunk Jun 16, 2011 10:22 AM (in response to denno)

          Why do you have two data source tables with redundant data?

          You can start with this relationship:

          Datasource1::ID# = Datasource2::ID#

          THen, from a layout based on Datasource1, perform a find for all records in Datasource 1 that meet your criteria by entering find mode and putting an = in Datasource1::BloodTest and an asterisk in Datasource2::BloodTest. If you perfom this find by hand, you'll need to add the BloodTest field from DataSource2 to this layout so that you can enter criteria into it.

          • 2. Re: matching records from 2 different data sources
            mgores
            mgores Jun 16, 2011 10:36 AM (in response to denno)

            Is ID# the patient's ID or the database's record ID?  If it is the patientID and it would be the same for a given person entered from either data source, Phil's answer would work.  If not then you would have to match by name, which would be nearly impossible since one data source could enter Robert Smith and the second enter Bob Smith or there could be multiple people named Bob Smith.

            • 3. Re: matching records from 2 different data sources
              denno
              denno Jun 16, 2011 10:47 AM (in response to denno)

              Thanks. ID is the patient's ID.

              The datasource files are reports from 2 different sources--one is hospital records and the other is from the lab.