Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2011
    Posts
    66

    Unanswered: help in query to avoid duplicate records

    i have a query which contains two tables linked with table1 and table2

    imte master ( table name )
    IMTE NUMBER( field name ) monthofcal( another field name in table1 )
    asd101xyz jan
    asd101xyz jul
    asd102xyz jan
    asd103xyz jan

    CALIBRATION_CERTIFICATE ( table name )
    IMTE NUMBER( field name )
    asd101xyz
    asd102xyz
    asd103xyz

    both are linked as join properties "IMTE NUMBER"

    few records are repeated because some records have same number but "monthofcal " field value is different in table1,. i want the imte number to sort on imte number and monthofcal filed so that the record will not duplicate or repeat

    sql view

    SELECT [IMTE MASTER].[IMTE NUMBER], CALIBRATION_CERTIFICATE.[IMTE NUMBER], [IMTE MASTER].monthofcal, CALIBRATION_CERTIFICATE.STATUSCER
    FROM [IMTE MASTER] LEFT JOIN CALIBRATION_CERTIFICATE ON [IMTE MASTER].[IMTE NUMBER] = CALIBRATION_CERTIFICATE.[IMTE NUMBER];


    can any one help me how to do

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you eliminate the rows with duplicated IMTE_NUMBER values to only keep one value for each IMTE_NUMBER (this is called a GROUP BY operation, by the way), which value for monthofcal do you want to see in the resulting row set: the first one, the last one?

    Also shouldn't be the table with the monthofcal column be named CALIBRATION_CERTIFICATE and the other be the IMTE_MASTER table?
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    66

    good to see your reply sinndho thanks for it

    i dont want the imte number to be repeated in the reports

    if i select from reportform
    and click " calibration month status "
    it take few data form imte master table and calibration certificate table based on imte number field


    plant (p1)
    shop (qd)
    month(jan)

    few number repeat because in imte master table it has two entrys ( due to monthofcal ONE IS IN JAN OTHER IS IN JUN)

    i used the same code which you have geiven once
    Last edited by eddi; 10-18-11 at 08:33.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I can't help if you don't answer the question, sorry.
    Have a nice day!

  5. #5
    Join Date
    Sep 2011
    Posts
    66

    sinndho sorry for that

    sinndho sorry i did not understand your question as i dont know access even to that level

    in imte master table i have one field in which i have imte number ( Q10435f51)should be calibrated in jan & jul updated in field monthofcal so i have to put two entry in imte master table

    and when imte number ( Q10435f51) is calibrated in jan & jun it will come twice in the report form for both jan and jul month report

    when i click "Calibration month status" button in report form so how to stop the duplication of record

    i have attached the database in previous reply

    pls guide me or you have any suggestion

  6. #6
    Join Date
    Sep 2011
    Posts
    66

    sinndho i think this the answer to your question

    the imte number field data and monthofcal field data should match the
    calibration certificate field data imte number and next cal due date field ( month : 01/jan/2011 ) should match to the the monthofcal data

    imte master === calibraiton ceritficate ( tables )

    imte number === imte number ( field ) should match
    monthofcal === next cal due date ( field ) ( only month should match not day )

    pls tell how do GROUP BY operation

    i think you are right that is what i want

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No need to be sorry, I was simply stating a fact.

    My question simply was: If you want to eliminate one of the two (or more) rows with duplicate values in the field [imte number], which value to you want to keep for the field [monthofcal]: the first ('jan' in your example) or the last ('jul' in your example)?

    You need to define a rule concerning this because in a GROUP BY query (which is what we need to use here), the fields that are not grouped must be submitted to an aggregate function (SUM, MIN, MAX, AVG, etc) and I need to know which function I should use in the query.
    Have a nice day!

  8. #8
    Join Date
    Sep 2011
    Posts
    66

    sorry is for not trying to let you help me

    if i select in reportform

    plant (p1)
    shop (qd)
    month(jan)


    it should show the first ('jan' in my example)

    thanks
    Last edited by eddi; 10-23-11 at 10:12.

  9. #9
    Join Date
    Sep 2011
    Posts
    66

    and also

    if i select in reportform

    plant (p1)
    shop (qd)
    month(jul)

    Q09427F51

    it should show the first ('jul' in my example)

    because monthofcal it has ( jan and jul ) and next cal due date (if caldate is with in 01/jul/2011 it should match to jan 11 and if it is with in jan 12 it should match jul

    thanks

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution:
    1. Create a lookup table:
    Code:
    Table name:	Tbl_Month		
    =========================			
    Field name	Data type	Index			Constraint (validation rule)
    --------------------------------------------------------------------			
    Month_Number	Long		Primary key		Between 1 And 12
    Month_ShortName	Text		Yes (No Duplicates)	
    Month_FullName	Text
    2. To retrieve the Min value (jan) use:
    Code:
    SELECT a.IMTE_NUMBER, 
           Tbl_Month.Month_ShortName
        FROM (SELECT CALIBRATION_CERTIFICATE.IMTE_NUMBER, 
                     Min(Tbl_Month.Month_Number) AS MinOfMonth_Number
                  FROM CALIBRATION_CERTIFICATE 
                      INNER JOIN (IMTE_MASTER 
                          INNER JOIN Tbl_Month ON IMTE_MASTER.MonthOfCal = Tbl_Month.Month_ShortName) 
                      ON CALIBRATION_CERTIFICATE.IMTE_NUMBER = IMTE_MASTER.IMTE_NUMBER
                  GROUP BY CALIBRATION_CERTIFICATE.IMTE_NUMBER) AS a 
            INNER JOIN Tbl_Month ON a.MinOfMonth_Number = Tbl_Month.Month_Number
        ORDER BY a.IMTE_NUMBER, Tbl_Month.Month_ShortName;
    3. To retrieve the Max value (jul) use:
    Code:
    SELECT a.IMTE_NUMBER, Tbl_Month.Month_ShortName
    FROM (SELECT CALIBRATION_CERTIFICATE.IMTE_NUMBER, 
                     Max(Tbl_Month.Month_Number) AS MaxOfMonth_Number
                  FROM CALIBRATION_CERTIFICATE 
                      INNER JOIN (IMTE_MASTER 
                          INNER JOIN Tbl_Month ON IMTE_MASTER.MonthOfCal = Tbl_Month.Month_ShortName) 
                      ON CALIBRATION_CERTIFICATE.IMTE_NUMBER = IMTE_MASTER.IMTE_NUMBER
                  GROUP BY CALIBRATION_CERTIFICATE.IMTE_NUMBER) AS a INNER JOIN Tbl_Month ON a.MaxOfMonth_Number = Tbl_Month.Month_Number
    ORDER BY a.IMTE_NUMBER, Tbl_Month.Month_ShortName;
    Have a nice day!

  11. #11
    Join Date
    Sep 2011
    Posts
    66

    thanks for the code

    thanks for the reply sinndho

    Pls tell me what is the procedure to create a lookup table and where should i use or put the code to work

    i tried creating it but i think i did it wrong i have attached what i have done in the data base

    pls bear me
    Last edited by eddi; 10-23-11 at 10:12.

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You create the new table by selecting "New Table" in the Database window, then fill in the necessary information (name of the columns, data type, etc.). When you're done you save it under the name "Tbl_Month", then fill it with:
    Code:
    Month_Number	Month_ShortName	Month_FullName
    ----------------------------------------------		
     1		jan		January
     2		feb		February
     3		mar		March
     4		apr		April
     5		may		May
     6		jun		June
     7		jul		July
     8		aug		August
     9		sep		September
    10		oct		October
    11		nov		November
    12		dec		December
    The code (as you call it) represents the SQL statements for the queries you want to use for eliminating duplicate rows. Create a query, switch to SQL View then paste one of the SQL statements in it. Repeate for the other one.
    Have a nice day!

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry to me this is just curingthe symtoms of bad design
    what I'd suggest you do is store the date of the calibration alongside the itme number
    then make the primary key of the calibration certificate to be itme & month(calibrationdate)

    that then means you cannot have more than one record for a specific itme number AND a calibration date
    when yhou want to report use format(calibration date,"mmm") to get the three letter month name or wahtever else you need from a date

    ..the adbatnages are numerous, not least you can then produce a report which indicates what equipment is due for calibration when. ideally use a calibration code it indicate the type of claibration (eg type lkhlk needs recalibrating in 6 months, type 089-7 in 3 months and so on

    as someone else has already said to you elsewhere storing the month of calibration in the ITME tabel is pants.. its a calibration it should apply to the calibration certificate, not the item itself
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Sep 2011
    Posts
    66

    Sinndho thanks for the reply

    pls help its working if i open the querys but dont know how to link to the report

    i have done as you have told in my data base and if i open

    query1 for Min value

    and query2 for Max value

    but how will it run when i click the reportform

    [calibration month status] button

    pls check with my database
    Attached Files Attached Files

  15. #15
    Join Date
    Sep 2011
    Posts
    66
    healdem thanks for the reply
    Jaded Developer

    thanks for the suggestion i have a bigger problem as i dont know to design because i dont know the importance of primarkey and how it is use full

    i shall come back with a new post and the question i will be glad if you and sinndho can help me

    thanks a lot both of you

Posting Permissions

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