help with criteria

[Jill]

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"))

 

[Ted Allen]

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

 

[Ted Allen]

Hi Jill,

In the earlier message I had relayed a two-step process
(two queries), where the first would define the records
with members that you wanted to exclude, and the second
would then only select members not included in the first
query.

I'm a little unsure which query sql you sent, but I would
assume that it was the first. If that is the case, it
looks like you would be wanting to exclude all records
in "CO", and you are joining the two tables on a field
called rowno. This looks like it should work fine
provided rowno in the Memb/loan table is a reference to
the member id in the member table (I assume that rowno in
that table is the member ID). But, this is different
than my understanding of your original question because
the state field is in the member table, rather than the
loan types that you wanted to exclude originally. So, I
am kind of confused. Please post back with more detail
of what you are trying to do and I will try to help if I
can.

By the way, I'm off tomorrow, and I'll be leaving in 2
1/2 hours today, so if I don't get back to you this
afternoon I won't be able to until Mon, but maybe someone
else will.

-Ted Allen

-----Original Message-----
I ended up with more than twice the records I had in the

1st query. I think I followed your instructions
correctly, but probably screwed on some detail. Here is
my script:

SELECT [Memb/loan].acct, dbo_memb.name,

dbo_memb.lastname, dbo_memb.addr1, dbo_memb.addr2,
dbo_memb.city, dbo_memb.state, dbo_memb.zip1

FROM [Memb/loan] RIGHT JOIN dbo_memb ON

[Memb/loan].rowno = dbo_memb.rowno

 

[Guest]

Hi Ted

Thanks for all of your help! I ended up creating a query that joined the memb table to the loan table. I then used the code column from loan and the acct column from memb, showing only the loan codes that I want to ultimately exclude. Then I created a new query and used the select statement from the 1st query in the criteria of acct. The criteria is now [acct Not In (SELECT dbo_memb.acc
FROM dbo_memb INNER JOIN dbo_loan ON dbo_memb.rowno = dbo_loan.rowno_membloan_mem
WHERE (((dbo_loan.code)="HE" Or (dbo_loan.code)="HC" Or (dbo_loan.code)="FM" Or (dbo_loan.code)="1M" Or (dbo_loan.code)="UM"))] I was surprised when the data appeared correct. I thought I had tried something along these lines before, but I probably used <> instead of Not In.

Thanks for all of your help - I'm more of a PL/SQL user than an Access user, but I feel like with your help I've "beat" Access at its own game

 

[Ted Allen]

Hi Jill,

Glad that you got it working. I'm just the reverse of
you, I'm used to working in the Access environment and
not too strong at stringing together SQL statements, but
I'm slowly learning.

-Ted

-----Original Message-----
Hi Ted,

Thanks for all of your help! I ended up creating a

query that joined the memb table to the loan table. I
then used the code column from loan and the acct column
from memb, showing only the loan codes that I want to
ultimately exclude. Then I created a new query and used
the select statement from the 1st query in the criteria
of acct. The criteria is now [acct Not In (SELECT
dbo_memb.acct

FROM dbo_memb INNER JOIN dbo_loan ON dbo_memb.rowno = dbo_loan.rowno_membloan_memb
WHERE (((dbo_loan.code)="HE" Or (dbo_loan.code)="HC" Or

(dbo_loan.code)="FM" Or (dbo_loan.code)="1M" Or
(dbo_loan.code)="UM"))] I was surprised when the data
appeared correct. I thought I had tried something along

Thanks for all of your help - I'm more of a PL/SQL user

than an Access user, but I feel like with your help
I've "beat" Access at its own game