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

Combining Querries results in repeat data

100+
P: 418
I have 3 queries that show expenses on various projects/funds. I would like to combine the results and show various expenses on each project/fund etc.

But when I try to combine these 3 queries, the data gets repeated multiple times and the result becomes huge.

Query 1: qryAPDetail has 92 records
Query 2: qryPayDetail has 77 records
Query 3: qryAllocDetail has 26 records

How do I combine these queries to get a combined result without a single data bering repeated?

Thanks.
Jun 3 '09 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 1,287
I don't think there is enough information to offer assistance. Are these queries dealing with the same dataset? Are you trying to combine them with a UNION?
Jun 3 '09 #2

100+
P: 418
ChipR:

Here are the SQL info on my queries.

QUERY 1 (AP Expenses)
qryECHOAPDetail


Expand|Select|Wrap|Line Numbers
  1. SELECT tblECHO.ECHOID, tblGrants.GrantNo, tblAccounts.AcctNo, tblAP.InvoiceDt, qryGrantFunds.FundNo, tblProjects.ProjectNo, tblAP.InvoiceNo, tblVendors.VendorName, tblSubClass.SubClassNo, tblAP.ContractNo, tblECHO.ECHONo, tblGrants.FedRatio, tblECHO.DtSubmitted, tblAP.ApAmount, tblECHO.DtReceived, tblAP.APID, [ApAmount]*[FedRatio] AS FedAmount, Round([FedAmount]+0.0001) AS NetAmount
  2. FROM tblVendors INNER JOIN (tblGrants RIGHT JOIN ((tblGrantFunds INNER JOIN qryGrantFunds ON tblGrantFunds.GrantFundID = qryGrantFunds.GrantFundID) INNER JOIN (tblECHO INNER JOIN (((tblAP INNER JOIN tblAccounts ON tblAP.AccountID = tblAccounts.AccountID) INNER JOIN tblSubClass ON tblAP.SubClassID = tblSubClass.SubClassID) INNER JOIN tblProjects ON tblAP.ProjectID = tblProjects.ProjectID) ON tblECHO.ECHOID = tblAP.ECHOID) ON tblGrantFunds.GrantFundID = tblAP.GrantFundID) ON tblGrants.GrantID = tblGrantFunds.GrandID) ON tblVendors.VendorID = tblAP.VendorID
  3. ORDER BY tblGrants.GrantNo, qryGrantFunds.FundNo, tblProjects.ProjectNo;

QUERY 2 (Payroll Expenses)
qryECHOPayDetail



Expand|Select|Wrap|Line Numbers
  1. SELECT tblECHO.ECHOID, tblGrants.GrantNo, tblAccounts.AcctNo, qryGrantFunds.FundNo, tblProjects.ProjectNo, tblPayroll.PayrollNo, tblPayroll.PayrollDate, tblSubClass.SubClassNo, tblECHO.ECHONo, tblECHO.DtSubmitted, tblPayroll.PayAmount, tblECHO.DtReceived, tblPayroll.PayrollID
  2. FROM tblSubClass INNER JOIN (tblProjects INNER JOIN (tblGrants RIGHT JOIN (tblECHO INNER JOIN (tblAccounts INNER JOIN ((tblGrantFunds INNER JOIN qryGrantFunds ON tblGrantFunds.GrantFundID = qryGrantFunds.GrantFundID) INNER JOIN tblPayroll ON tblGrantFunds.GrantFundID = tblPayroll.GrantFundID) ON tblAccounts.AccountID = tblPayroll.AccountID) ON tblECHO.ECHOID = tblPayroll.ECHOID) ON tblGrants.GrantID = tblGrantFunds.GrandID) ON tblProjects.ProjectID = tblPayroll.ProjectID) ON tblSubClass.SubClassID = tblPayroll.SubClassID
  3. ORDER BY tblGrants.GrantNo, qryGrantFunds.FundNo, tblProjects.ProjectNo;


QUERY 3 (Allocation Cost)
qryEchoAllocationDetail


Expand|Select|Wrap|Line Numbers
  1. SELECT tblECHO.ECHOID, tblGrants.GrantNo, tblAccounts.AcctNo, qryGrantFunds.FundNo, tblProjects.ProjectNo, tblAllocation.AllocationDate, tblSubClass.SubClassNo, tblECHO.ECHONo, tblECHO.DtSubmitted, tblAllocation.Amount, tblECHO.DtReceived, tblAllocation.AllocationID
  2. FROM tblSubClass INNER JOIN (tblProjects INNER JOIN (tblGrants RIGHT JOIN (tblECHO INNER JOIN (tblAccounts INNER JOIN ((tblGrantFunds INNER JOIN qryGrantFunds ON tblGrantFunds.GrantFundID = qryGrantFunds.GrantFundID) INNER JOIN tblAllocation ON tblGrantFunds.GrantFundID = tblAllocation.GrantFundID) ON tblAccounts.AccountID = tblAllocation.AccountID) ON tblECHO.ECHOID = tblAllocation.ECHOID) ON tblGrants.GrantID = tblGrantFunds.GrandID) ON tblProjects.ProjectID = tblAllocation.ProjectID) ON tblSubClass.SubClassID = tblAllocation.SubClassID
  3. ORDER BY tblGrants.GrantNo, qryGrantFunds.FundNo, tblProjects.ProjectNo;
