473,325 Members | 2,712 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Finding no detail record....

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
2 1692
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Joop | last post by:
Hello everyone, I'm new on this list and have been working for quite a while on a PHP app that will make an HTML frontend for a PostgreSQL dbms. Works fine so far but I'm having a little trouble...
0
by: Luke Airig | last post by:
I am using the Saxon engine and I have an xml file that contains batches of records. Each batch starts with a header record and the associated detail records immediately follow each header. There...
3
by: Helgardh | last post by:
I have a linked table (Access 2003) to an Outlook inbox. The body of the e-mail messages are in a memo field. My problem is that I need to "read" the memo and find data on certain lines. The...
2
by: RogBaker | last post by:
I have a report in MS Access 2000. In the Detail section of the Report, I have the following record information. TextBox:Name TextBox:Address1 TextBox:Address2 TextBox:CityStateZip...
7
by: john | last post by:
In my form I have a master table and a details table linked 1xM. I can search through the whole parent table but I also like to be able to search through the child table fields to find parent...
9
by: Allan Ebdrup | last post by:
I would like to use reflection to find all classes that inherit from my current class, even if they are in another assembly I want to find them if the current project has a reference to that...
275
by: Astley Le Jasper | last post by:
Sorry for the numpty question ... How do you find the reference name of an object? So if i have this bob = modulename.objectname() how do i find that the name is 'bob'
1
by: ramaswamynanda | last post by:
Hello, I have a simple vb.net 2005 application using an SQLserver database There is a form , employees that has a grid view and a detail view control The grid displays data and has a "Select"...
1
by: ccmanc68 | last post by:
I'm needing to print reports by the record detail, Meaning if I had five records on the detail subform, I could click print and would get five reports with the same headers "Parent RecordID" but the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.