Run Queries on Related Tables

[sharsy]

Hello,

I have 2 Tables in Access(2003), Members and Calls. The two tables are
related (via a membership number) so that multiple calls can be made
to members and these are all recorded in the Calls table. In the Calls
table there is a field called 'Reason' with a limited number of drop-
down options.

My question is, how can run a query that identifies members who do not
have a particular Reason for a Call?

Kind regards,

SB

 

[Arvin Meyer [MVP]]

In a query, join the Members and Calls on the MemberID. Make sure that the
join shows all Member and any. Then select the fields that you want to see
from the members table. Now add the MemberID from the Calls table to the
query and add the Criteria: Is Null

 

[John Spencer]

The SQL for such as query could look something like the following:
SELECT Members.*
FROM Members
WHERE NOT Exists (SELECT * FROM Calls WHERE Reason = "xyz" and
Calls.MemberNumber = Members.MemberNumber)

In query design view
== add the Members table
== Add the fields you want to see
== In a blank field box enter
Exists (SELECT * FROM Calls WHERE [Reason] = "xyz"
and [Calls].[MemberNumber] = [Members].[MemberNumber])
== Enter False in the criteria under this calculated field

If this query is too slow, post back for alternate methods. AND state if you
need to be able to update the results.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[sharsy]

Thank you for your help so far!! Okay to clarify things a bit more
(mind you I'm not an advanced access user so I apologise in
advance ), we would like to schedule calls to our members at certain
intervals in their membership (30 days, 60 days, 7 months, 18 months
and every 12 months (for their renewal) - which is based on their
'Joining Date' in the Members table. In addition to these call
'Reasons' (e.g. '30 Days', 'Renewal' etc.), there are three other call
types (e.g. 'General') that may be entered into the database at
anytime.

Problem with creating reports showing the Scheduled calls (e.g. '30
Days'): I need a query that will tell me all the members who have yet
to have a '30 Days' call, but these people may potentially already be
on the Calls table (for a 'General' call etc.). I can get around this
issue however by getting the data entry girl to automatically create 4
blank calls with the scheduled timing points as these are one-off
calls. However, I was wondering if there was query criteria that would
save the creation of these blank calls.

Problem with creating report showing members up for their 12 month
'Renewal' (drop-down in the 'Reason' field) call: I need a query that
will tell me all the members who are due for renewal (based on their
'Renewal Month' field in the Members table which is entered when they
join - it simply has one of the 12 months in it e.g. 'May') and who
have yet to have a 'Renewal' call made to them within the last 2
months (in the Calls table there is a 'Date Made' field to capture the
date of calls). So I need query criteria that works off their 'Renewal
Month' field in the Members table, plus the 'Date Made' and 'Reason'
fields in the Calls table (because if a member has been with us for a
few years they could potentially have more than one 'Renewal' calls.

Does this make any sense?