Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2011
    Posts
    3

    Unanswered: access help - query multiple values in same field

    Can someone help me?

    I have inherited a database where I am trying to query from multiple tables. They are a student table, schedule table, registration table and course/class table.

    In the Title Id field in the registration table, the Title Id is listed multiple times. What I am trying to do is get a report that will list who took Excel 2007 Level 1 (Title ID=143) and did not take Excel 2007 Level 2 (Title Id =144) So what I need to say is find all the students that took 143 but not 144.. but these values are in the same field and the student is listed multiple times in the table. How can I do this? Can I use paramater prompts at all?

    My boss is trying to use this as a target for upcoming classes and he does not know Access at all.

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    here's 1 easy way;

    make a query for the 143s...let's call it q143
    next make a query for the 144s; i.e. q144

    now make a 3rd query - put q143 and q144 up as the tables/record sources

    draw a join line from the appropriate student id from the q143 to the q144 field....and then right click on that join line and select the option so it shows ALL of q143; it will have an arrow pointing at q144 after that option.

    run the query and you'll see all the 143s, and all the 144s that match plus blanks in the 144s showing who did not take the course....and if you want just the blanks you can refine it to put in 'null' as the 144 criteria....

    hope this helps.
    www CahabaData com

  3. #3
    Join Date
    Feb 2011
    Posts
    3

    ??

    I tired this and it did not work.. any other suggestions??

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I would have suggested something similar, and it should work. To make it a little simpler, create the two initial queries NTC mentioned, and then run the unmatched query wizard against them (it will create the third query NTC described). That should give you anybody who's taken 143 that hasn't taken 144. If it doesn't work, post the SQL of all 3 queries or a sample db. Simply saying "it did not work" doesn't give us much to go on.
    Paul

  5. #5
    Join Date
    Feb 2011
    Posts
    3
    thank you -- this time I was able to extract the data I needed. What happened when I tred to set it up initally was that it was just giving me the first set of data I was querying.

    I have another question, how can I set this up using paramaters so that my boss can run it using differents class Id's?

    Thank you for all of your help!

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'd have a form for the user to input the 2 class ID's (in the example, 143 & 144). Have each of the base queries point to that form for their respective criteria. That would look like:

    Forms!FormName.ControlName

    Running the third query should then give you the result for any given combination.
    Paul

Posting Permissions

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