Re: Query for non-matching records on two tables w/o keys

J Streger wrote:

I am trying to write a query in Access via SQL that takes two tables, neither
of which has a primary key (and I cannot give a primary key to one of the
tables as it's a thrid party table) and returns the records from PrintJobs1
that do not exist in PrintJobs. I intend on appending this data to PrintJobs,
but first I'm trying to get the select query down and the following seems to
return all the records in PrintJobs1:

SELECT PrintJobs1.Id, PrintJobs1.Copies, PrintJobs1.Printer,
PrintJobs1.Computer, PrintJobs1.Owner, PrintJobs1.Document,
PrintJobs1.SumittedDate, PrintJobs1.SumittedTime, PrintJobs1.TotalPages,
PrintJobs1.Cost, PrintJobs.Id, PrintJobs.Owner
FROM PrintJobs1 LEFT JOIN PrintJobs ON (PrintJobs1.SumittedDate =
PrintJobs.SubmittedDate) AND (PrintJobs1.SumittedTime =
PrintJobs.SubmittedTime) AND (PrintJobs1.Id = PrintJobs.Id) AND
(PrintJobs1.Owner = PrintJobs.Owner)
WHERE (((PrintJobs.Id) Is Null)) OR (((PrintJobs.Owner) Is Null)) OR
(((PrintJobs.SubmittedDate) Is Null)) OR (((PrintJobs.SubmittedTime) Is
Null));


You are using the wrong join, change it to a RIGHT JOIN

You can also remove all but one of the criteria. E.g.
WHERE PrintJobs.Id Is Null
is sufficient

--
Marsh
MVP [MS Access]
.