[AccessD] query problem

Gary Kjos garykjos at gmail.com
Wed Apr 26 10:17:42 CDT 2017


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


More information about the AccessD mailing list