Solved

Access multiple outer joins

Posted on 2009-04-07
3
420 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
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:masterjojobinks
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

867 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now