none
Make-table query does not return all the records vs. select query RRS feed

  • Question

  • I have a select query (Access 2016) that returns 2806 records. When I convert it into a make-table query the table it creates has only 532 records. How do I get the make-table query to return all the records?
    Thursday, February 28, 2019 4:34 PM

All replies

  • Hi. Can you post the SELECT statement and the SELECT INTO statement? We just need to make sure we're not missing anything. Thanks.
    Thursday, February 28, 2019 4:45 PM
  • Sorry its so huge

    ______SELECT___________________________________________________________

    SELECT [HANA_ERP_QALS].[AUFNR] & "-" & [HANA_ERP_AFVC].[VORNR] & "-" & [HANA_ERP_QAMV].[merknr] AS MRC, HANA_ERP_QAMV.prueflos AS INSPLOT_NBR, HANA_ERP_QALS.MATNR AS PART_NBR, HANA_ERP_MAKT.MAKTX AS PART_NM, HANA_ERP_QALS.AUFNR AS ORDER_NBR, HANA_ERP_AFVC.VORNR AS OPER_NBR, HANA_ERP_QAMV.merknr AS CHAR_NBR, HANA_ERP_QAMV.verwmerkm AS MRC_A, HANA_ERP_QAMR.SATZSTATUS AS STATUS_CD, HANA_ERP_QAMR.PRUEFDATUV AS INSP_STARTDATE, HANA_ERP_QALS.WERK AS SITE_CD, HANA_ERP_QAMV.verwmerkm AS MRC_DESC, HANA_ERP_QAMV.erstelldat AS CREATE_DATE, HANA_ERP_OBJK.SERNR AS SERIALNUMBER, HANA_ERP_QALS.STAT35, HANA_ERP_QAMR.MBEWERTG, HANA_ERP_QAMR.SATZSTATUS, IIf(([stat35]="X") Or (([HANA_ERP_QAMR].[satzstatus]="1") And ([mbewertg]="")),"CLOSED","OPEN") AS STATUS_TXT, HANA_ERP_ZAFKO_DPAS.NETWORK AS CONTRACT, HANA_ERP_ZQM_HIERARCHY.QNOTE_DESC, [hana_erp_QAMV].[prueflos] & [hana_erp_QAMV].[vorglfnr] & [hana_erp_QAMV].[merknr] & "0000000001" AS ltkey, IIf([HANA_ERP_QAMR].[AENDERDAT]="00000000","0",[HANA_ERP_QAMR].[AENDERDAT]) AS STATUSDATE
    FROM (((((((HANA_ERP_QAMV INNER JOIN HANA_ERP_QAMR ON (HANA_ERP_QAMV.VORGLFNR = HANA_ERP_QAMR.VORGLFNR) AND (HANA_ERP_QAMV.PRUEFLOS = HANA_ERP_QAMR.PRUEFLOS)) INNER JOIN HANA_ERP_QALS ON HANA_ERP_QAMV.PRUEFLOS = HANA_ERP_QALS.PRUEFLOS) INNER JOIN HANA_ERP_AFVC ON (HANA_ERP_QAMV.VORGLFNR = HANA_ERP_AFVC.APLZL) AND (HANA_ERP_QALS.AUFPL = HANA_ERP_AFVC.AUFPL)) INNER JOIN HANA_ERP_SER05 ON HANA_ERP_QALS.AUFNR = HANA_ERP_SER05.PPAUFNR) INNER JOIN HANA_ERP_OBJK ON (HANA_ERP_SER05.OBKNR = HANA_ERP_OBJK.OBKNR) AND (HANA_ERP_SER05.MANDT = HANA_ERP_OBJK.MANDT)) INNER JOIN HANA_ERP_MAKT ON HANA_ERP_QALS.MATNR = HANA_ERP_MAKT.MATNR) INNER JOIN HANA_ERP_ZAFKO_DPAS ON HANA_ERP_QALS.AUFNR = HANA_ERP_ZAFKO_DPAS.AUFNR) INNER JOIN HANA_ERP_ZQM_HIERARCHY ON (HANA_ERP_ZAFKO_DPAS.MANDT = HANA_ERP_ZQM_HIERARCHY.MANDT) AND (HANA_ERP_ZAFKO_DPAS.NETWORK = HANA_ERP_ZQM_HIERARCHY.PRCTR)
    WHERE (((HANA_ERP_MAKT.SPRAS)="E") AND ((HANA_ERP_QAMV.verwmerkm) Like "RWK*"))
    GROUP BY [HANA_ERP_QALS].[AUFNR] & "-" & [HANA_ERP_AFVC].[VORNR] & "-" & [HANA_ERP_QAMV].[merknr], HANA_ERP_QAMV.prueflos, HANA_ERP_QALS.MATNR, HANA_ERP_MAKT.MAKTX, HANA_ERP_QALS.AUFNR, HANA_ERP_AFVC.VORNR, HANA_ERP_QAMV.merknr, HANA_ERP_QAMR.PRUEFDATUV, HANA_ERP_QALS.WERK, HANA_ERP_QAMV.verwmerkm, HANA_ERP_QAMV.erstelldat, HANA_ERP_OBJK.SERNR, HANA_ERP_QALS.STAT35, HANA_ERP_QAMR.MBEWERTG, HANA_ERP_QAMR.SATZSTATUS, IIf(([stat35]="X") Or (([HANA_ERP_QAMR].[satzstatus]="1") And ([mbewertg]="")),"CLOSED","OPEN"), HANA_ERP_ZAFKO_DPAS.NETWORK, HANA_ERP_ZQM_HIERARCHY.QNOTE_DESC, [hana_erp_QAMV].[prueflos] & [hana_erp_QAMV].[vorglfnr] & [hana_erp_QAMV].[merknr] & "0000000001", IIf([HANA_ERP_QAMR].[AENDERDAT]="00000000","0",[HANA_ERP_QAMR].[AENDERDAT]), HANA_ERP_QAMR.SATZSTATUS
    HAVING (((HANA_ERP_QALS.WERK)="CHEM") AND ((HANA_ERP_QAMV.erstelldat)>"20180101"));
    
    ___MAKE TABLE_____________________________________________
    SELECT [HANA_ERP_QALS].[AUFNR] & "-" & [HANA_ERP_AFVC].[VORNR] & "-" & [HANA_ERP_QAMV].[merknr] AS MRC, HANA_ERP_QAMV.prueflos AS INSPLOT_NBR, HANA_ERP_QALS.MATNR AS PART_NBR, HANA_ERP_MAKT.MAKTX AS PART_NM, HANA_ERP_QALS.AUFNR AS ORDER_NBR, HANA_ERP_AFVC.VORNR AS OPER_NBR, HANA_ERP_QAMV.merknr AS CHAR_NBR, HANA_ERP_QAMV.verwmerkm AS MRC_A, HANA_ERP_QAMR.SATZSTATUS AS STATUS_CD, HANA_ERP_QAMR.PRUEFDATUV AS INSP_STARTDATE, HANA_ERP_QALS.WERK AS SITE_CD, HANA_ERP_QAMV.verwmerkm AS MRC_DESC, HANA_ERP_QAMV.erstelldat AS CREATE_DATE, HANA_ERP_OBJK.SERNR AS SERIALNUMBER, HANA_ERP_QALS.STAT35, HANA_ERP_QAMR.MBEWERTG, HANA_ERP_QAMR.SATZSTATUS, IIf(([stat35]="X") Or (([HANA_ERP_QAMR].[satzstatus]="1") And ([mbewertg]="")),"CLOSED","OPEN") AS STATUS_TXT, HANA_ERP_ZAFKO_DPAS.NETWORK AS CONTRACT, HANA_ERP_ZQM_HIERARCHY.QNOTE_DESC, [hana_erp_QAMV].[prueflos] & [hana_erp_QAMV].[vorglfnr] & [hana_erp_QAMV].[merknr] & "0000000001" AS ltkey, IIf([HANA_ERP_QAMR].[AENDERDAT]="00000000","0",[HANA_ERP_QAMR].[AENDERDAT]) AS STATUSDATE INTO SQL_MRC_TABLE
    FROM (((((((HANA_ERP_QAMV INNER JOIN HANA_ERP_QAMR ON (HANA_ERP_QAMV.VORGLFNR = HANA_ERP_QAMR.VORGLFNR) AND (HANA_ERP_QAMV.PRUEFLOS = HANA_ERP_QAMR.PRUEFLOS)) INNER JOIN HANA_ERP_QALS ON HANA_ERP_QAMV.PRUEFLOS = HANA_ERP_QALS.PRUEFLOS) INNER JOIN HANA_ERP_AFVC ON (HANA_ERP_QAMV.VORGLFNR = HANA_ERP_AFVC.APLZL) AND (HANA_ERP_QALS.AUFPL = HANA_ERP_AFVC.AUFPL)) INNER JOIN HANA_ERP_SER05 ON HANA_ERP_QALS.AUFNR = HANA_ERP_SER05.PPAUFNR) INNER JOIN HANA_ERP_OBJK ON (HANA_ERP_SER05.OBKNR = HANA_ERP_OBJK.OBKNR) AND (HANA_ERP_SER05.MANDT = HANA_ERP_OBJK.MANDT)) INNER JOIN HANA_ERP_MAKT ON HANA_ERP_QALS.MATNR = HANA_ERP_MAKT.MATNR) INNER JOIN HANA_ERP_ZAFKO_DPAS ON HANA_ERP_QALS.AUFNR = HANA_ERP_ZAFKO_DPAS.AUFNR) INNER JOIN HANA_ERP_ZQM_HIERARCHY ON (HANA_ERP_ZAFKO_DPAS.MANDT = HANA_ERP_ZQM_HIERARCHY.MANDT) AND (HANA_ERP_ZAFKO_DPAS.NETWORK = HANA_ERP_ZQM_HIERARCHY.PRCTR)
    WHERE (((HANA_ERP_MAKT.SPRAS)="E") AND ((HANA_ERP_QAMV.verwmerkm) Like "RWK*"))
    GROUP BY [HANA_ERP_QALS].[AUFNR] & "-" & [HANA_ERP_AFVC].[VORNR] & "-" & [HANA_ERP_QAMV].[merknr], HANA_ERP_QAMV.prueflos, HANA_ERP_QALS.MATNR, HANA_ERP_MAKT.MAKTX, HANA_ERP_QALS.AUFNR, HANA_ERP_AFVC.VORNR, HANA_ERP_QAMV.merknr, HANA_ERP_QAMR.PRUEFDATUV, HANA_ERP_QALS.WERK, HANA_ERP_QAMV.verwmerkm, HANA_ERP_QAMV.erstelldat, HANA_ERP_OBJK.SERNR, HANA_ERP_QALS.STAT35, HANA_ERP_QAMR.MBEWERTG, HANA_ERP_QAMR.SATZSTATUS, IIf(([stat35]="X") Or (([HANA_ERP_QAMR].[satzstatus]="1") And ([mbewertg]="")),"CLOSED","OPEN"), HANA_ERP_ZAFKO_DPAS.NETWORK, HANA_ERP_ZQM_HIERARCHY.QNOTE_DESC, [hana_erp_QAMV].[prueflos] & [hana_erp_QAMV].[vorglfnr] & [hana_erp_QAMV].[merknr] & "0000000001", IIf([HANA_ERP_QAMR].[AENDERDAT]="00000000","0",[HANA_ERP_QAMR].[AENDERDAT]), HANA_ERP_QAMR.SATZSTATUS
    HAVING (((HANA_ERP_QALS.WERK)="CHEM") AND ((HANA_ERP_QAMV.erstelldat)>"20180101"));
    
    

    Thursday, February 28, 2019 5:18 PM
  • Okay, thanks. That confirms it. Nothing funny is going on there. You are sure the table SQL_MRC_TABLE does not exist, correct? I was just wondering if it's an existing table, it might have some constraints in it; but you should get a prompt to overwrite it anyway, I think.
    Thursday, February 28, 2019 6:14 PM