Criteria in a single field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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]
 
Back
Top