AND query

[clk]

I am working with a SQL tables linked into Access. I have no control over set up of the table. I need to run a report limiting the data. What I want to accomplish:


Fields in table: Client Number, Client Name, Case Type

Filter Field: Case Type (wc, ss, pi, auto, etc.)

I need to filter all the records where a client has a WC AND a SS case in the system. I can filter WC or SS but that is not what I need. I have tried running separate queries, AND in the filter row.

Any help is appreciated.

[Bob Barrows]

Post by clk
I am working with a SQL tables linked into Access. I have no control
over set up of the table. I need to run a report limiting the data.
Fields in table: Client Number, Client Name, Case Type
Filter Field: Case Type (wc, ss, pi, auto, etc.)
I need to filter all the records where a client has a WC AND a SS
case in the system. I can filter WC or SS but that is not what I
need. I have tried running separate queries, AND in the filter row.
Any help is appreciated.

where [case type] in ('wc','ss')
or
where ( [case type] = 'wc' or [case type] = 'ss')

They both mean the same thing.

Caveat: this will return clients who have either case type. If you want a
list of clients who have both types, then you will need subqueries:

select [client number] ...
from table
where
[client number] in (select [client number] from table where [case type] =
'wc'')
and
[client number] in (select [client number] from table where [case type] =
'ss'')

[clk]

Post by Bob Barrows

Post by clk
I am working with a SQL tables linked into Access. I have no control
over set up of the table. I need to run a report limiting the data.
Fields in table: Client Number, Client Name, Case Type
Filter Field: Case Type (wc, ss, pi, auto, etc.)
I need to filter all the records where a client has a WC AND a SS
case in the system. I can filter WC or SS but that is not what I
need. I have tried running separate queries, AND in the filter row.
Any help is appreciated.

where [case type] in ('wc','ss')
or
where ( [case type] = 'wc' or [case type] = 'ss')
They both mean the same thing.
Caveat: this will return clients who have either case type. If you want a
select [client number] ...
from table
where
[client number] in (select [client number] from table where [case type] =
'wc'')
and
[client number] in (select [client number] from table where [case type] =
'ss'')

Thank you for the reply. I will give it a try. I did try the "In" option and like you said I got both. I need it if only they have something under both types. I will try the subqueries you suggested. Thanks again...

[clk]

Post by Bob Barrows

Post by clk
I am working with a SQL tables linked into Access. I have no control
over set up of the table. I need to run a report limiting the data.
Fields in table: Client Number, Client Name, Case Type
Filter Field: Case Type (wc, ss, pi, auto, etc.)
I need to filter all the records where a client has a WC AND a SS
case in the system. I can filter WC or SS but that is not what I
need. I have tried running separate queries, AND in the filter row.
Any help is appreciated.

where [case type] in ('wc','ss')
or
where ( [case type] = 'wc' or [case type] = 'ss')
They both mean the same thing.
Caveat: this will return clients who have either case type. If you want a
select [client number] ...
from table
where
[client number] in (select [client number] from table where [case type] =
'wc'')
and
[client number] in (select [client number] from table where [case type] =
'ss'')

OK....I have three queries. One to get all the SS cases, one to get all the WC cases and one that shows cases. How do I set up the query to only give me ones that have both? I am stuck. I have tried joining the queries different ways but nothing seems to work.

[John W. Vinson]

Post by clk
OK....I have three queries. One to get all the SS cases, one to get all the WC cases and one that shows cases. How do I set up the query to only give me ones that have both? I am stuck. I have tried joining the queries different ways but nothing seems to work.

You need only ONE query: the one Bob posted:

select [client number] <whatever fields you want to see> from table
where
[client number] in
(select [client number] from table where [case type] = "wc'')
and
[client number] in
(select [client number] from table where [case type] = "ss'')

If that's not working, please post back with the exact SQL that you're using,
and perhaps one of the queries that IS working.