2 reports giving two different data why? | Needs Regular Fix | | Join Date: Aug 2008
Posts: 337
| |
I have a simple query called qryApDetail. SQL for this one is: - SELECT tblECHO.ECHOID, tblECHO.ECHONo, qryGrantFunds.GrantFundID, qryGrantFunds.FundNo, tblAP.ApAmount, [ApAmount]*qryGrantFunds.FedRatio AS FedAmount, tblECHO.DtReceived, Round([FedAmount]+0.0001) AS NetAmount, tblECHO.DtSubmitted, qryGrantFunds.GrantNo, qryGrantFunds.FedRatio, qryGrantProject.ProjectNo, tblAP.APID, tblAP.InvoiceDt, tblAP.InvoiceNo, tblAP.ContractNo, tblAccounts.AcctNo, tblSubClass.SubClassNo, tblVendors.VendorName
-
FROM tblVendors INNER JOIN (tblECHO INNER JOIN (((qryGrantProject INNER JOIN (qryGrantFunds INNER JOIN tblAP ON qryGrantFunds.GrantFundID = tblAP.GrantFundID) ON qryGrantProject.GrantFundID = tblAP.GrantFundID) INNER JOIN tblAccounts ON tblAP.AccountID = tblAccounts.AccountID) INNER JOIN tblSubClass ON tblAP.SubClassID = tblSubClass.SubClassID) ON tblECHO.ECHOID = tblAP.ECHOID) ON tblVendors.VendorID = tblAP.VendorID;
The other is a union query called qryUnionFund, SQL for this one is: - SELECT ECHOID, FundNo, AcctNo,SubClassNo, ProjectNo, Amount,"Allocation" as Category
-
FROM qryAllocDetail
-
-
UNION SELECT ECHOID, FundNo, AcctNo,SubClassNo, ProjectNo, PayAmount, "Payroll" as Category
-
FROM qryPayDetail;
-
-
-
UNION SELECT ECHOID, FundNo, AcctNo,SubClassNo, ProjectNo, NetAmount, "AP" as Category
-
FROM qryApDetail;
qryApDetail is pulling all records, but qryUnionFund leaving out 2 AP data. In other words, I have 3 transactions data that show up on qryApDetail but not on qryUnionFund. Why will there be this discrepency?
Can someone please help me find my error? Thank you all.
| | Needs Regular Fix | | Join Date: Aug 2008
Posts: 337
| | | re: 2 reports giving two different data why?
More info on this Union Query:
I have 3 transactions, all have the same AccountNo, FundNo, GrantNo, SubClassNo, ProjectNo and Amount. (61306 / 880 / MN95006 / 0999/ 61612 /$582,226.58.
If I change any one of these variables, let's say if I make SubclassNo 0990 and PJ08 for the two transactions, then I get all three transactions in the query. The same things happen if I change the amount for two (let's say $400,000 and 500,000 instead of $582,226.58) I get correct result.
What is it about Union Query that does not combine 3 separate records with same data variables? And if there is some restrictions, how do overwrite it so I get correct result?
Thanks.
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: 2 reports giving two different data why?
Quote:
Originally Posted by MNNovice  I have a simple query called qryApDetail. SQL for this one is:
- SELECT tblECHO.ECHOID, tblECHO.ECHONo, qryGrantFunds.GrantFundID, qryGrantFunds.FundNo, tblAP.ApAmount, [ApAmount]*qryGrantFunds.FedRatio AS FedAmount, tblECHO.DtReceived, Round([FedAmount]+0.0001) AS NetAmount, tblECHO.DtSubmitted, qryGrantFunds.GrantNo, qryGrantFunds.FedRatio, qryGrantProject.ProjectNo, tblAP.APID, tblAP.InvoiceDt, tblAP.InvoiceNo, tblAP.ContractNo, tblAccounts.AcctNo, tblSubClass.SubClassNo, tblVendors.VendorName
-
FROM tblVendors INNER JOIN (tblECHO INNER JOIN (((qryGrantProject INNER JOIN (qryGrantFunds INNER JOIN tblAP ON qryGrantFunds.GrantFundID = tblAP.GrantFundID) ON qryGrantProject.GrantFundID = tblAP.GrantFundID) INNER JOIN tblAccounts ON tblAP.AccountID = tblAccounts.AccountID) INNER JOIN tblSubClass ON tblAP.SubClassID = tblSubClass.SubClassID) ON tblECHO.ECHOID = tblAP.ECHOID) ON tblVendors.VendorID = tblAP.VendorID;
The other is a union query called qryUnionFund, SQL for this one is: - SELECT ECHOID, FundNo, AcctNo,SubClassNo, ProjectNo, Amount,"Allocation" as Category
-
FROM qryAllocDetail
-
-
UNION SELECT ECHOID, FundNo, AcctNo,SubClassNo, ProjectNo, PayAmount, "Payroll" as Category
-
FROM qryPayDetail;
-
-
-
UNION SELECT ECHOID, FundNo, AcctNo,SubClassNo, ProjectNo, NetAmount, "AP" as Category
-
FROM qryApDetail;
qryApDetail is pulling all records, but qryUnionFund leaving out 2 AP data. In other words, I have 3 transactions data that show up on qryApDetail but not on qryUnionFund. Why will there be this discrepency?
Can someone please help me find my error? Thank you all. I'm not sure that this is applicable in your case but by Default, Access eliminates Duplicate Records for UNION Queries. You can, however, force Access to include Duplicates by using the ALL Option after the UNION Operator, as in: - SELECT ECHOID, FundNo, AcctNo,SubClassNo, ProjectNo, _
-
Amount,"Allocation" as Category
-
-
FROM qryAllocDetail
-
-
UNION ALL SELECT ECHOID, FundNo, AcctNo,SubClassNo, _
-
ProjectNo, PayAmount, "Payroll" as Category
-
FROM qryPayDetail;
| | Needs Regular Fix | | Join Date: Aug 2008
Posts: 337
| | | re: 2 reports giving two different data why?
ADezii:
Thank you - it worked. I didn't know the default restriction for Union query. I appreciate your help.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: 2 reports giving two different data why?
Quote:
Originally Posted by MNNovice ADezii:
Thank you - it worked. I didn't know the default restriction for Union query. I appreciate your help. That's why we are all here, you are quite welcome.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,414 network members.
|
