report using two queries for input

[jozino01]

hi,

one query looks like:

SELECT Sum(EXPENSES.AMOUNT) AS TOT_EXPENSE, EXPENSES.ACCT_NUM, EXPENSES.DATE
FROM EXPENSES
GROUP BY EXPENSES.ACCT_NUM, EXPENSES.DATE;

TOT_EXPENSE ACCT_NUM DATE
$3,464.97 01 3/08/05
$2,240.00 01 4/07/05
$2,128.00 01 4/08/05
$72,589.36 02 6/15/05
$1,110.00 02 6/17/05
$55,000.00 04 6/01/05
$51,150.12 04 7/18/05
$250,000.00 08 6/08/04
$114,857.53 08 8/01/04
$1,620.00 08 8/13/04

second query looks like:

SELECT Sum(([REVENUE].[CAD]+[B/g_VALUE]+[B/S_VALUE]+[LOGS_SCALING]+[B/S_SCALING])) AS TOT_REV, [REVENUE].[ACCT_NUM], [REVENUE].[DATE]
FROM REVENUE
GROUP BY [REVENUE].[DATE], [REVENUE].[ACCT_NUM];

TOT_REV ACCT_NUM DATE
$1,094,771.13 87 30-Jun-02
$411,998.03 87 31-Jul-02
$1,198,537.16 87 31-Aug-02
$435,665.84 87 30-Sep-02
$1,570.99 87 30-Nov-02
$8,798.25 87 16-Jan-03
$26,057.62 87 27-Jan-03
$85,316.76 50 28-Feb-03
$25,376.57 87 28-Feb-03
$146,721.73 50 11-Mar-03
$524,611.20 71 05-Jun-03

I need to create a report showing:

acct_num month/year tot_revenue tot_exepense balance

grouped by acct_num and month/year

where balance=tot_revenue - tot_expense

for some months there are no data (either revenue or expenses) for certain acct_num

any advice, please?

 

[GingerR]

in a query, bring in your Accounts table. then bring in these two queries above. Make outer joins from your Accounts table to these two queries. This means draw the join arrows so that they start at the Accounts table (on Account Number) and point to the account number fields in the two queries. This will show ALL Account Numbers, and any related data from these two queries. From there you should be able to bring down the fields you need, as well as your calculations. Remember to accomodate items that have nothing to report: use something like:

Expense: nz(TOT_EXPENSE,0) Revenue: nz(TOT_REV,0)

you won't be able to subtract the two if any are blank/null, so use NZ to change them to zeros.

Try that out and see how far you get.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html