Can someone please help me with a JOIN query using an Access database linked to Kace?
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
Be the first to answer this question