By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,316 Members | 2,317 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,316 IT Pros & Developers. It's quick & easy.

2 reports giving two different data why?

100+
P: 418
I have a simple query called qryApDetail. SQL for this one is:

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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:

Expand|Select|Wrap|Line Numbers
  1. SELECT ECHOID, FundNo, AcctNo,SubClassNo, ProjectNo, Amount,"Allocation" as Category
  2. FROM qryAllocDetail
  3.  
  4. UNION SELECT ECHOID, FundNo, AcctNo,SubClassNo, ProjectNo, PayAmount, "Payroll" as Category
  5. FROM qryPayDetail;
  6.  
  7.  
  8. UNION SELECT ECHOID, FundNo, AcctNo,SubClassNo, ProjectNo, NetAmount, "AP" as Category
  9. 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.
Jun 29 '09 #1
Share this Question
Share on Google+
4 Replies


100+
P: 418
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.
Jun 29 '09 #2

ADezii
Expert 5K+
P: 8,679
@MNNovice
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT ECHOID, FundNo, AcctNo,SubClassNo, ProjectNo, _
  2.                Amount,"Allocation" as Category
  3.  
  4. FROM qryAllocDetail
  5.  
  6. UNION ALL SELECT ECHOID, FundNo, AcctNo,SubClassNo, _
  7.                ProjectNo, PayAmount, "Payroll" as Category
  8.                FROM qryPayDetail;
Jun 29 '09 #3

100+
P: 418
ADezii:

Thank you - it worked. I didn't know the default restriction for Union query. I appreciate your help.
Jun 30 '09 #4

ADezii
Expert 5K+
P: 8,679
@MNNovice
That's why we are all here, you are quite welcome.
Jun 30 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.