[AccessD] query problem

[Rocky Smolin]

I think you're right.  I'll try that.

r

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bob
Gajewski
Sent: Wednesday, April 26, 2017 4:52 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] query problem

It sounds like you need to include an "and not exists" against a subquery of
all students that have participated in a Society course. You can use that
<or> a left exclusionary join

SELECT <select_list>
FROM TableA A
LEFT JOIN TableB B
ON A.Key = B.Key
WHERE B.Key is NULL
                                  

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Rocky Smolin
Sent: Tuesday, April 25, 2017 21:27 PM
To: 'Access Developers discussion and problem solving'
<accessd at databaseadvisors.com>
Subject: [AccessD] query problem

Dear List:

 

My client's company markets courses to CPAs for continuing education.  The
courses can be sponsored either by an Accounting Firm or a state Accounting
Society. 

 

I have a SQL statement which is the record source for a form - rather
complex because there's a whole form full of filters which the user can
select - and those WHEREs become added on to the basic record source of the
form.  That has been working well. It produces a list of people in
accounting firms who have been participants in one or more of these  courses
for CPAs based on the filtering criteria selected by the user. 

 

Now comes another request from the client to exclude from this list anyone
who has participated in a course sponsored by a  Society (as opposed to an
Accounting Firm) because he doesn't want to market to the Accounting Firms
and undercut the Societies that have already gotten a participant from the
Accounting Firm

 

So I have devised a query that yields a list of Accounting Firms that have
sent participants to a Society sponsored course. But I can't figure out how
to get this list to suppress any participant who belongs to an Accounting
Firm in this query.

 

Normally I would use the temp table approach and reconstruct the table after
every request to change the filters.  But I'm pretty far down this road of
altering the record source of the form by incorporating into the SQL the
various WHEREs representing the filters.  And it's fast that way as opposed
to deleting and filling a temp table with records that pass all the tests.

 

There's an Unmatched Query Wizard which I'm thinking of trying. But is there
an easy solution to this?

 

MTIA

 

Rocky

 

 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com