Group By?? Group ID?? What do I do?

[RuBot]

I have an Access table designed as follows:

branch name, name, account number, major type, minor type, status, balance,
date.

The 'major types' are the types of accounts. I need to find out who has an
account type of savings, but no checkings. Further queries are variations of
this first query.

For example records might look like
Denver, Bob, 11111, SAV, EFP, Active, 10.00, 9/27/05
Denver, Bob, 22222, CK, EFP, Active, 50.00, 09/22/05
Texas, Fred, 123123, SAV, EFP, Active, 1.00, 02/02/99
Danville, Dan, 312312, CK, EFP, Active, 12.01, 09.12.99

The query should see that only Fred has a savings account and not a checking
account, and this should be the only record displayed. How do I properly group
to test the conditional?

[Dan Bracuk]

So far you don't have to group anything.

select * from the_table
where major_type = 'SAV'
and name not in
(select name from table
where major_type = 'CK')

By the way, the spaces in your tablenames will cause you more trouble than
they are worth. You should rename them.

Originally posted by: RuBot
I have an Access table designed as follows:

branch name, name, account number, major type, minor type, status, balance,
date.

The 'major types' are the types of accounts. I need to find out who has an
account type of savings, but no checkings. Further queries are variations of
this first query.

For example records might look like
Denver, Bob, 11111, SAV, EFP, Active, 10.00, 9/27/05
Denver, Bob, 22222, CK, EFP, Active, 50.00, 09/22/05
Texas, Fred, 123123, SAV, EFP, Active, 1.00, 02/02/99
Danville, Dan, 312312, CK, EFP, Active, 12.01, 09.12.99

The query should see that only Fred has a savings account and not a checking
account, and this should be the only record displayed. How do I properly group
to test the conditional?

[RuBot]

Actually the field names are a little confusing so I just wrote them out for
better understanding.

Here's my query, and here's my error message:
<cfquery name = "SavNoCk" datasource="TopAccounts">
select * from acctinfostripped
where mjaccttypcd = 'SAV'
and name not in
(select name from table where mjaccttypcd = 'CK')
</cfquery>

"Syntax error. in query expression 'mjaccttypcd = 'SAV' and name not in
(select name from table where mjaccttypcd = 'CK')"

[paross1]

You didn't use the proper table name in your sub-select. Also, using a NOT
EXISTS may be more efficient.

<cfquery name = "SavNoCk" datasource="TopAccounts">
SELECT *
FROM acctinfostripped AS a
WHERE a.mjaccttypcd = 'SAV'
AND NOT EXISTS
(SELECT 1
FROM acctinfostripped AS a1
WHERE a1.mjaccttypcd = 'CK'
AND a1.name = a.name)
</cfquery>

Phil