How do I combine multiple queries? 1

[R00K]

Good morning,

I have several queries that give various values. I wish to find the sum of all the queries ordered by date.

Here is a sample:

SELECT ControlDates.BalanceDate, Sum(Deposit-Withdrawal) As Balance
FROM Transactions, ControlDates
WHERE TransDate<=BalanceDate
GROUP BY ControlDates.BalanceDate;

SELECT ProfitLoss.PLDate, Sum((ProfitLoss!Gain-(ProfitLoss!Gain*0.2))-ProfitLoss!InterestMargin) AS Netgain
FROM ProfitLoss
GROUP BY ProfitLoss.PLDate;

SELECT RunningBalance!BalanceDate AS Balance, RunningBalance!Expr2*0.000054794 AS ManagementFee
FROM RunningBalance
ORDER BY RunningBalance!BalanceDate;

There are several other queries. I am after a single number resulting from the queries. Let me know if I am going about this the wrong way.

Thank you.

 

[sxschech]

So your end result would be:

Case 1:

Date Amount
12/15/2003 808 ==> Balance+NetGain+ManagementFee

If no common date, then omit.
--------

Or would it be

Case 2:

Date Balance NetGain ManagementFee
12/15/2003 500 230 78


Case 1 would be a union query and then do a summary off the union query

Case 2 would require joining by Date, or another common field of all the queries.

 

[SundancerKid]

Here is an example of a Union Query

SELECT City, CompanyName, ContactName, &quot;Customers&quot; AS [Relationship]
FROM Customers
UNION ALL
SELECT City, CompanyName, ContactName, &quot;Suppliers&quot; AS
[Relationship]
FROM Suppliers
ORDER BY City, CompanyName;