Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2007
    Posts
    16

    Unanswered: Stop Appending Query if Duplicate Found

    I want to stop appending data if the Field [A] and Field [B] records found/Match in Main table Field [A] and [B]. If Match found Msg Box appear and appending stops.

    Can any one help me or give me code.

    Regards.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Do you want to stop appending the whole query or just not include those records in the append?
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2007
    Posts
    16
    Just want to stop stop those records not whole append process.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So you want to modify your SQL to not include those that are duplicates...

    This can be achieved using sub-queries (there are other methods too)

    Please post your current SQL statement and we can help you modify as necessary.
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2007
    Posts
    16
    Giving you below append query SQL Code. want to stop append those records that matches basis [IGMNO] [PORTCD] Fields.

    SQL:

    INSERT INTO MCIGMMS ( PORTCD, IGMNO, IGMYY, IGMDAT, SHPANO, VSLFLT, VOYAGE, ARVDAT, SLOCCD, CNTCOD, LSTPRT, CAPTAN, SHPCMP, TOTIDX, BOTTOM, BEGIDX )
    SELECT PCIGMMS2.PORTCD, PCIGMMS2.IGMNO, PCIGMMS2.IGMCY, PCIGMMS2.IGMDAT, PCIGMMS2.SHPANO, PCIGMMS2.VSLFLT, PCIGMMS2.VOYAGE, PCIGMMS2.ARVDAT, PCIGMMS2.SLOCCD, PCIGMMS2.CNTCOD, PCIGMMS2.LSTPRT, PCIGMMS2.CAPTAN, PCIGMMS2.SHPCMP, PCIGMMS2.TOTIDX, PCIGMMS2.SBOTTOM, PCIGMMS2.BEGIDX
    FROM MCIGMMS RIGHT JOIN PCIGMMS2 ON MCIGMMS.IGMNO = PCIGMMS2.IGMNO;

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    WHERE MCMIGMMS.PORTCD NOT IN
     (
     SELECT PORTCD
     FROM PCIGMMS2
     )
    
    AND MCMIGMMS.IGMNO NOT IN
     (
     SELECT IGMNO
     FROM PCIGMMS2
     )
    This is completely untested but should give you a good idea of how to get it right. The sub select statements pick out all the values from one table and compares them to the original select statement using NOT IN.

    Let us know how you get on
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2007
    Posts
    16
    My Dear,

    Thanks a lot its working. Still one problem i m facing, I have one append query with aggregate function (Count calculation). This code is not working in this query. any idea?

    Please advise.

    SQL Code:

    INSERT INTO MCIGMFI ( PORTCD, IGMNO, IGMYY, IGMDAT, SHPANO, VSLFLT, VOYAGE, ARVDAT, SLOCCD, CNTCOD, LSTPRT, CAPTAN, SHPCMP, TOTIDX, BOTTOM, BEGIDX )
    SELECT PCIGMMS2.PORTCD, PCIGMMS2.IGMNO, PCIGMMS2.IGMCY, PCIGMMS2.IGMDAT, PCIGMIX2.SLOCCD, PCIGMMS2.VSLFLT, PCIGMMS2.VOYAGE, PCIGMMS2.ARVDAT, PCIGMMS2.SLOCCD, PCIGMMS2.CNTCOD, PCIGMMS2.LSTPRT, PCIGMMS2.CAPTAN, PCIGMMS2.SHPCMP, Count(PCIGMIX2.INDXNO) AS CountOfINDXNO, PCIGMMS2.SBOTTOM, Min(PCIGMIX2.INDXNO) AS MinOfINDXNO
    FROM PCIGMIX2 INNER JOIN (PCIGMMS2 INNER JOIN MCIGMMS ON PCIGMMS2.IGMNO = MCIGMMS.IGMNO) ON PCIGMIX2.IGMNO = PCIGMMS2.IGMNO
    GROUP BY PCIGMMS2.PORTCD, PCIGMMS2.IGMNO, PCIGMMS2.IGMCY, PCIGMMS2.IGMDAT, PCIGMIX2.SLOCCD, PCIGMMS2.VSLFLT, PCIGMMS2.VOYAGE, PCIGMMS2.ARVDAT, PCIGMMS2.SLOCCD, PCIGMMS2.CNTCOD, PCIGMMS2.LSTPRT, PCIGMMS2.CAPTAN, PCIGMMS2.SHPCMP, PCIGMMS2.SBOTTOM
    HAVING (((MCIGMMS.PORTCD) Not In ([MCIGMMS].[PORTCD])) AND ((MCIGMMS.IGMNO) Not In ([MCIGMMS].[IGMNO])));

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Looks like you're trying to insert into more columns than you have declared.

    Your INSERT has 10(?)
    but the SELECT has more.
    George
    Home | Blog

  9. #9
    Join Date
    Mar 2007
    Posts
    16
    yes exactly........all these select are important.

    is there any problem in coding

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You can only insert 10 values into 10 columns...
    George
    Home | Blog

  11. #11
    Join Date
    Mar 2007
    Posts
    16
    SORRY MY MISTAKE........SELECT AND INSERT ARE SAME FIELDS. BELOW IS CODE AGAIN.

    SQL:
    INSERT INTO MCIGMFI ( PORTCD, IGMNO, IGMYY, IGMDAT, SHPANO, VSLFLT, VOYAGE, ARVDAT, SLOCCD, CNTCOD, LSTPRT, CAPTAN, SHPCMP, TOTIDX, BOTTOM, BEGIDX )
    SELECT PCIGMMS2.PORTCD, PCIGMMS2.IGMNO, PCIGMMS2.IGMCY, PCIGMMS2.IGMDAT, PCIGMIX2.SLOCCD, PCIGMMS2.VSLFLT, PCIGMMS2.VOYAGE, PCIGMMS2.ARVDAT, PCIGMMS2.SLOCCD, PCIGMMS2.CNTCOD, PCIGMMS2.LSTPRT, PCIGMMS2.CAPTAN, PCIGMMS2.SHPCMP, Count(PCIGMIX2.INDXNO) AS CountOfINDXNO, PCIGMMS2.SBOTTOM, Min(PCIGMIX2.INDXNO) AS MinOfINDXNO
    FROM PCIGMIX2 INNER JOIN (PCIGMMS2 INNER JOIN MCIGMMS ON PCIGMMS2.IGMNO = MCIGMMS.IGMNO) ON PCIGMIX2.IGMNO = PCIGMMS2.IGMNO
    GROUP BY PCIGMMS2.PORTCD, PCIGMMS2.IGMNO, PCIGMMS2.IGMCY, PCIGMMS2.IGMDAT, PCIGMIX2.SLOCCD, PCIGMMS2.VSLFLT, PCIGMMS2.VOYAGE, PCIGMMS2.ARVDAT, PCIGMMS2.SLOCCD, PCIGMMS2.CNTCOD, PCIGMMS2.LSTPRT, PCIGMMS2.CAPTAN, PCIGMMS2.SHPCMP, PCIGMMS2.SBOTTOM
    HAVING (((PCIGMMS2.PORTCD) Not In ([MCIGMMS]![PORTCD])) AND ((PCIGMMS2.IGMNO) Not In ([MCIGMMS]![IGMNO])));


    I M RECEVING BELOW ERROR:

    YOU TRIED TO EXECUTE A QUERY THAT DOES NOT INCLUDE THE SPECIFIED EXPRESSION......................................AS PART OF AN AGGREGATE FUNCTION.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Change your counts to Count(*) (count of all rows returned)
    George
    Home | Blog

  13. #13
    Join Date
    Mar 2007
    Posts
    16
    Cant change Count(PCIGMIX2.INDXNO) to Count (*) it is necessary coz there is a need to just count indxno field.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Count(*) counts the number of inserts...
    As a tesat, give that a go and see if it works.

    Alternatively, the group and count might be clashing
    George
    Home | Blog

  15. #15
    Join Date
    Mar 2007
    Posts
    16
    I m putting the check that you hv given me NOT IN [(MCMIGMMS.PORTCD)] in Criteria field. Is that correct? If it is ok then i dont know why the aggregate error msg appears.

Posting Permissions

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