SQL Server to Access SQL

[skilche1]

OK, I am tapping in the SQL Server database from within Access. I am trying to create a query from a query statement that works fine on the SQL Server side. When I applied the same query in Access, it doesn't work and end up getting an error code (Syntax error in FROM clause). And to be honest, I really hadn't gotten that involved in the SQL coding for Queries aspects until now. I am venturing in uncharted areas.

Here is the code that works on the server side:

SELECT     SHOP_FLOOR_WORK.SALES_ORDER_NUMBER, SHOP_FLOOR_WORK.LINE_ITEM, SHOP_FLOOR_WORK.PRODUCTION_ORDER_NUMBER, SHOP_FLOOR_WORK.UNIT_NUMBER, SHOP_FLOOR_WORK.PLANT_ID, SHOP_FLOOR_WORK.KMAT_ID, 
                      KMAT.KMAT_DESCRIPTION, SHOP_FLOOR_WORK.UNIT_WIDTH, SHOP_FLOOR_WORK.UNIT_HEIGHT, OPERATOR_TRANSACTION.DATE_COMPLETED, EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME
FROM         KMAT INNER JOIN
                      SHOP_FLOOR_WORK INNER JOIN
                      OPERATOR_TRANSACTION ON SHOP_FLOOR_WORK.PRODUCTION_ORDER_NUMBER = OPERATOR_TRANSACTION.PRODUCTION_ORDER_NUMBER AND 
                      SHOP_FLOOR_WORK.UNIT_NUMBER = OPERATOR_TRANSACTION.UNIT_NUMBER INNER JOIN
                      KMAT_OPERATION ON OPERATOR_TRANSACTION.KMAT_OPERATION_ID = KMAT_OPERATION.KMAT_OPERATION_ID INNER JOIN
                      EMPLOYEE_LOG ON OPERATOR_TRANSACTION.EMPLOYEE_LOG_ID = EMPLOYEE_LOG.EMPLOYEE_LOG_ID INNER JOIN
                      EMPLOYEE ON EMPLOYEE_LOG.EMPLOYEE_ID = EMPLOYEE.EMPLOYEE_ID ON KMAT.KMAT_ID = SHOP_FLOOR_WORK.KMAT_ID
WHERE     (KMAT_OPERATION.OPERATION_STATUS_ID = 6)  AND (OPERATOR_TRANSACTION.DATE_COMPLETED > '1/1/2008')

And here is the code I've been trying to convert for Access SQL Query:

SELECT     dbo_SHOP_FLOOR_WORK.SALES_ORDER_NUMBER, dbo_SHOP_FLOOR_WORK.LINE_ITEM, dbo_SHOP_FLOOR_WORK.PRODUCTION_ORDER_NUMBER, dbo_SHOP_FLOOR_WORK.UNIT_NUMBER, dbo_SHOP_FLOOR_WORK.PLANT_ID, dbo_SHOP_FLOOR_WORK.KMAT_ID, 
                     dbo_KMAT.KMAT_DESCRIPTION, dbo_SHOP_FLOOR_WORK.UNIT_WIDTH, dbo_SHOP_FLOOR_WORK.UNIT_HEIGHT, dbo_OPERATOR_TRANSACTION.DATE_COMPLETED, dbo_EMPLOYEE.FIRST_NAME, dbo_EMPLOYEE.LAST_NAME
FROM         dbo_KMAT INNER JOIN
                      ((((dbo_SHOP_FLOOR_WORK INNER JOIN
                      dbo_OPERATOR_TRANSACTION ON dbo_SHOP_FLOOR_WORK.PRODUCTION_ORDER_NUMBER = dbo_OPERATOR_TRANSACTION.PRODUCTION_ORDER_NUMBER AND 
                      dbo_SHOP_FLOOR_WORK.UNIT_NUMBER = dbo_OPERATOR_TRANSACTION.UNIT_NUMBER) INNER JOIN
                      dbo_KMAT_OPERATION ON dbo_OPERATOR_TRANSACTION.KMAT_OPERATION_ID = dbo_KMAT_OPERATION.KMAT_OPERATION_ID) INNER JOIN
                      dbo_EMPLOYEE_LOG ON OPERATOR_TRANSACTION.EMPLOYEE_LOG_ID = dbo_EMPLOYEE_LOG.EMPLOYEE_LOG_ID) INNER JOIN
                      dbo_EMPLOYEE ON dbo_EMPLOYEE_LOG.EMPLOYEE_ID = dbo_EMPLOYEE.EMPLOYEE_ID ON dbo_KMAT.KMAT_ID = dbo_SHOP_FLOOR_WORK.KMAT_ID)
WHERE     ((dbo_KMAT_OPERATION.OPERATION_STATUS_ID = 6)  AND (dbo_OPERATOR_TRANSACTION.DATE_COMPLETED > '1/1/2008'));

What am I missing here?

Thanks

 

[Banana]

What will you do with that query? If it's for reporting or otherwise read-only, you can just use passthrough query which will be certainly quicker.

But if it's to be bound to form or edited one way or other, then the error is the fact that you can't do a JOIN with AND/OR as you can do with SQL Server.

 

[skilche1]

I would be pulling data from the tables off the SQL Server to show in a form on the amount of product an Employee has produced between a given time period. An now, I am not looking to edit the data, but extracting data for viewing purposes only. I already have one query I was able to build from Access and it allows me to view data.

Does this help?