[AccessD] query problem

[Rocky Smolin]

Yes, I think that will work.  But it's making my brain hurt. :)

R


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Gary Kjos
Sent: Wednesday, April 26, 2017 8:18 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] query problem

As Bob already replied, I think you just need an outer join and test the key
on the table or query that has the rows you DON'T want for null on the key.
If it's null that means there ISN'T a matching row in that table so you DO
want that row in your results.  If it's not null there is a match and you do
not want it in your result set.

On Tue, Apr 25, 2017 at 8:26 PM, Rocky Smolin <rockysmolin at bchacc.com>
wrote:
> 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



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