SQL statement to compare two queries and only pull records not entered

Question

EDITED: Please look again.

`qryHistoryTimeLog`   /   `qryHistoryAppt`
34 , 03/07/17 , 170   /   34 , 01/13/17 , Checked Out and Complete
34 , 03/28/17 , 125   /   34 , 02/09/17 , Checked Out and Complete
34 , 04/28/17 , 140   /   34 , 03/07/17 , Checked Out and Complete
34 , 05/19/17 ,  85   /   34 , 03/28/17 , Checked Out and Complete
34 , 08/05/17 ,  75   /   34 , 04/28/17 , Checked Out and Complete
34 , 08/24/17 ,  65   /   34 , 05/19/17 , Checked Out and Complete
                      /   34 , 06/09/17 , Checked Out and Complete
                      /   34 , 08/05/17 , Checked Out and Complete
                      /   34 , 08/24/17 , Checked Out and Complete

The query qryHistoryAppt is pulling from the software's database table of all appointments that are for client #34 and are "Checked Out and Complete".

The query qryHistoryTimeLog is pulling from my table of entered information from the client's appointment.

I am looking for the appointment dates that have been Checked Out and Completed, but have not yet been entered into my tblTimeLog so I know which appointment cards need to be pulled and entered.

`qryTesting`
34 , 01/13/17
34 , 02/09/17
34 , 06/09/17

I also should add, that I am passing along the client number through this new query. So that when I run the query I can enter which client's appointments I am looking for.

ANSWERED:

SELECT DISTINCT qryHistoryAppt.petId, 
                qryHistoryAppt.petName, 
                qryHistoryAppt.cstLName, 
                qryHistoryAppt.aptDate
FROM qryHistoryAppt LEFT JOIN qryHistoryTimeLog ON 
    (qryHistoryAppt.petId = qryHistoryTimeLog.PetID) AND 
    (qryHistoryAppt.aptDate = qryHistoryTimeLog.ApptDate)
WHERE (((qryHistoryAppt.petId)=[]) AND
       ((qryHistoryTimeLog.ApptDate) Is Null));

Change it to a `LEFT JOIN` and then `Where [qryHistoryTimeLog].[ApptDate] IS NULL` Remove the `Group By` and `Having` — SS_DBA, Oct 23 '17 at 16:32
Not looking for a null. Comparing what dates from the software side are not entered into the tblTimeLog. — Scott Jethro Hardwick, Oct 23 '17 at 17:03
Possible duplicate of [How to select rows with no matching entry in another table?](https://stackoverflow.com/questions/4076098/how-to-select-rows-with-no-matching-entry-in-another-table) — Tab Alleman, Oct 23 '17 at 17:38

score 1 · Accepted Answer · answered Oct 24 '17 at 02:14

1

Simply LEFT JOIN on petId and ApptDate. Right now you are only matching against petID.

SELECT DISTINCT h.petId, h.aptDate
FROM qryHistoryAppt h
LEFT JOIN qryHistoryTimeLog l 
   ON h.petId = l.PetID AND h.aptDate = l.ApptDate
WHERE (((l.PetID) IS NULL));

answered Oct 24 '17 at 02:14

Parfait

104,375
17
94
125

Awesome!! Kudos!! – Scott Jethro Hardwick Oct 28 '17 at 16:50

score 0 · Answer 2 · answered Oct 23 '17 at 16:37

0

SELECT qryHistoryAppt.petId, 
         qryHistoryAppt.petName, 
         qryHistoryAppt.aptDate
FROM qryHistoryAppt LEFT JOIN qryHistoryTimeLog ON qryHistoryAppt.petId = qryHistoryTimeLog.PetID
WHERE qryHistoryTimeLog.PetID is null
GROUP BY qryHistoryAppt.petId, qryHistoryAppt.petName, qryHistoryAppt.aptDate
HAVING (((qryHistoryAppt.petId)=[]))
ORDER BY qryHistoryAppt.aptDate DESC;

answered Oct 23 '17 at 16:37

Max08

955
1
7
16

The Where statement is still not working right. All of the dates from the software side are not entered into the tblTimeLog. All I want to see are the dates that are missing from that table. – Scott Jethro Hardwick Oct 23 '17 at 17:01
could you take a new look at this – Scott Jethro Hardwick Oct 23 '17 at 23:25

SQL statement to compare two queries and only pull records not entered

2 Answers2