Results 1 to 5 of 5

Thread: Query Help

  1. #1
    Join Date
    May 2003
    Posts
    9

    Unanswered: Query Help

    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

  2. #2
    Join Date
    May 2003
    Posts
    87

    Re: Query Help

    Code:
    select distinct a.lngArn
    from tblArn a, tblAnalysis b
    where a.lngArn = b.lngArn
       and b.ysnApproved = 'Y'
    /
    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

  3. #3
    Join Date
    May 2003
    Posts
    9
    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

  4. #4
    Join Date
    May 2003
    Posts
    87
    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 ???

    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

  5. #5
    Join Date
    May 2003
    Posts
    9
    Thanks for your help dbmadcap Ive managed to get it working now

    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •