Problems with queries

  • Thread starter Thread starter Jan
  • Start date Start date
J

Jan

Any advice regarding my query problems gratefully received:
I have three tables (customer, correspondence and a linking table).

TblCustomer (CustomerName,CustomerID)
TblCustomerID_Correspondence(CustomerID,CorrespondenceID)
TblCorrespondence(CorrespondenceId,CorrespondenceType)

I am trying to keep track of the correspondence that I send to each
customer.
In my CorrespondenceType field (where type = letter or email), there is data
showing that some customers have been sent emails, some have been sent
letters, some have been sent both and some have been sent no correspondence.
CorrespondenceID is 1 and 2 for letters and 3 for emails.
I am trying to write a query which allows me to see all the customers who
have not been sent a letter. The problem is that some customers have been
sent a letter and an email, thereby having two records in the linking table
eg

Customer CustomerID CorrespondenceID CorrespondenceType

Fred 1 1 Email

Fred 1 2 Email

Fred 1 3
Letter

Sam 2 1 Email

Sam 2 3 Letter


If I write a query where the CorresponenceType <> letter, the results still
show a record for customer 1, because even though they have been sent a
letter they have also been sent an email.
Could someone please tell me how I go about querying my data so that I can
see only those customers who have not been sent a letter at all?
Many thanks
Jan
 
Jan

I may not completely understand your data or what you are trying to do, so
take this with a grain ...

It sounds like you are trying to find Customers who don't have a record with
(1 or 2?) a letter.

One way to approach this is to first find all those who DO, then compare
that list to the full set of customer correspondence for the "Unmatched"
ones. (check the query wizard for "unmatched")
 
Back
Top