Solved

MS Access left join query is not displaying all records in the 'left' query.

Posted on 2014-11-19
8
474 Views
Last Modified: 2014-11-20
I've created a left join query that doesn't display all the records in the query ... it must be due to I have criteria on the other table.  It does not display student records that don't have a scholarship awarded in the 'right' trable.  Here is the SQL:
qry1.txt


I found I can use the following SQL to display all the records I want but can't update values .... I'd like to change scholarship amounts for a student.
Here is that SQL:

qry2.txt


Any advice is appreciated.
0
Comment
Question by:doe5
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 40454119
It doesn't look like you need the qryRTaylor table - it isn't used to select records, and what you want to update (the scholarship fields) are in the other table.

Based on your description of the problem and your sample code, this should work:

UPDATE tblScholarshipsAwards 
SET FallAmt = 100, SpringAmt = 200
WHERE SchID=1

Open in new window


If that is not what you are trying to do, then please clarify. It may help to attach a few lines from each file (changing names and other personally identifiable information first, of course).
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40454167
Try this:  
SELECT a.Id,  a.[Last Name], a.Maj1, 
x.SchID, x.AmtToStudent, x.FallAmt, x.SpringAmt
from qryRTaylor AS a left join (select b.SchID, b.AmtToStudent, b.FallAmt, b.SpringAmt  from tblScholarshipsAwards  AS b inner join qryRTaylor AS a  on a.ID=b.StudID where b.SchID=1) as x  on (a.ID=x.StudID)

Open in new window


Based on this scenario:
table: a (aid, adesc) all text
aID      adesc
1      a1
2      a2
9      a9
table: b(aid, bid, bdesc) all text
aID      bID      bdesc
1      1      b11
1      2      b12
1      3      b13
2      1      b21
Query:
select a.aid, a.adesc, x.aid, x.bdesc
from a left join (select b.aid, b.bdesc  from b inner join a on a.aid=b.aid where b.aid='1') as x
 on (a.aid=x.aid)

Result:
a.aid      adesc      x.aid      bdesc
1      a1      1      b13
1      a1      1      b12
1      a1      1      b11
2      a2            
9      a9
0
 

Author Comment

by:doe5
ID: 40454889
Thanks for the replies ... neither of those gave me the solutions I needed.  I'll provide more details ...

TABLE A has student records
ID, Name, FAid, Maj

TABLE B has Scholarship Money Awarded to students (StudID links to A.ID)
ID, StudID, SCHID, Amt

I want to display all the records in Table A that are 'ACC' majors and also display scholarship money that has been awarded to a student for one scholarship only - (B.SCHID=1)   AND I also want student records that are 'ACC' majors without any scholarship money for B.SCHID=1.

In the resulting datasheet I want to be able to update the scholarship fields -- award money or change an amount.
The trick seems to be when I narrow the query to adding criteria to the SCHID then the results are narrowed down to only those records that have been awarded a Scholarship with that id.

Hnsar's solution will generate the datasheet I want with all the records from Table A but I'm not allowed to update any of the fields on the resulting datasheet.  

Maybe this is too complex for a query and I should code this?

Thanks again for your replies and help.  Let me know if you want additional information or data.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 30

Expert Comment

by:hnasr
ID: 40455018
May you upload a sample database?

Include a few representative records in each table. List out the expected output with imposed criteria.

My comment was based on a working example, but replaced field names from your explanation.
It may be a table design issue with key fields and indexed fields.
0
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 40455139
You can do that with a nested query (aka, a subquery). Here is some pseudocode that should explain how to do what you need:

UPDATE tableB B
SET B.Amt = something
WHERE B.StudID IN (SELECT StudID from ...) <<< using "IN" can update multiple records

or use "=" if your (subquery) will only select 1 record, like this:

WHERE B.StudID = (SELECT StudID from ...)

The (subquery) defines which records to update, while the outer UPDATE only processes one file.
0
 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40455370
The problem is caused when you have criteria that is applied against the right-side table because the criteria is applied AFTER the join rather than before so it will eliminate left-side records as well.  The solution is to  create two queries (or use a sub query).
To implement the two query solution - create a query of just the right-side table and apply the criteria.  Name the query and save it.  Then rather than joining directly to the right-side table, join to the query you just created.  This coerces Jet/ACE into applying the criteria in the order you want it to be applied.
0
 

Author Closing Comment

by:doe5
ID: 40456342
Thank you for such a simple solution.   I appreciate that you explained why my way wasn't working ... that will be very helpful  going forward.
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 40456452
You are welcome.  This is one of those issues that has burned me in the past - more than once.  Determining the cause of the problem and understanding why was very painful.  But I find that understanding why goes a long way toward future prevention.  You are more likely to remember a "rule" you understand.  Don't apply criteria to the right side of a Left join (or the left side of a right join).  This may be a Jet/ACE only issue.  I don't know if SQL Server makes the same mistake.

This solution (two queries) can sometimes help speed up slow queries by forcing Jet/ACE to perform a database operation in a specific sequence.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

786 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