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]

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.

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]

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.

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'')

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]

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.

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'')

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]

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.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com