473,320 Members | 1,848 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Combining Querries results in repeat data

418 256MB
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
7 2717
ChipR
1,287 Expert 1GB
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
MNNovice
418 256MB
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
ChipR
1,287 Expert 1GB
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
MNNovice
418 256MB
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
MNNovice
418 256MB
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
1,356 Expert 1GB
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
32,556 Expert Mod 16PB
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

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

Similar topics

6
by: Alan | last post by:
I'm just about to start a project that needs to combine the results of a SQL Server query with the results of an Index Server query. The basic idea is that the user enters/selects a bunch of search...
8
by: Ilan | last post by:
Hi all I need to add data from two Excel sheets (both on the same workbook) to an existing table in my SQL DB. The problem is that each sheet holds different fields for the same record, though...
3
by: alwayswinter | last post by:
I currently have a form where a user can enter results from a genetic test. I also have a pool of summaries that would correspond to different results that a user would enter into the form. I...
1
by: Richard Holliingsworth | last post by:
Hello: Thanks for reading this. We just converted to A2002 and some of my querries have stopped working and I can't figure out why. Basically, anything with a "NOT" is not working. For...
5
by: M.Stanley | last post by:
Hi, I'm attempting to create a query that will combine 2 columns of numbers into one. The followng comes from 1 table with 4 fields (A,B,C,D) A B RESULT 700 000 700000 700 001 ...
1
by: striker77 | last post by:
i get a list of results based on an id and based on a status value that can be 'active', 'inprocess' or 'cancel' - it is possible that the same user has both 'active' and 'inprocess' states...
3
by: Jacko7289 | last post by:
Hi, It's me again :-) Just wondering if its possible to create one report using the output of two or more SQL Querries. Currently i've got two SQL querries. one generates the total number of...
2
by: J055 | last post by:
Hi I need to search a number of DataTables within a DataSet (with some relationships) and then display the filtered results in a GridView. The Columns that need to be displayed come from 2 of...
3
by: Ken Fine | last post by:
This is a question that someone familiar with ASP.NET and ADO.NET DataSets and DataTables should be able to answer fairly easily. The basic question is how I can efficiently match data from one...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.