Re: [MS_AccessPros] Joined Query

 

Kevin,

I'm not sure I fully understand your issue however the WHERE clause must be included in every part of the union query.  Placing a WHERE only in the last section of the union query only affects the last section.

Regards,

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Kevin qingqinga@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, April 3, 2019 7:13 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Joined Query

 

Dear All,

Here is the union query：

SELECT All
ID,LetterType,TransactionID,TransactionType,OfficeLocation,CostType,Amount,RecordDate,IIf([Checked]=True,"已审核","") As CheckAlready,GroupAndRemark,AccountID,ActualTransactionDate,Month(ActualTransactionDate) As ActualMonth,ELookup("AccountName","tblAccount","AccountID=" & Nz([AccountID],0)) As Account,Year(ActualTransactionDate) As ActualYear,FinalAmount,Checked FROM qryaTransaction

UNION All SELECT
ID,LetterType,ManagerialCostID,TransactionType,OfficeLocation,ManagerialCostType,Amount,RecordDate,IIf([Checked]=True,"已审核","") As CheckAlready,Remark,AccountID,ActualTransactionDate,Month(ActualTransactionDate) As ActualMonth,ELookup("AccountName","tblAccount","AccountID=" & Nz([AccountID],0)) As Account,Year(ActualTransactionDate) As ActualYear,FinalAmount,Checked FROM qrybManagerialCost

UNION All SELECT
ID,LetterType,PromotionCostID,TransactionType,OfficeLocation,PromotionCostType,Amount,RecordedDate,IIf([Checked]=True,"已审核","") As CheckAlready,Remark,AccountID,ActualTransactionDate,Month(ActualTransactionDate) As ActualMonth,ELookup("AccountName","tblAccount","AccountID=" & Nz([AccountID],0)) As Account,Year(ActualTransactionDate) As ActualYear,FinalAmount,Checked FROM qrycPromotionCost

UNION All SELECT
ID,LetterType,OtherIncomeID,TransactionType,OfficeLocation,OtherIncomeType,Amount,RecordDate,IIf([Checked]=True,"已审核","") As CheckAlready,Remark,AccountID,ActualOtherIncomeDate,Month(ActualOtherIncomeDate) As ActualMonth,ELookup("AccountName","tblAccount","AccountID=" & Nz([AccountID],0)) As Account,Year(ActualOtherIncomeDate) As ActualYear,FinalAmount,Checked FROM qrydOtherIncome

UNION ALL SELECT
ID,LetterType,OtherCostID,TransactionType,OfficeLocation,OtherCostType,Amount,RecordDate,IIf([Checked]=True,"已审核","") As CheckAlready,Remark,AccountID,ActualOtherCostDate,Month(ActualOtherCostDate) As ActualMonth,ELookup("AccountName","tblAccount","AccountID=" & Nz([AccountID],0)) As Account,Year(ActualOtherCostDate) As ActualYear,FinalAmount,Checked FROM qryeOtherCost
WHERE ((ActualMonth)=11 AND (ActualYear)=2018);

Best Regards,

Kevin

On 03-Apr-2019 7:45 PM, Kevin qingqinga@yahoo.com [MS_Access_Professionals] wrote:

 

Dear All,

I made a mistake, it's union query, not "joned query".

Best Regards,

Kevin

On 03-Apr-2019 6:52 PM, Kevin qingqinga@yahoo.com
[MS_Access_Professionals] wrote:
> Dear All,
>
> In a joined query, I put the condition as below:
>
> WHERE ((11)=1 AND (15)=Forms!frmAllReceipt!TransactionYear AND
> (13)=Forms!frmAllReceipt!TransactionMonth);
>
> It does work well with 15 and 13, but 11 doesn't screen out the expected
> result. behind the 11 is AcountID.
>
> Please help. Thanks in advance.
>
> Best Regards,
>
> Kevin

__._,_.___

---

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 (5)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

SPONSORED LINKS

.

__,_._,___