Query Help

JohnnieP · #1 (**permalink**) 06-23-03, 11:35

Hi All,

Im currently finding it really difficult to get my head around a query and was hoping someone could help.

First table tblArn
Second Table tblAnalysis

The tblAnalysis and tblArn tables are linked by field lngArn.
The other field in the tblAnalysis table Im interested in is ysnApproved.

The lngArn field can appear multiple times in the tblAnalysis table as any ARN can have multiple analyses assigned to it. Which means particular analyses for an ARN may not have been approved.

The query I have to produce is to list "all" ARN's where "all" analysis results for this ARN have been approved.

Any help would be greatly appreciated,

Thanks

John

dbmadcap · #2 (**permalink**) 06-23-03, 14:36

Code:

select distinct a.lngArn
from tblArn a, tblAnalysis b
where a.lngArn = b.lngArn
   and b.ysnApproved = 'Y'
/

Quote:

Originally posted by JohnnieP
Hi All,

Im currently finding it really difficult to get my head around a query and was hoping someone could help.

First table tblArn
Second Table tblAnalysis

The tblAnalysis and tblArn tables are linked by field lngArn.
The other field in the tblAnalysis table Im interested in is ysnApproved.

The lngArn field can appear multiple times in the tblAnalysis table as any ARN can have multiple analyses assigned to it. Which means particular analyses for an ARN may not have been approved.

The query I have to produce is to list "all" ARN's where "all" analysis results for this ARN have been approved.

Any help would be greatly appreciated,

Thanks

John

JohnnieP · #3 (**permalink**) 06-24-03, 04:15

Thanks for your help.

I tried the query but what happens is I get ARN's where some of its analyses approved fields are set to 'No'. I guess it picks the field up as some of its other approved fields are set to 'Yes'.

What Im trying now is:

create query Analysis Approved
create query Analysis Non-Approved
create join on these 2 queries

Do some kind of join on these queries so I only get ARN numbers where all its Analyses are in the approved section. I should then in theory be able to use this joined query to get the data I need.

Sorry if this isnt that clear Im a bit new to this SQL mallarky

John

dbmadcap · #4 (**permalink**) 06-24-03, 11:03

Here is the query to get only the Approved rows :

Code:

select distinct a.lngArn
from tblAnalysis
where ysnApproved = 'Y'
  and lngArn in
  (select lngArn
   from tblAnalysis
   group by lngArn
   having count(distinct ysnApproved) = 1
/

To get the Non-Approved, simply change ysnApproved = 'Y' to ysnApproved = 'N'.

Is this what you wanted ???

Quote:

Originally posted by JohnnieP
Thanks for your help.

I tried the query but what happens is I get ARN's where some of its analyses approved fields are set to 'No'. I guess it picks the field up as some of its other approved fields are set to 'Yes'.

What Im trying now is:

create query Analysis Approved
create query Analysis Non-Approved
create join on these 2 queries

Do some kind of join on these queries so I only get ARN numbers where all its Analyses are in the approved section. I should then in theory be able to use this joined query to get the data I need.

Sorry if this isnt that clear Im a bit new to this SQL mallarky

John

JohnnieP · #5 (**permalink**) 06-24-03, 11:06

Thanks for your help dbmadcap Ive managed to get it working now

John

Internet Services

Web Development

Digital Marketing

Consumer Tech