an access query for a confused banker

[jh8735]

I use Access to run reports from various pieces of financial data. The
one query that I have been unable to produce, but need is the following:

A query that yields all accounts from a person that has a loan account
with us but no deposit account. Luckily, all loan information is kept
in a 'loan' table and deposit is kept in a 'deposit' table with a few
common fields such as account number.

What I tried doing was to pull 'account number' from 'loan' and
'deposit' tables and use NULL for deposit info. and IS NOT NULL for loan
info so that records with valid Loan info. that was without deposit info
would be pulled.

This yielded results, but not the type that I was looking for because
John Doe, for instance, can have 5 accounts and if only one of those
accounts satisfies this critera, then John Doe's name will be pulled.
This query can only be useful if ALL of the records associated with John
Doe fall under this criteria otherwise one record will and one record
won't and his name will be displayed when it shouldn't.

Have I thoroughly confused everyone? I know this doesn't make much
sense, but if anyone can point me in the right direction, I'd be a
happy man.

Thanks
--
Posted via http://dbforums.com

[Steve Jorgensen]

Create and save one query that gets all loan accounts grouped by person-id
(one record per person), and with no other columnns. Create and save
another query that gets all deposit accounts grouped by person-id, and with
no other columns. Now, make another query that outer joins from the fist
query to the second, and check for Null in the person-id column from the
second.

This gets one intance of each relevant person-id - I think you can take it
from there.

On Mon, 15 Sep 2003 15:27:54 -0400, jh8735 <me*********@dbforums.com>
wrote:

I use Access to run reports from various pieces of financial data. The
one query that I have been unable to produce, but need is the following:

A query that yields all accounts from a person that has a loan account
with us but no deposit account. Luckily, all loan information is kept
in a 'loan' table and deposit is kept in a 'deposit' table with a few
common fields such as account number.

What I tried doing was to pull 'account number' from 'loan' and
'deposit' tables and use NULL for deposit info. and IS NOT NULL for loan
info so that records with valid Loan info. that was without deposit info
would be pulled.

This yielded results, but not the type that I was looking for because
John Doe, for instance, can have 5 accounts and if only one of those
accounts satisfies this critera, then John Doe's name will be pulled.
This query can only be useful if ALL of the records associated with John
Doe fall under this criteria otherwise one record will and one record
won't and his name will be displayed when it shouldn't.

Have I thoroughly confused everyone? I know this doesn't make much
sense, but if anyone can point me in the right direction, I'd be a
happy man.

Thanks

[PC Datasheet]

From what you have posted, you need to create an unmatched records query. Go to
queries, click on new and select Find Unmatched Query Wizard. In the first
screen select your loan table as having the records you want. When you click
next, select your deposit table as having the related records. Click next again
and select account number in both tables and then click on the double arrow
button. Click next again and choose the fields you want to see in the results.
When you click Finish you will get all the people that have a loan account with
you but no deposit account.
--
PC Datasheet
A Resource for Access, Excel and Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

· Design and basic development for new applications
· Additions, Modifications and "Fixes" for existing applications
· Mentoring for do-it-yourselfers who want guidance
· Complete application design and development
· Applications Using Palm Pilot To Collect Data And
Synchronize The Data Back To Access Or Excel
"jh8735" <me*********@dbforums.com> wrote in message
news:33****************@dbforums.com...

I use Access to run reports from various pieces of financial data. The
one query that I have been unable to produce, but need is the following:

A query that yields all accounts from a person that has a loan account
with us but no deposit account. Luckily, all loan information is kept
in a 'loan' table and deposit is kept in a 'deposit' table with a few
common fields such as account number.

What I tried doing was to pull 'account number' from 'loan' and
'deposit' tables and use NULL for deposit info. and IS NOT NULL for loan
info so that records with valid Loan info. that was without deposit info
would be pulled.

This yielded results, but not the type that I was looking for because
John Doe, for instance, can have 5 accounts and if only one of those
accounts satisfies this critera, then John Doe's name will be pulled.
This query can only be useful if ALL of the records associated with John
Doe fall under this criteria otherwise one record will and one record
won't and his name will be displayed when it shouldn't.

Have I thoroughly confused everyone? I know this doesn't make much
sense, but if anyone can point me in the right direction, I'd be a
happy man.

Thanks
--
Posted via http://dbforums.com