By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

Finding no detail record....

P: n/a
Hi,

I'm developing a CRM (Customer Relationship Management) system in MS
Access. I have 2 tables. The first is a customer master file, the
second is an activity file.

A key function of the system will be to track when it is time to
follow up with a customer. Sometimes it's for a specific reason, other
times it's just a routine "keep in touch" contact.

In the activity file, there is a True/False field where when the field
is true, the follow up activity is complete.

My challenge is that the user might sometimes forget to create a
follow up activity. Just scrolling through the file, it's easy to see
when this happens - the customer master record has no activity file
record that is not marked complete. With a lot of customers however,
one might get missed.

I want to query the database and present the user with a list of
customers that don't have an open activity record. It seems like I
need an "EXCEPTION JOIN" but I don't see any such functionality. How
would I create a query that would tell me what customers don't have a
"False" activity complete flag record?

Thank You!

Paul
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
pa**@laflammeconsulting.com (Paul) wrote in
news:a1**************************@posting.google.c om:
Hi,

I'm developing a CRM (Customer Relationship Management) system
in MS Access. I have 2 tables. The first is a customer master
file, the second is an activity file.

A key function of the system will be to track when it is time
to follow up with a customer. Sometimes it's for a specific
reason, other times it's just a routine "keep in touch"
contact.

In the activity file, there is a True/False field where when
the field is true, the follow up activity is complete.

My challenge is that the user might sometimes forget to create
a follow up activity. Just scrolling through the file, it's
easy to see when this happens - the customer master record has
no activity file record that is not marked complete. With a
lot of customers however, one might get missed.

I want to query the database and present the user with a list
of customers that don't have an open activity record. It seems
like I need an "EXCEPTION JOIN" but I don't see any such
functionality. How would I create a query that would tell me
what customers don't have a "False" activity complete flag
record?

Thank You!

Paul

Easy fix.

SELECT customerID
FROM customers
WHERE customer NOT IN
(SELECT customer
FROM activity
WHERE NOT complete
)
;

Bob Q
Nov 12 '05 #2

P: n/a
Bob Quintal <bq******@generation.net> wrote in message news:<d0******************************@news.terane ws.com>...
pa**@laflammeconsulting.com (Paul) wrote in
news:a1**************************@posting.google.c om:
Hi,

I'm developing a CRM (Customer Relationship Management) system
in MS Access. I have 2 tables. The first is a customer master
file, the second is an activity file.

A key function of the system will be to track when it is time
to follow up with a customer. Sometimes it's for a specific
reason, other times it's just a routine "keep in touch"
contact.

In the activity file, there is a True/False field where when
the field is true, the follow up activity is complete.

My challenge is that the user might sometimes forget to create
a follow up activity. Just scrolling through the file, it's
easy to see when this happens - the customer master record has
no activity file record that is not marked complete. With a
lot of customers however, one might get missed.

I want to query the database and present the user with a list
of customers that don't have an open activity record. It seems
like I need an "EXCEPTION JOIN" but I don't see any such
functionality. How would I create a query that would tell me
what customers don't have a "False" activity complete flag
record?

Thank You!

Paul

Easy fix.

SELECT customerID
FROM customers
WHERE customer NOT IN
(SELECT customer
FROM activity
WHERE NOT complete
)
;

Bob Q


Thanks Bob, it worked like a charm!!

Paul
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.