Get Unique Records Between Two Tables

[EnginerdUNH]

Hi,

I am working with two different tables tblSampleMain and tblSamplePull where tblSamplePull is a temporary table where data gets pulled into the database and manipulated before being added into tblSampleMain. What I am trying to do now is write a query to extract the unique records from tblSamplePull (i.e. the records that only appear in tblSampleMain). I was able to write following query to extract the unique ID numbers from tblSamplePull but I can't figure out how to expand it to include the other columns in the table:

Code: Select all

SELECT DISTINCT SampleNumDate
FROM (SELECT SampleNumDate FROM tblSamplePull
UNION ALL
Select SampleNumDate FROM tblSampleMain
)  AS a;

I also tried doing it a different way using the following query that relies on the where condition that the SampleNumDate values are not equal but that didn't work:

Code: Select all

SELECT tblSamplePull.SampleNumDate, tblSamplePull.SampleNumber, tblSamplePull.SampleSupplier, tblSamplePull.SamplePriority, tblSamplePull.SampleReceived, tblSamplePull.SampleEngineer, tblSamplePull.SamplePeer, tblSamplePull.SampleVTS, tblSamplePull.SampleTaskOwner, tblSamplePull.SampleTaskStart, tblSamplePull.SampleTaskECD
FROM tblSamplePull LEFT JOIN tblSampleMain ON tblSamplePull.[SampleNumDate] = tblSampleMain.[SampleNumDate]
WHERE ((tblSampleMain.SampleNumDate) Is Null))
ORDER BY tblSamplePull.SampleNumDate;

[HansV]

You write

"records from tblSamplePull (i.e. the records that only appear in tblSampleMain)"

I don;t understand that - if a record only occurs in tblSampleMain it does not appear in tblSamplePull.

The second SQL will return

"records from tblSamplePull (i.e. the records that do not[/url] appear in tblSampleMain)