Union Query Report

[pdehner]

I am still having difficulty and still need help.
I have now 4 tables. - Please not all the fields names below are correct
(shorten for readability)

Funds - AccountID, FundName, FundDescript, Fed_State_Other
Allocation - AccountID, FY, AllowAmt, AllowDate, AllowDescrip
Requested - AccountID, FY, RequestAmt, RequestDate, RequestDescrip
Transactions - AccountID, FY, TranAmt, TranDate, TranDescrip

I need a report that allows the user to enter the FY (fiscal year) they are
requesting and show the
fund fundname Fed_State_Other
Allowdate, AllowAmt, AllowDescrip
RequestDate, RequestAmt, RequestDescrip
TranDate, Tranamt, TranDescrip

All grouped by Fund, subtototalling on allowamt, requestamt, tranamt, then
showing balance totalallowamt - tranamt

Not all Funds will have allowamts but should be shown, Not all Funds will
have requestedamts, Not all Funds will have TranAmts

I created a union join and have a query that includes all the information
with a type field
SELECT [AllocationRequestAmount] AS [AMT], [AllocationRequestDate] AS
[TDate], [AllocationDescription] AS [Desc], [FY] AS [Fiscal], [AccountID] AS
[Account], "A" AS TYPE FROM [FundsRequested]
UNION SELECT [TransactionAmount] AS [AMT], [TransactionDate] AS [TDate],
[TransactionDescription] AS [Desc], [FY] AS [Fiscal], [AccountID] As
[Account] , "C" AS TYPE FROM [FundTransactions]
UNION SELECT [AllocationAMT] AS [AMT], [AllocationDate] AS [TDATE],
[AllocationDescription] AS [Desc], [FY] as [Fiscal], [AccountID] AS
[Account], "R" AS TYPE FROm [Allocations];

Now I need to sum the AMT field based on the TYPE field Then produce the
report.

any ideas???
thanks in advance