/build/static/layout/Breadcrumb_cap_w.png

Miscellaneous Question


Can someone please help me with a JOIN query using an Access database linked to Kace?

04/10/2015 1863 views

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!

Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