Criteria in a single field

D

Deb H

I have two tables. The primary table lists the ID and name, address, etc of
each client. The related table lists each account held by a client, a unique
account number, and the account type, such as checking or savings. I need to
select records for clients who have a checking account type, but not a
savings account type. Account type is a single field. How can I type the
criteria in my query to show these records? I am using Access 2003. TIA.
 
J

John Vinson

I have two tables. The primary table lists the ID and name, address, etc of
each client. The related table lists each account held by a client, a unique
account number, and the account type, such as checking or savings. I need to
select records for clients who have a checking account type, but not a
savings account type. Account type is a single field. How can I type the
criteria in my query to show these records? I am using Access 2003. TIA.
Click to expand...

You'll need a somewhat more complex query to do this, since each
individual record in the account table is just one type of account.
Try:

SELECT <whatever fields you want>
FROM [Primary]
INNER JOIN [Accounts]
ON [Primary].[ID] = [Accounts].[ID]
WHERE [Accounts].[Type] = "Checking"
AND NOT EXISTS
(SELECT ID FROM [Accounts] AS X
WHERE [X].[ID] = [Primary].[ID]
AND [X].[Type] = "Savings");


John W. Vinson[MVP]
 
Top