SQL - Append Query (1 Viewer)

[Cotty42]

Hi All

I have a master dataset which has approx 8500 patient records and have just inheritted another, similar dataset with approx 2500 patient records, many of which are duplicated.

As part of this inherited dataset I have a table of test results some of which exist in the master dataset and some don't.

Typically say I have patient P1, P2 and P3, all of which wxist in both datasets.

In the master dataset P1 has test results T1, T2, & T3 and in the inherited dataset has test results T1, T2, T3, T4, T5, T6 & T7. But they don't necessarily correspond, i.e. Master T1 = Inherited T1, Master T2 = Inherited T4, Master T3 = Inherited T7. So I only want to append T2, T3, T5 & T6 to the Master table. - All tests are sorted on date.

Each table has fields PatientID, TestDate, TestResult, Comment
So I want to join the tables on PatientID and then check for non-matching TestDate in the inherited table and append those records where there is no matched record to the master table.

I have probably not explained this very clearly (its been a long day) - but any help gratefully accepted.

Thanks
Dave

 

[plog]

You would link your two tables via the PatientID and TestDate fields. Use a LEFT JOIN on both links from the inherited table into the master table (show all from inherited, show only matches form master). Then you bring down all the fields from the inherited table and the PatientID field from the master. Under master.PatientId you set the criteria to 'Is Null'.

That query identifies all records in inherited that are not in the master.

 

[Cotty42]

Thanks Plog - Worked a treat

Cheers
Dave