Run Queries on Related Tables
sharsy
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]
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
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
"sharsy" <sharibr...@gmail.com> wrote in message
news:95737106-bd6b-4c0b...@m33g2000pri.googlegroups.com...
PieterLinden via AccessMonster.com
Something like this should work:
SELECT Members.MemberID, Members.FirstName, Members.LastName, Calls.Reason
FROM Members INNER JOIN Calls ON Members.MemberID = Calls.fromMemberID
WHERE (((Calls.Reason) Is Null));
Or if you need to be able to edit Members, then...
SELECT Members.MemberID, Members.FirstName, Members.LastName
FROM Members
WHERE (((Members.MemberID)=(SELECT fromMemberID FROM Calls WHERE Reason IS
NULL)));
The answer depends on your business requirements. What are you going to do
with the data returned?
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1
John Spencer
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
(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? :)