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.
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....
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.