Hey everyone! I’m stuck on a tricky Access query. Can anyone help me out?
I’ve got this database with three tables:
Learners (Name, Contact)
Subjects (Subject Title, Cost)
Enrollments (Subject Title, Name)
What I’m trying to do is create a query that shows all the subjects each learner hasn’t taken yet. This would be super helpful for our enrollment team to know who to reach out to for new sign-ups.
I’ve been messing around with different ways to do this but I’m stumped. I thought about using NOT EXISTS but I’m not sure how to set it up correctly.
The output I’m looking for would be something like:
Tim | Pro Web Design
Tim | Advanced Photoshop
Sarah | Intro to Web Design
Sarah | Intro to Photoshop
Sarah | Pro Web Design
Sarah | Advanced Photoshop
(Tim’s already done the intro courses, while Sarah hasn’t taken any yet)
Any ideas on how to tackle this in Access? I’d really appreciate some guidance!
hey ryan, i think i got a solution for ya. try using a cross join between learners and subjects, then exclude the ones in enrollments with a left join and where clause. something like:
SELECT L.Name, S.[Subject Title]
FROM Learners L, Subjects S
LEFT JOIN Enrollments E ON L.Name = E.Name AND S.[Subject Title] = E.[Subject Title]
WHERE E.Name IS NULL
hope this helps!
Hey Ryan! That’s a cool project you’re working on. 
I’m curious, have you tried using a subquery approach? Something like this might do the trick:
SELECT L.Name, S.[Subject Title]
FROM Learners L, Subjects S
WHERE NOT EXISTS (
SELECT 1
FROM Enrollments E
WHERE E.Name = L.Name
AND E.[Subject Title] = S.[Subject Title]
)
This basically checks for each learner-subject combo if it exists in the Enrollments table. If it doesn’t, it shows up in the results.
How big is your database, by the way? If it’s pretty large, you might want to consider indexing for better performance.
Oh, and have you thought about adding a date field to your Enrollments table? Could be super useful for tracking when people sign up and maybe even predicting future enrollments!
Let me know if this helps or if you need any clarification. Always fun to tackle these kinds of puzzles! 
I’ve encountered a similar challenge before, and here’s what worked for me:
Try using a UNION query to combine all possible learner-subject combinations with existing enrollments, then filter out the enrolled ones. Here’s the basic structure:
SELECT L.Name, S.[Subject Title]
FROM Learners L, Subjects S
UNION ALL
SELECT E.Name, E.[Subject Title]
FROM Enrollments E
WHERE E.Name IS NULL
This approach creates a comprehensive list and then removes duplicates, effectively showing untaken courses. You might need to tweak it slightly based on your specific table structure, but it should get you on the right track.
Remember to test it with a small dataset first to ensure it’s giving you the expected results. Good luck with your project!