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

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.
doe5Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ThomasMcA2Commented:
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
hnasrCommented:
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
doe5Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

hnasrCommented:
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
ThomasMcA2Commented:
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
PatHartmanCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
doe5Author Commented:
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
PatHartmanCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.