By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,247 Members | 1,427 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,247 IT Pros & Developers. It's quick & easy.

an access query for a confused banker

P: n/a

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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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


Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.