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

[Guest]

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));

Any help would be greatly appreciated. I want to get through this week with
hair left on my head.


--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003

 

[Marshall Barton]

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

 

[Guest]

I tried that. Changed PrintJobs1 to CZPrintJobs and PrintJobs to PAPrintJobs

Now I return no Records. There are 43 records in CZPrintJobs that are not in
PAPrintJobs. They don't have the same columns but the 4 in the query are in
both, and they are the same data as I copied them from CZ to PA to be sure.
Same data style etc. I tried changing my null property to any of the 4 and
still not returning any records.

SELECT CZPrintJobs.Id, CZPrintJobs.Copies, CZPrintJobs.Printer,
CZPrintJobs.Computer, CZPrintJobs.Owner, CZPrintJobs.Document,
CZPrintJobs.SumittedDate, CZPrintJobs.SumittedTime, CZPrintJobs.TotalPages,
CZPrintJobs.Cost, PAPrintJobs.Id
FROM CZPrintJobs RIGHT JOIN PAPrintJobs ON (CZPrintJobs.Owner =
PAPrintJobs.Owner) AND (CZPrintJobs.Id = PAPrintJobs.Id) AND
(CZPrintJobs.SumittedDate = PAPrintJobs.SubmittedDate) AND
(CZPrintJobs.SumittedTime = PAPrintJobs.SubmittedTime)
WHERE (((PAPrintJobs.Id) Is Null));
--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003

 

[John Spencer]

As much as I dislike disagreeing with Marshall.

You do need a LEFT JOIN to return the records in CZPrintJobs and not a
RIGHT JOIN. Other than that I see nothing wrong with the query you have
posted.

If you make the change are you still returning all the records in
CZPrintJobs?



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Marshall Barton]

I tried that. Changed PrintJobs1 to CZPrintJobs and PrintJobs to PAPrintJobs

Now I return no Records. There are 43 records in CZPrintJobs that are not in
PAPrintJobs. They don't have the same columns but the 4 in the query are in
both, and they are the same data as I copied them from CZ to PA to be sure.
Same data style etc. I tried changing my null property to any of the 4 and
still not returning any records.

SELECT CZPrintJobs.Id, CZPrintJobs.Copies, CZPrintJobs.Printer,
CZPrintJobs.Computer, CZPrintJobs.Owner, CZPrintJobs.Document,
CZPrintJobs.SumittedDate, CZPrintJobs.SumittedTime, CZPrintJobs.TotalPages,
CZPrintJobs.Cost, PAPrintJobs.Id
FROM CZPrintJobs RIGHT JOIN PAPrintJobs ON (CZPrintJobs.Owner =
PAPrintJobs.Owner) AND (CZPrintJobs.Id = PAPrintJobs.Id) AND
(CZPrintJobs.SumittedDate = PAPrintJobs.SubmittedDate) AND
(CZPrintJobs.SumittedTime = PAPrintJobs.SubmittedTime)
WHERE (((PAPrintJobs.Id) Is Null));

I seem to be all twisted around with this problem and it
appears that I was looking at the wrong table. Now I think
the LEFT JOIN was correct so change that back.

If it's consistent with your original query you should be
back to the original problem. If you still get all the
records from table CZPrintJobs, it implies that there are no
records that match all four of the ON clause conditions.

At this point I suggest that you drop those conditions one
at a time to see which one(?) is causing all the trouble.
Since the CZPrintJobs table is coming from another app,
check the time and date fields first. Maybe they are text
fields in one table and real date/time fields in the other??

 

[Marshall Barton]

As much as I dislike disagreeing with Marshall.

John, I would like you to feel obligated to disagree when
it's clear that I am wrong. Hopefully, not too often ;-)

 

[John W. Vinson]

At this point I suggest that you drop those conditions one
at a time to see which one(?) is causing all the trouble.
Since the CZPrintJobs table is coming from another app,
check the time and date fields first. Maybe they are text
fields in one table and real date/time fields in the other??

The other problem with a time field is that a Date/Time is stored as a Double
Float number; the times are displayed to the second, but actually stored with
sub-microsecond precision. It's possible that, if the data was entered in a
different way in the two tables (as is likely), that #12:31:18# in one table
might not be exactly the same as #12:31:18# in the other - and not match.

It may be necessary to join on a Format() expression casting the time into a
string.

John W. Vinson [MVP]

 

[Guest]

That is why I copied the data from one table to the other manually, deleted
some records and tried the query with the problems. Of course I also changed
the query to the Right Join at the same time, so thus why no records.

Changing the query to the LEFT JOIN again, now results in the 48 records
missing and the correct output I wanted.

I fell into the bad practice of changing multiple things, rather than one at
a time. My science teachers would have failed me for that But thank you.
It seems that both ensuring the data was the same (I was sure it was the
date/time that would be probelms too) and the LEFT JOIN did the trick. Thank
you both!
--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003