Distinct IDs by last contact date?

[Gingertrees]

Trying to query a call list to pull people who haven't been contacted since a certain date. So if my date is 10/10/2012, I want all people who's last contact was 10/10, 10/1, et al. I do not want to see people who were called by 10/10 but have been contacted since. Likewise, if multiple calls were made, I only need to see John Doe ONCE.

Tables are consumers and calls. Call date = CDate. Callbackdate = when they should (have) been called back.

Here's the query's SQL. Currently this pulls one record for each consumer who was called by the specified date, but does NOT check whether or not they have been contacted since.

SELECT DISTINCT Consumers.Phone, Consumers.CName, Consumers.Address, Consumers.City, Consumers.State, Consumers.Zip, Consumers.[Serial #], Consumers.Timezone, Calls.Notes, Calls.User, Calls.Callbackdate, Calls.CDate
FROM Consumers INNER JOIN Calls ON Consumers.ID = Calls.ConsumerRecord
WHERE (((Calls.CDate)<[End Date?]))
ORDER BY Calls.User;

 

[jackd]

Can you show the values for a few typical records?
You mention should have been called back, but How do you determine "has been called since"?
Do you really have a "fieldname with a ?" ?

 

[Gingertrees]

My fieldname is CDate, the message box simply states "End Date?"
As long as there is a call back date, there should exist a record of a call on said callback date.

Abbreviated list:
Consumers.Phone,Consumers.Name,Consumers.Serial,Calls.Callbackdate,Calls.CDate,Calls.User
8881112233,John Doe,SN12345,10/25/12,10/24/12,Jen
5658889999,Jack Black,SN87654,10/4/12,10/3/12,Jen
5658889999,Jack Black,SN87654,10/5/12,10/4/12,Jen
9012223333,Amy Tan,SN22222,10/9/12,10/8/12,Tom
7776665555,Tim Kitts,SN88998,10/10/12,10/9/12,Tom
9012223333,Amy Tan,SN22222,10/10/12,10/9/12,Tom
7776665555,Tim Kitts,SN88998,,10/10/12,Tom

Say this is all that I've got - this shows all calls that Tom and Jen have made. As long as there is a call back date, there should exist a record of a call on said callback date.
Now the first one - that's after 10/10 - so I don't want to see it in my query.
The 2nd one, Jack Black - he was called twice, but there's still a call back date that doesn't match any later data. So I'd want to see ONE instance of his info with the latest existing CDate (call date).
Amy Tan I want to see one of her, because there's no record of a call on 10/10 for her (but a callback date says there should have been).
Tim Kitts: his last call left a null callback date. So I don't need to see him in my results.

 

[jackd]

I reviewed your data and made a mock up.

I created 2 tables (since your data didn't include the Id and Ref I made them up to satisfy your data).

Calls
field_name	data_type

Ref		Long	
Callbackdate	Date	 
CDate		Date	 
User		Text	 


Consumers
field_name	data_type

ID		Long	
Phone		Text	
Name		Text	
Serial		Text	


I also created 2 queries

CallBacksParmQuery

SELECT Calls.Ref, Calls.Callbackdate
FROM Calls
WHERE (((Calls.Callbackdate)<=[End Date?] Or (Calls.Callbackdate) Is Null));

CallDatesParmQuery

SELECT Calls.Ref, Calls.CDate
FROM Calls
WHERE (((Calls.CDate)<=[End Date?]));

And then put them together to make a final query

CallBacksWithNoCallOnCallBackDateOrNull

SELECT DISTINCT Consumers.Phone, Consumers.Name
, Consumers.Serial, Calls.User, CallBacksParmQuery.Callbackdate
FROM ((Calls INNER JOIN Consumers ON Calls.Ref = Consumers.ID) INNER JOIN CallBacksParmQuery ON Consumers.ID = CallBacksParmQuery.Ref) LEFT JOIN CallDatesParmQuery ON (CallBacksParmQuery.Callbackdate = CallDatesParmQuery.CDate) AND (CallBacksParmQuery.Ref = CallDatesParmQuery.Ref)
WHERE (((CallDatesParmQuery.Ref) Is Null) AND ((CallDatesParmQuery.CDate) Is Null))
ORDER BY Calls.User;

The final output

Phone Name Serial User Callbackdate
5658889999 Jack Black SN87654 Jen 10/5/2012
7776665555 Tim Kitts SN88998 Tom
9012223333 Amy Tan SN22222 Tom 10/10/2012

I hope this is useful to you.