Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Advanced filter (of sorts) (2000)

    Ok, I have a table with MANY records in it, with no primary key and multiple occurrances of many records. A new record is added for every individual once they complete a class. I realize that columns with the class name formatted as yes/no would be more effective, however I acquired this beast, and now I need to manipulate the data for a one-time presentation. Basically, I would like to create a filter/query that would give me the names of only those who have completed three of the courses, for example, I would have something as simplistic as:

    Jones - Math 101
    Jones - Math 102
    Jones - Math 103
    Jones - English 101
    Edwards - Math 101
    Edwards - Math 102
    Edwards - Math 103
    Edwards - English 101
    Smith - Math 101

    I would like to query for all those who have completed Math 101, 102, and 103, and want my query to return only this:

    Jones
    Edwards

    I hope thats a good enough description without having posted an example.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advanced filter (of sorts) (2000)

    Say that your table is named tblData and that the fields are named LastName and Class. Create a query with the following SQL:

    SELECT LastName
    FROM tblData
    WHERE Class In ("Math 101","Math 102","Math 103")
    GROUP BY LastName
    HAVING Count(*)=3

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced filter (of sorts) (2000)

    Like a charm. <img src=/S/wartgun.gif border=0 alt=wartgun width=73 height=24>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •