?
Solved

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

Posted on 2014-11-19
8
Medium Priority
?
678 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
[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
  • 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
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 38

Accepted Solution

by:
PatHartman earned 2000 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 38

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

777 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