Combining Querries results in repeat data

[MNNovice]

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.

[ChipR]

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?

[MNNovice]

ChipR:

Here are the SQL info on my queries.

QUERY 1 (AP Expenses)
qryECHOAPDetail

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
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
ORDER BY tblGrants.GrantNo, qryGrantFunds.FundNo, tblProjects.ProjectNo;

QUERY 2 (Payroll Expenses)
qryECHOPayDetai l

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
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
ORDER BY tblGrants.GrantNo, qryGrantFunds.FundNo, tblProjects.ProjectNo;

QUERY 3 (Allocation Cost)
qryEchoAllocati onDetail

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
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
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.

[ChipR]

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.

[MNNovice]

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

SELECT tblFunds.FundNo, qryECHOAPDetail.ProjectNo, qryEchoAllocDetail.ProjectNo, qryECHOPayDetail.ProjectNo, qryECHOAPDetail.NetAmount, qryEchoAllocDetail.Amount, qryECHOPayDetail.PayAmount
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.

[MNNovice]

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.

SELECT ECHOID, FundNo, SubClassNo, ProjectNo, Amount,"Allocation" as Category
FROM qryEchoAllocDetail

UNION SELECT ECHOID, FundNo, SubClassNo, ProjectNo, PayAmount, "Payroll" as Category
FROM qryECHOPayDetail;


UNION SELECT ECHOID, FundNo, SubClassNo, ProjectNo, ApAmount, "AP" as Category
FROM qryECHOAPDetail;

[Denburt]

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

[NeoPa]

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.