In addition to John's solution, you can try:
SELECT DISTINCT a.Aid, a.Acity, a.Astate, a.Adate, f.Fid, f.ASSETS, f.AssetsDate
FROM AttrTbl a LEFT JOIN FinTbl f ON a.Aid = f.Fid
WHERE a.Aid=12345 AND a.Adate=99991231 AND Nz(f.Assetsdate,20140331)=20140331;
You would want to make sure you don't get erroneous results.
Also, are you creating this query in Access or are you using a pass-through. If you don't need to edit the results, you may get much better performance using a pass-through query.
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 11 Sep 2014 07:44:01 -0700
Subject: Re: [MS_AccessPros] What causes an outer join fail to work when use with remote tables?
Thank you all.
"causes an outer join to fail" I was trying to say my Left Outer Join query display an Inner Join result. Sorry for the confusion.
The table's stored in SQL Server(s), and is located in another state. The tables are big and are read only tables. I would say a hundred thousand rows. The ID field is the indexed field.
My querie's exactly like Crystal discribed. I do have CRITERIA in the outer joined table. Why does it shows only Inner Join result when there's criteria in the query?
Below is the SQL that I've tried.
'the result of this query displays only Inner Join
SELECT DISTINCT a.Aid, a.Acity, a.Astate, a.Adate, f.Fid, f.ASSETS, f.AssetsDate
FROM AttrTbl a LEFT JOIN FinTbl f ON a.Aid = f.Fid
WHERE (((a.Aid)=12345) AND ((a.Adate)=99991231) AND ((f.Assetsdate)=20140331));
'i then tried another approach, do it in 3 steps, then i got the correct result - the Outer Join.
'step 1 get the attribute info
SELECT DISTINCT a.Aid, a.Acity, a.Astate, a.Adate
FROM AttrTbl
WHERE (a.Aid = 12345) And (a.Adate = 99991231)
'step 2 get the financial info, filter by financial date
SELECT f.Fid, f.ASSETS, f.AssetsDate
FROM FnTbl f
WHERE f.dt=20140331;
'step 3 get final output, join the 2 queries
SELECT a.*, f.* FROM qryA a LEFT JOIN qryF ON a.Aid = f.Fid
What are the other better ways to code, besides the 3 steps approach?
Thank you again.
SELECT DISTINCT a.Aid, a.Acity, a.Astate, a.Adate, f.Fid, f.ASSETS, f.AssetsDate
FROM AttrTbl a LEFT JOIN FinTbl f ON a.Aid = f.Fid
WHERE a.Aid=12345 AND a.Adate=99991231 AND Nz(f.Assetsdate,20140331)=20140331;
You would want to make sure you don't get erroneous results.
Also, are you creating this query in Access or are you using a pass-through. If you don't need to edit the results, you may get much better performance using a pass-through query.
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 11 Sep 2014 07:44:01 -0700
Subject: Re: [MS_AccessPros] What causes an outer join fail to work when use with remote tables?
Thank you all.
"causes an outer join to fail" I was trying to say my Left Outer Join query display an Inner Join result. Sorry for the confusion.
The table's stored in SQL Server(s), and is located in another state. The tables are big and are read only tables. I would say a hundred thousand rows. The ID field is the indexed field.
My querie's exactly like Crystal discribed. I do have CRITERIA in the outer joined table. Why does it shows only Inner Join result when there's criteria in the query?
Below is the SQL that I've tried.
'the result of this query displays only Inner Join
SELECT DISTINCT a.Aid, a.Acity, a.Astate, a.Adate, f.Fid, f.ASSETS, f.AssetsDate
FROM AttrTbl a LEFT JOIN FinTbl f ON a.Aid = f.Fid
WHERE (((a.Aid)=12345) AND ((a.Adate)=99991231) AND ((f.Assetsdate)=20140331));
'i then tried another approach, do it in 3 steps, then i got the correct result - the Outer Join.
'step 1 get the attribute info
SELECT DISTINCT a.Aid, a.Acity, a.Astate, a.Adate
FROM AttrTbl
WHERE (a.Aid = 12345) And (a.Adate = 99991231)
'step 2 get the financial info, filter by financial date
SELECT f.Fid, f.ASSETS, f.AssetsDate
FROM FnTbl f
WHERE f.dt=20140331;
'step 3 get final output, join the 2 queries
SELECT a.*, f.* FROM qryA a LEFT JOIN qryF ON a.Aid = f.Fid
What are the other better ways to code, besides the 3 steps approach?
Thank you again.
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (9) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar