help with criteria

From: Ted Allen (anonymous_at_discussions.microsoft.com)
Date: 02/19/04 

Date: Thu, 19 Feb 2004 10:35:07 -0800

Hi Jill,

You may want to try the following. I think it is
somewhat similar to your subquery idea, but I don't use
subqueries much so I'm not much help with spotting the
potential problem there. I'm sure there is a more elegant
way to do this in sql, but I think this should work in
case no one else posts a better solution.

You could try first creating a query that joins the
members and loan info for only the loan types that you
want to exclude. Then, create a second query based on
the member info and the first query. Then, make sure
that the join between them is set to select all records
from the members table, and only those from the first
query where there is a match. Add the member info from
the table, but also add the member ID from the first
query, and set the criteria for that field to "Is Null"
(without the quotes of course). This should give you a
list of all members that did not have any of the excluded
loan types.

Hope that helps. Post back if it was off the mark.

-Ted Allen
>-----Original Message-----
>I am creating a query joining two tables. The 'memb'
>table contains member names and addresses and the 'loan'
>table has loan info associated with the members. A
single
>member/acct# could have numerous loan codes. I want my
>results to be only members who DO NOT have certain loan
>codes associated with them acct#. I thought a subquery
>would do the job, but it leaves in the member if they
>have any other loan codes.
>
>Select distinct memb.acct
>from memb, loan
>where memb.rowno=loan.rowno_membloan_memb
>AND ((memb.acct) Not In (select acct from memb where
>loan.rowno_membloan_memb=memb.rowno And loan.code="he"
or
>loan.code="hc"or loan.code="1m" or loan.code="um" or
>loan.code="fm"))
>
>.
>

Relevant Pages

  • How to model/test some financial data
    ... in terms of their ability to get a loan at the lowest possible rate. ... dependant variables are percentage of loan funded (by other ... P1 - Borrowers that are members of groups are more likely to get ...
    (sci.stat.math)
  • Re: My opinion of the BN Change Group
    ... a loan was also made to Steve Thompson 'Official' ... Nor am I suggesting that the expenditure in the 2005 accounts labelled ... Are BN members to take your above statement Duncan that you think the above ...
    (uk.rec.naturist)
  • The Mick Ayers thing again
    ... At CCBN's AGM this coming Sunday the EC will be doing their best to kill off any discussion of the Mick Ayers loan non-payment scandal. ... one thing members will be voting on is membership fees. ... An understandable viewpoint, especially as CCBN's history of actually sticking to a budget is very chequered, and their constant U turns on the matter of Mick Ayers. ...
    (uk.rec.naturist)
  • Re: My opinion of the BN Change Group
    ... a loan was also made to Steve Thompson 'Official' ... Are BN members to take your above statement Duncan that you think the above ... to post an opinion that differs from theirs or seems to support BN, ...
    (uk.rec.naturist)
  • Re: Trying to work out a join
    ... there is no subquery: ... Members.MemberID NOT IN (SELECT Members.MemberID FROM Members INNER ... If I use the query above, ... >> table then it doesnt return the user via that query. ...
    (microsoft.public.inetserver.asp.db)
Quantcast