Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2002
    Location
    Ann Arbor, MI
    Posts
    47

    Question Unanswered: Query - Basing results on frequency per catagory

    I have a query (attached) that goes through entries in a table and filters by Date and Supplier. I am basing a report on this to analyze trends; This report is sorted by PartNumber, than by InspectionReportNumber(s). I want this query to return only entries that have GREATER than 1 InspectionReportNumbers per 1 PartNumber (I have allot of “onesies”). Ho can I do this? Thanks for your help!

    -Keith
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2001
    Location
    Estonia
    Posts
    33

    Re: Query - Basing results on frequency per catagory

    you can use "find duplicates query wizard" - analyze the query to see how that could be done

    Originally posted by NightZen
    I have a query (attached) that goes through entries in a table and filters by Date and Supplier. I am basing a report on this to analyze trends; This report is sorted by PartNumber, than by InspectionReportNumber(s). I want this query to return only entries that have GREATER than 1 InspectionReportNumbers per 1 PartNumber (I have allot of “onesies”). Ho can I do this? Thanks for your help!

    -Keith

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT InspecReportNumber
         , InspectionReportTABLE.[Date]
         , PartNumber
         , Description
         , Supplier
         , QAComments
         , DispDeviation
         , DispDeQty
         , DispSort
         , DispSoQty
         , DispRework
         , DispRewQty
         , DispScrap
         , DispScQty
         , DispReturn
         , DispRetQty
         , DispUseAs
         , DispUseQty
         , approvMfg
         , approvMfgDate
         , approvEng
         , approvEngDate
      FROM InspectionReportTABLE
     WHERE InspectionReportTABLE.[Date]
              Between [Type the date of the beginning of the month] 
                  And [Type the ending date]
       AND Supplier="SYR"
       AND PartNumber in
           ( select PartNumber
               from InspectionReportTABLE
           group by PartNumber
             having count(*) > 1 )
    ORDER 
        BY PartNumber
    rudy

  4. #4
    Join Date
    Sep 2002
    Location
    Ann Arbor, MI
    Posts
    47
    Thanks Rudy! I have been playing around with your sql code and I just can't see any differances between the two (yours and mine). Again, what I am looking for is to return all PartNumbers that have two or more InspectionReportNumbers >= 0.

    Alec, I have not yet tried the wizard due to a control-freak IT dept. not wanting users to have access to advanced wizards. Thanks for your help though ...

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    um, where did the ">= 0" condition come from? that wasn't there originally, was it?

    Code:
    AND PartNumber in
           ( select PartNumber
               from InspectionReportTABLE
             where InspectionReportNumber >= 0
           group by PartNumber
             having count(*) > 1 )

  6. #6
    Join Date
    Sep 2002
    Location
    Ann Arbor, MI
    Posts
    47
    Rudy, I figured it out - Your query worked, however the structure of the WHERE statement was giving me unexpected results:

    WHERE InspectionReportTABLE.[Date]
    Between [Type the date of the beginning of the month]
    And [Type the ending date]
    AND Supplier="SYR"
    AND PartNumber in
    ( select PartNumber
    from InspectionReportTABLE
    group by PartNumber
    having count(*) > 1 )

    The first two parts ("Between" and "Supplier=") were filtering after your "select" statement. I made a seperate query to do these first, then ran yours against this new query. Is there any way to effect the order of execution in the WHERE statement?

    Thanks for your help. -Keith
    Last edited by NightZen; 11-13-02 at 13:09.

Posting Permissions

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