Solved

Parameter with Left join

Posted on 2014-10-15
17
205 Views
Last Modified: 2015-05-17
I have created a left query and on the second table I also provide a parameter . I now need to show all records which correspond to that month as well as all records from first table for which there is no record for that month in second table.

However the query does not output it for 2nd part. How do I get this.

SQL is as

SELECT Organisation.[UEN Number], Organisation.Name1, Organisation.Channel, Audit_Profile.[Risk Category], Audit_Profile.[Country of promoter], Audit_Profile.[Number of promoters], Audit_Profile.[Annual Turnover KYC], Audit_Profile.[Monthly incoming KYC], Audit_Profile.[Monthly outgoing KYC], Audit_Bank_review.[For month], Audit_Bank_review.Bank, Audit_Bank_review.[Acct currency], Audit_Bank_review.[Total deposits], Audit_Bank_review.[Total withdrawals], Audit_Bank_review.[Single largest TX], Audit_Bank_review.[Opening balance], Audit_Bank_review.[Closing Balance]
FROM (Organisation INNER JOIN Audit_Profile ON Organisation.[UEN Number] = Audit_Profile.[UEN Number]) LEFT JOIN Audit_Bank_review ON Organisation.[UEN Number] = Audit_Bank_review.[UEN Number]
WHERE (((Organisation.Channel)="Foreign incorporation") AND ((Audit_Bank_review.[For month])=[Which month?]));

So in this query - if I input month as "September" I expect all records for september + blank records to be displayed. But it is not happening.
0
Comment
Question by:Vaibhavjoshi2005
  • 4
  • 4
  • 3
  • +3
17 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40381599
Hello,

use LEFT OUTER JOIN
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40381608
It will only do that where Organisation.Channel="Foreign incorporation"; how many records do you have with that?
0
 
LVL 1

Author Comment

by:Vaibhavjoshi2005
ID: 40381611
Tried. Doesn't work. It still does not give me the blank records.
0
 
LVL 1

Author Comment

by:Vaibhavjoshi2005
ID: 40381612
100 records as of now. I dont have problem with Channel. But what I want is display all records for month September + all records for which there is no record in review table for september.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40381615
Two questions:

1. What sort of values do you have in Audit_Bank_review.[For month]? If it is 1-Sep-2014, then you have to type 1-Sep-2014.

2. I think you need a RIGHT JOIN instead of a LEFT JOIN.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40381621
You also need to change

((Audit_Bank_review.[For month])=[Which month?]))

to

((Audit_Bank_review.[For month])=[Which month?]) OR Audit_Bank_review.[For month] IS NULL)
0
 
LVL 1

Author Comment

by:Vaibhavjoshi2005
ID: 40381633
Here is more explanation;
Organisation table states the details of organization and audit review stores record of their bank transactions for month (By various months – like august, September, October etc). So there is one to many relationship.
Now if I execute for the month of September I want to see all records from Audit review with September month in record + all organisations for which there is no September record.

I can not use

((Audit_Bank_review.[For month])=[Which month?]) OR Audit_Bank_review.[For month] IS NULL)
Because an organization may have record for August but not for September (which will not be returned in this case)

I can not use right join, as I can not see blanks in this case .
0
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.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40381638
How about this? Change:

LEFT JOIN Audit_Bank_review ON Organisation.[UEN Number] = Audit_Bank_review.[UEN Number]
WHERE (((Organisation.Channel)="Foreign incorporation") AND ((Audit_Bank_review.[For month])=[Which month?]));

to

RIGHT JOIN Audit_Bank_review ON Organisation.[UEN Number] = Audit_Bank_review.[UEN Number] AND Audit_Bank_review.[For month])=[Which month?]
WHERE (((Organisation.Channel)="Foreign incorporation");
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 40381768
In such cases I create a separate query:

Select
    Audit_Bank_review.[UEN Number]
From
    Audit_Bank_review
WHERE
    Audit_Bank_review.[For month] = [Which month?]

Save this and use it in the main query with the Left outer join.

/gustav
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 40381800
I use the same technique as Gustav, but rather than saving the query, I simply make it a subquery.

SELECT A.*, B.*
FROM A
LEFT JOIN (
SELECT *
FROM yourTable
WHERE [someField] = "SomeValue"
) as B
ON A.KeyField = B.KeyField

The reason the LEFT OUTER JOIN does not work is that as soon as you ADD the Criteria to the 2nd table, that negates the OUTER portion of the JOIN.  Another way to do this which might return WHAT you are looking for is:

SELECT A.*, B.*
FROM A
LEFT JOIN B
ON A.JoinField = B.JoinField
WHERE ((B.SomeField IS NULL) OR (B.SomeField = "SomeValue"))
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40382499
The separate query suggested by Gus or the sub query suggested by Dale both work.  The REASON you need to do this is because of the way joins work and when criteria is applied.  So, the database engine is performing the Left Join FIRST and then applying the criteria to the many-side table.  That is why you are losing the empty records.  To get around this, you need to coerce the query engine into applying the many-side criteria FIRST and then doing the join so you apply the criteria in a separate query or subquery and then left join to the query.  That preserves the intention of the left join.

Without knowing the reason, you can't ever apply what we tell you correctly in other situations.  So, in summary, if you need a Left join and you have criteria on the many-side, you need to separate it to force the criteria to be applied first and then do the join.  There is no problem with criteria on the many-side table in an inner join because you are only trying to return rows that match.
0
 
LVL 1

Author Comment

by:Vaibhavjoshi2005
ID: 40772387
I've requested that this question be deleted for the following reason:

no good solution
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40772758
No good solution?
Gus and Dale gave you query options and I explained WHY the problem exists.  Just because you don't like the answer doesn't mean it isn't a good solution.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40782181
I think either Dale or Gus' solution will work since the point is to isolate the criteria being applied to the table of the right join BEFORE the join takes place.  So you can split between them.  If you are feeling generous, you can give me points for explaining WHY this must be done.
0

Featured Post

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.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

910 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