Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Access 2003 - Intersection in SQL RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    I am trying to take the intersection of 2 queries using SQL and I can't get anything to work.  I've tried NOT IN but it doesn't seem to be supported.  I've tried NOT EXISTS.  Anybody know how to take the intersection in Access?
    Thursday, April 12, 2007 3:11 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    I have found ways to intersect two tables only. So, I'm not sure if this would be of any help. It is really ugly.

    1)

    SELECT [CIS15-Roster].[Student-ID], [CIS15-Roster].[F-Name], [CIS15-Roster].[L-Name]
    FROM [CIS15-Roster] INNER JOIN [CIS52-Roster] ON [CIS15-Roster].[Student-ID] = [CIS52-Roster].[Student-ID]
    WHERE ((([CIS15-Roster].[Student-ID]) In (SELECT [Student-ID] FROM [CIS52-Roster])));

    2)

    SELECT [CIS15-Roster].[Student-ID], [CIS15-Roster].[F-Name], [CIS15-Roster].[L-Name]
    FROM [CIS15-Roster], [CIS52-Roster]
    WHERE ((([CIS15-Roster].[Student-ID]) In (SELECT [Student-ID] FROM [CIS52-Roster])));

    Monday, June 25, 2007 4:17 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    I have found ways to intersect two tables only. So, I'm not sure if this would be of any help. It is really ugly.

    1)

    SELECT [CIS15-Roster].[Student-ID], [CIS15-Roster].[F-Name], [CIS15-Roster].[L-Name]
    FROM [CIS15-Roster] INNER JOIN [CIS52-Roster] ON [CIS15-Roster].[Student-ID] = [CIS52-Roster].[Student-ID]
    WHERE ((([CIS15-Roster].[Student-ID]) In (SELECT [Student-ID] FROM [CIS52-Roster])));

    2)

    SELECT [CIS15-Roster].[Student-ID], [CIS15-Roster].[F-Name], [CIS15-Roster].[L-Name]
    FROM [CIS15-Roster], [CIS52-Roster]
    WHERE ((([CIS15-Roster].[Student-ID]) In (SELECT [Student-ID] FROM [CIS52-Roster])));

    Here is the link to the original page:
    http://cerig.efpg.inpg.fr/tutoriel/bases-de-donnees/chap21.htm

    Monday, June 25, 2007 4:17 PM