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
Solved

Access multiple outer joins

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

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…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

839 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