Results 1 to 2 of 2
  1. #1
    reynier09 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    17

    help with sql

    what am I missing here that I'm getting syntax error missing operator:
    SELECT [Employee ID]


    FROM [Document Association]
    WHERE [Document Association].[Document ID]='725'
    INTERSECT
    SELECT Counter
    FROM [Completion Documents]
    WHERE [Completion Documents].[Document ID]='725';

    The idea is I have employees that are assigned document training in separate records, and I'm trying to obtain which employees have completed a certain amount of records (like area training, so one record might me useful for different areas).i.e. Packaging training requires you to have completed training document 1, 2, 3, so I'm trying to obtain which employees completed all three documents. Thanks

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    access sql does not use INTERSECT - so the fact you are means have to ask the question - is your backend Access or SQL Server?

    And I can't see why you would want to use this based on what you are trying to achieve

    Also ='725' implies the datatype for document type is text - I'm assuming it is actually a number, but if it is actually text, add the single quotes back in below

    I would have thought something like

    SELECT EmployeeID, Counter
    FROM [Document Association] INNER JOIN [Completion Documents] ON [Document Association].[Document ID] = [Completion Documents].[Document ID]
    WHERE [Document Association].[Document ID]=725

    Or perhaps

    SELECT EmployeeID, Counter
    FROM [Document Association] LEFT JOIN [Completion Documents] ON [Document Association].[Document ID] = [Completion Documents].[Document ID]
    WHERE [Document Association].[Document ID]=725

    To mimic INTERSECT you would need to use a union query

    SELECT EmployeeID, Counter
    FROM [Document Association] LEFT JOIN [Completion Documents] ON [Document Association].[Document ID] = [Completion Documents].[Document ID]
    WHERE [Document Association].[Document ID]=725
    UNION SELECT EmployeeID, Counter
    FROM [Document Association] RIGHT JOIN [Completion Documents] ON [Document Association].[Document ID] = [Completion Documents].[Document ID]
    WHERE [Completion Documents].[Document ID]=725

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums