Results 1 to 5 of 5
  1. #1
    aymarhoon is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    2

    IN Operator

    Hi guys

    Is it possible to have specific values and values from a select query in IN Operator. I mean something like this



    SELECT tblStudents.*, Grade
    FROM tblStudents
    WHERE Grade In ("A","A-","B+","B","B-","C+","C","C-","D+","D","F",

    SELECT DISTINCT tblStudents AS Grade
    FROM tblStudents
    WHERE Grade Not In ("DIS","E", "W") AND Subject Like "ehs*")

    )


    I want to generate a crosstab query to have fixed column heading (grades A to F) but dynamic for (DIS, W, E, etc).

    Your help is greatly appreciated..

    Thanks

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You could try this
    Code:
    SELECT tblStudents.*, Grade
    FROM tblStudents
    WHERE Grade In ("A","A-","B+","B","B-","C+","C","C-","D+","D","F")
    UNION
    SELECT  tblStudents.* , Grade
    FROM tblStudents
    WHERE Grade Not In ("DIS","E", "W") AND Subject Like "ehs*")
    See w3schools for more SQL examples and info.

  3. #3
    aymarhoon is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    2
    Thanks but where I can place this statement in the cross tab query?

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I was answering your first question re combining queries.

    You may get something from this MS Access Crosstab article.

    My guess is that Google and Youtube would be useful tools for researching your questions.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. INSERT INTO or other SQL operator ?
    By VAer in forum Access
    Replies: 12
    Last Post: 09-17-2018, 08:53 AM
  2. IN operator
    By dougperk in forum Macros
    Replies: 1
    Last Post: 03-11-2015, 04:39 AM
  3. AND Operator in the Query
    By drunkenneo in forum Queries
    Replies: 1
    Last Post: 06-09-2014, 11:27 PM
  4. Or operator
    By Rhubie in forum Access
    Replies: 1
    Last Post: 09-06-2012, 04:42 PM
  5. in operator
    By pchang in forum Access
    Replies: 1
    Last Post: 01-16-2011, 06:24 AM

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