Access UNION SQL Query Syntax error message-CodePudding

I am trying to make a union query between two tables in my database, the statement I have written is

SELECT tblMetalsResults.reportno, tblMetalsResults.sampleno, tblMetalsResults.element, tblMetalsResults.ElementResult, tblMetalsResults.ElementRL, tblMetalsResults.ElementAnalyst1, tblMetalsResults.ElementAnalyst2, tblMetalsResults.ElementDate1, tblMetalsResults.ElementDate2, tblLogging.loBattery, tblLogging.loTest
FROM tblLogging INNER JOIN tblMetalsResults ON tblLogging.ReportNo = tblMetalsResults.reportno
WHERE (((tblMetalsResults.reportno)=[forms]![frmlogging]![reportno]))
UNION
SELECT tblHerbicideResults.herbreportno, tblHerbicideResults.sampleno, tblHerbicideResults.24DResults, tblHerbicideResults.D45TPResults, tbllogging.lobattery, tbllogging.lotest
FROM tbllogging INNER JOIN tblHerbicideResults ON tbllogging.Reportno = tblHerbicideResults.herbreportno
WHERE (((tblHerbicideResults.reportno0=[forms]![frmlogging]![reportno]));

Does anyone know what I am doing wrong? It says syntax error in union query when I try to save.

CodePudding user response：

both side of unions should have the same number of columns and with compatible data types . if you don't have those columns , you can create those columns as null :

SELECT
    tblMetalsResults.reportno,
    tblMetalsResults.sampleno,
    tblMetalsResults.element,
    tblMetalsResults.ElementResult,
    tblMetalsResults.ElementRL,
    tblMetalsResults.ElementAnalyst1,
    tblMetalsResults.ElementAnalyst2,
    tblMetalsResults.ElementDate1,
    tblMetalsResults.ElementDate2,
    tblLogging.loBattery,
    tblLogging.loTest
FROM tblLogging INNER JOIN tblMetalsResults ON tblLogging.ReportNo = tblMetalsResults.reportno
WHERE (((tblMetalsResults.reportno)=[forms]![frmlogging]![reportno]))
UNION
SELECT  
    tblHerbicideResults.herbreportno,
    tblHerbicideResults.sampleno,
    NULL,
    tblHerbicideResults.24DResults,
    tblHerbicideResults.D45TPResults,
    NULL,
    NULL,
    NULL,
    NULL,
    tbllogging.lobattery,
    tbllogging.lotest
FROM tbllogging INNER JOIN tblHerbicideResults ON tbllogging.Reportno = tblHerbicideResults.herbreportno
WHERE (((tblHerbicideResults.reportno0=[forms]![frmlogging]![reportno]));