Results 1 to 4 of 4

Thread: Query Dbt

  1. #1
    Join Date
    Jun 2003
    Posts
    2

    Red face Unanswered: Query Dbt

    I have a dbt da in sql statement for Access

    I have a table with

    Production table with fields(Date1,FILE,MT_ID,NAME,LINE_COUNT,QUALITY)
    This table has got lots of entries for a particular date itself
    Example : 1/6/2003 456777887 4023 SINDHU 89.0 98%
    1/6/2003 78889899 4023 SINDHU 78 95%

    Now I want to create a seperate table with the fields

    DATE -- which has just the unique dates in them
    NAME-unique user
    MT_ID --unique ID
    TOTAL LINE COUNT -- The total line count for unique employees for unique date
    AVERAGE ACCURACY -- The average accuracy for unique employee for unique date
    LINECOUNT BELOW 98% -- Total No. of Line Count for the unique employee for unique date whose ACCURACY IS BELOW 98%

    So for this i wrote a query which gave me

    UNIQUE DATE
    UNIQUE NAME
    TOTAL LINE COUNT
    AVERAGE ACCURACY

    This is the query

    SELECT DATE1,NAME,SUM(LINE_COUNT) AS LCOUNT,AVG(QUALITY) AS ACCURACY FROM Production GROUP BY DATE1,NAME

    but i need the MT_ID and LINECOUNT BELOW 98% too

    Pls let me know how the subquery will be

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, you realize that for each DATE1,NAME combination, there will be multiple rows, so which value of MT_ID do you want? or is it a new field?

    also, i don't understand LINECOUNT BELOW 98% -- could you please explain it again?


    rudy
    http://r937.com/

  3. #3
    Join Date
    Jun 2003
    Posts
    2

    Unhappy Explanation to Query

    Table1 -- Production

    fields(Date1,FILE,MT_ID,NAME,LINE_COUNT,QUALITY)

    DATE FILE MT_ID NAME LINE COUNT ACCURACY
    1/6/2003 456777887 4023 SINDHU 89.0 98%
    1/6/2003 78889899 4023 SINDHU 78 95%
    2/6/2003 67899 2344 RETA 789 100%
    ...........................
    ...............

    Now I want to insert into the table ----Daywise_Report

    FIELDS(DATE1 , MT_ID , Name , TLine_Count,AV_Accuracy, LCB_98)

    Where we have to find the total line count and average accuracy for each employees for each date and insert into TLine_Count and AV_Accuracy

    And also find the LCB_98 ie -- TOTAL(LINE_COUNT) who's ACCURACY is less than 98%

    And insert to appropriate fields
    Hope U can help me out
    Thanks
    regards
    Smitha

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if there are multiple MT_IDs in each group, you need to decide how to choose just one of them, i.e. which one

    in addition, i still don't understand which lines in each group should be counted if the group's accuracy is less than 98% -- all the lines in a group go towards calculating the group average, no?

    i am afraid this is as far as i can help you --

    Code:
    insert 
      into Daywise_Report
         ( DATE1 
         , MT_ID 
         , Name 
         , TLine_Count
         , AV_Accuracy
         , LCB_98 )
    select DATE1
         , MT_ID
         , NAME
         , SUM(LINE_COUNT) 
         , AVG(QUALITY) 
         , 0          
      from Production 
    group
        by DATE1
         , NAME

Posting Permissions

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