Solved

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

Posted on 2014-11-19
8
407 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
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.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

759 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

20 Experts available now in Live!

Get 1:1 Help Now