Help with a query...Have a 1 to many relationship need help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the Contact Management template from the Office templates area.
The contacts table has a one to many relationship with the calls table. I am
trying to create a query that will list only contacts that have NOT been
called within 45 days. The query I have so far using the sample data in the
template shows every contact and each call that is older than 45 days. So, I
use distinct to get rid of duplicate contacts, but each contact is still
returned by the query if ANY call is older than 45 days.

I guess I need something that says...Only show contacts that have NO call
activity for the last 45 days. I'm probably not making sense, but I would
appreciate any help.

Scott Bridges
 
Scott

If you start with the query you have (all contacts with calls older than 45
days), you could create another query, based on it, that looks for unmatched
contacts in the Contacts table (roughly, show me the Contacts that are NOT
in the list of those called in the last 45 days).

Or have I misunderstood...
 
Thanks for you reply Jeff. I'm not being too clear. Each month I call or
write to a contact. These entries are the 'many' side of the one to many
relationship. So for instance my contact 'Joe' is one record in the Contacts
table. He has many records in the Calls table (lets say one for each month
dated 8/1/04, 9/5/04 and 10/3/04). Now I want to query the database for
anyone that I have not called or written to in the last 45 days. I guess I'm
having trouble understanding how to build the query to ask the right
question. I don't want to query for calls older than 45 days. I want to query
for anyone that doesn't have a call record newer than 45 days. I get lost in
the logic of how to structure the query.

Thanks again for your help. You MVPs are the best.
 
try

SELECT Contacts.ContactID, FirstName, LastName, WorkPhone, WorkExtension,
MobilePhone, FaxNumber
FROM Contacts LEFT JOIN (SELECT ContactID FROM Calls WHERE
CallDate>=Date()-45) As TimeFrame
ON Contacts.ContactID = TimeFrame.ContactID
WHERE TimeFrame.ContactID Is Null;

hth
 
Amazing! Tina, thank you so much! That is exactly what I was trying to do. I
wasn't even close with query I had put together.

Thanks Tina and thank you also Jeff. I appreciate everyone's help.
 
you're very welcome. i just did what Jeff suggested, only using a subquery
instead of two queries - which is a technique i learned here in the
newsgroups. :)
 
Back
Top