Basically, I have an Access database that is linked to Kace.  I use it to make queries which I link a label program to and print labels.  <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

 

I’m trying to make a query that basically just has all the fields of one of the asset types in.  The problem is some of those are linked fields, so they don’t show up as a field in the ASSET_DATA_## table.

 

I thought I could do this – make a report in Kace that basically lists all the fields from the asset table, and then just copy the SQL query into a query in Access.  I made the list fine, but when I copy and paste to Access, and try to run the query, I get an “Syntax error (missing operator) in query expression” error.  I think I am missing some () or something simple.

 

SELECT ASSET.ID AS ASSET_ID, A10043.NAME AS FIELD_10043, ASSET_DATA_34.FIELD_10041 AS FIELD_10041, ASSET_DATA_34.FIELD_10040 AS FIELD_10040, ASSET.NAME AS ASSET_NAME, ASSET_DATA_34.FIELD_10047 AS FIELD_10047, ASSET_DATA_34.FIELD_10042 AS FIELD_10042, ASSET_DATA_34.FIELD_10044 AS FIELD_10044, A10046.NAME AS FIELD_10046, A10045.NAME AS FIELD_10045, ASSET_DATA_34.FIELD_10048 AS FIELD_10048, A10039.NAME AS FIELD_10039  FROM ASSET_DATA_34 LEFT JOIN ASSET ON ASSET_DATA_34.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=34 LEFT JOIN ASSET_ASSOCIATION J10043 ON J10043.ASSET_ID = ASSET.ID AND J10043.ASSET_FIELD_ID=10043

                             LEFT JOIN ASSET A10043 ON A10043.ID = J10043.ASSOCIATED_ASSET_ID

                             LEFT JOIN ASSET_DATA_1 AD10043 ON AD10043.ID = A10043.ASSET_DATA_ID

                             LEFT JOIN ASSET_ASSOCIATION J10046 ON J10046.ASSET_ID = ASSET.ID AND J10046.ASSET_FIELD_ID=10046

                             LEFT JOIN ASSET A10046 ON A10046.ID = J10046.ASSOCIATED_ASSET_ID

                             LEFT JOIN ASSET_DATA_10 AD10046 ON AD10046.ID = A10046.ASSET_DATA_ID

                             LEFT JOIN ASSET_ASSOCIATION J10045 ON J10045.ASSET_ID = ASSET.ID AND J10045.ASSET_FIELD_ID=10045

                             LEFT JOIN ASSET A10045 ON A10045.ID = J10045.ASSOCIATED_ASSET_ID

                             LEFT JOIN ASSET_DATA_28 AD10045 ON AD10045.ID = A10045.ASSET_DATA_ID

                             LEFT JOIN ASSET_ASSOCIATION J10039 ON J10039.ASSET_ID = ASSET.ID AND J10039.ASSET_FIELD_ID=10039

                             LEFT JOIN ASSET A10039 ON A10039.ID = J10039.ASSOCIATED_ASSET_ID

                             LEFT JOIN ASSET_DATA_4 AD10039 ON AD10039.ID = A10039.ASSET_DATA_ID

                                ORDER BY ASSET_ID

1 Comment   [ + ] Show Comment

Comments

  • If this works in KACE see if you can just make this a Pass Through query in Access. If the query is correct it should run.
    • THANK YOU!!!!! I learned something new today, it was quick, and worked instantly! I can't thank you enough!
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity

Share