Inner Join more then 1 table with MS ACCESS database

[czupet]

I am using iReport 5.1.0 and ODBC for MS ACCESS database

But I am getting the following error when I inner join more then 1 table.

Does anyone know how to fix this probelm?

Thank you 

Peter

 

Here's my error:

 

 

Error filling print... Error preparing statement for executing the report query : 

SELECT
i.`RecordID` AS ContactsTable_RecordID,
     i.`ScanType` AS ContactsTable_ScanType,
     i.`MillNumber` AS ContactsTable_MillNumber,
     i.`MachineNumber` AS ContactsTable_MachineNumber,
     i.`ContactNumber` AS ContactsTable_ContactNumber,
     i.`Contact` AS ContactsTable_Contact
FROM
     ContactsTable i
inner join ContactsTable o
on i.RecordID = o.RecordID
inner join ContactsTable i
on i.RecordID = t.RecordID


net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query : 

SELECT
i.`RecordID` AS ContactsTable_RecordID,
     i.`ScanType` AS ContactsTable_ScanType,
     i.`MillNumber` AS ContactsTable_MillNumber,
     i.`MachineNumber` AS ContactsTable_MachineNumber,
     i.`ContactNumber` AS ContactsTable_ContactNumber,
     i.`Contact` AS ContactsTable_Contact
FROM
     ContactsTable i
inner join ContactsTable o
on i.RecordID = o.RecordID
inner join ContactsTable i
on i.RecordID = t.RecordID

 
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:377) 
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:196) 
    at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1087) 
    at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:668) 
    at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1258) 
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:877) 
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:822) 
    at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:61) 
    at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:446) 
    at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:276) 
    at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:745) 
    at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:891) 
    at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572) 
    at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997) 
Caused by: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'i.RecordID = o.RecordID
inner join ContactsTable i
on i.RecordID = t.RecordID'. 
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6956) 
    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7113) 
    at sun.jdbc.odbc.JdbcOdbc.SQLPrepare(JdbcOdbc.java:4829) 
    at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(JdbcOdbcConnection.java:475) 
    at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(JdbcOdbcConnection.java:443) 
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:267) 
    ... 13 more 
Print not filled. Try to use an EmptyDataSource... 

[czupet]

Here's the correct syntax if anyone is interested.

 

 

SELECT

     ContactsTable.`RecordID`,

     ContactsTable.`ScanType`,

     ContactsTable.`MillNumber`,

     ContactsTable.`MachineNumber`,

     ContactsTable.`Contact`

FROM

     (`ContactsTable` ContactsTable INNER JOIN `ContactsTable` i ON ContactsTable.`RecordID` = i.`RecordID`)

     INNER JOIN `ContactsTable` o ON ContactsTable.`RecordID` = o.`RecordID`