Solved

Access multiple outer joins

Posted on 2009-04-07
3
426 Views
Last Modified: 2013-11-29
I have a user wanting to convert a flat FileMaker Pro database into Access. The database (which is attached) tracks students and different "matrix logic" tests they have completed, and the user is accustomed to seeing ALL of the tests, and dates filled in as the tests are completed. I'm able to outer join the "test list" to the "completed list", but outer joining that to the "student list" won't show the non-taken tests. I've wracked my brain but can't get this to pull through (and it's not helping my marketing efforts of why Access is better than FileMaker!)
ml.mdb
0
Comment
Question by:DerryLyons
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 5

Accepted Solution

by:
Mike77 earned 250 total points
ID: 24092010
Hi,
I'm not too sure I understand what you mean, but could something like this do :

Query1 (gets all the student - test possible combinations):
SELECT ML_STUDENTS.STU_ID, ML_STUDENTS.STU_LAST, ML_STUDENTS.STU_FIRST, ML_STUDENTS.STU_GRADE, ML_LOOKUP.ML_ID, ML_LOOKUP.ML_SORT, ML_LOOKUP.ML_LEVEL
FROM ML_LOOKUP, ML_STUDENTS
ORDER BY ML_STUDENTS.STU_LAST, ML_STUDENTS.STU_FIRST, ML_LOOKUP.ML_SORT;

Query2 (gets the results) :
SELECT Query1.STU_ID, Query1.STU_LAST, Query1.STU_FIRST, Query1.STU_GRADE, Query1.ML_ID, Query1.ML_SORT, Query1.ML_LEVEL, ML_COMPLETERS.ML_TAKEN, ML_COMPLETERS.ML_REDONE
FROM Query1 LEFT JOIN ML_COMPLETERS ON (Query1.ML_ID = ML_COMPLETERS.ML_TEST) AND (Query1.STU_ID = ML_COMPLETERS.ML_STU)
ORDER BY Query1.STU_LAST, Query1.STU_FIRST, Query1.ML_SORT;
0
 
LVL 8

Expert Comment

by:Joe Overman
ID: 24092063
Mike77 took the words right off my keyboard.  Good job.
0
 

Author Closing Comment

by:DerryLyons
ID: 31567713
That will do the trick! Looks like I was trying to join the tables together too soon, then not thinking about a compound left join later. Thanks!!
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question