What I want is a report to show combined result of these 3 queries. Which is why I need a combined query first.

The results should reflect expenses by Fund Number (FundNo). So that we can see how much money is spent in each Fund by SubClassNo and by Project within each category (AP, Payroll and Allocation).

I tried other threads that deal with Cross Tab Query, Union Query but I was not successful in getting the result I need.

Hope this makes sense. Thanks for your offer of help and for your time.
Jun 3 '09 #3

Expert 100+
P: 1,287
It seems you want to list these things by Fund Number, so add all 3 queries to a new query that includes a table (or query) listing the Fund Numbers. Join them on the Fund Numbers, and then add the fields you need.
Jun 3 '09 #4

100+
P: 418
ChipR:

It didn't work for me. I must be doing something wrong and am certain I didn't quite understand what you have asked me to do. But here is the new query SQL

Expand|Select|Wrap|Line Numbers
  1. SELECT tblFunds.FundNo, qryECHOAPDetail.ProjectNo, qryEchoAllocDetail.ProjectNo, qryECHOPayDetail.ProjectNo, qryECHOAPDetail.NetAmount, qryEchoAllocDetail.Amount, qryECHOPayDetail.PayAmount
  2. FROM qryECHOPayDetail INNER JOIN ((tblFunds INNER JOIN qryECHOAPDetail ON tblFunds.FundNo = qryECHOAPDetail.FundNo) INNER JOIN qryEchoAllocDetail ON tblFunds.FundNo = qryEchoAllocDetail.FundNo) ON qryECHOPayDetail.FundNo = qryECHOAPDetail.FundNo;
It gives me 1,563 records some of which are repeated. I tried to pull data for ProjectNo only. Since I didn't succeed with the ProjectNo. I didn't try with SubClassNo.

What I needed should look like this:

FundNo 871

EchoNo 09-066

SubClassNo 0999
ProjectNo 61750
ApAmount 40,529
PayAmount 13,319
AllocationCost 6,771


ProjectNo 62314
ApAmount 0
PayAmount 4,110
AllocCost 2,066


ProjectNo 65652
ApAmount 0
PayAmount 40
AllocCost 196

Project 61750 Total 60,419
Project 62314 Total 6,176
Project 65652 Total 236

SubClass 0999 Total 66,831
Fund 871 Total 66,831

It needs to combine these costs for all ECHOs. I only showed example from ECHO No. 09-066

Hope this makes sense. Thanks for your time and effort in finding a solution for me.
Jun 3 '09 #5

100+
P: 418
Well, I found a solution to my union query with the help from Denburt. Here is what the SQL and it's working. Thanks for your time ChipR.

Expand|Select|Wrap|Line Numbers
  1. SELECT ECHOID, FundNo, SubClassNo, ProjectNo, Amount,"Allocation" as Category
  2. FROM qryEchoAllocDetail
  3.  
  4. UNION SELECT ECHOID, FundNo, SubClassNo, ProjectNo, PayAmount, "Payroll" as Category
  5. FROM qryECHOPayDetail;
  6.  
  7.  
  8. UNION SELECT ECHOID, FundNo, SubClassNo, ProjectNo, ApAmount, "AP" as Category
  9. FROM qryECHOAPDetail;
Jun 3 '09 #6

Denburt
Expert 100+
P: 1,356
A quick note, a Union Select will not show any duplicates, you might want to use a UNION ALL SELECT instead depending on the results you are looking for.
Here is a link that you may find useful.
http://www.w3schools.com/sql/sql_union.asp
Jun 3 '09 #7

NeoPa
Expert Mod 15k+
P: 31,709
Joining data from different record sources can be done in many ways depending on exactly how you want them joined.

I seriously suggest you follow the link Den provided, as understanding this area of database work would benefit you greatly M (if my guess is right).
  • UNION -> Used for when you want want multiple data sources to be treated as a continued set of data. Essentially a single data source, but coming from multiple places.
  • JOIN -> Used for when you want the data from multiple sources to appear all together in a single output record. An example would be for lookups. Some data from both sources should match for this method (Normally PK == FK). Types of usable JOINs in Access are INNER JOIN, LEFT JOIN (outer) and RIGHT JOIN (outer).
  • FULL OUTER -> This is a full Cartesian Product. You were probably experiencing a little of this earlier. This is where each record from one source matches up with each record of every other one.
    EG. Table A has three records - A, B & C and Table 2 has two records - 1 & 2.
    With a FULL OUTER JOIN the results would be :
    A 1
    A 2
    B 1
    B 2
    C 1
    C 2

    It is possible to restrict the output records using filtering (WHERE clause), but if not you see the full cartesian product of the multiple record sources.
Jun 4 '09 #8

Post your reply

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