Solved

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

Posted on 2014-11-19
8
443 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 34

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 34

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 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

19 Experts available now in Live!

Get 1:1 Help Now