Sum again

[mia]

My tab key doesn't work right, whenever I hit the tab key, it just posted
the message, sorry...

I imported two Excel worksheets, one for revenue and one for expense:

Revenue:

fund yr mo type amt
3000 03 01 11 1500
3000 03 02 11 1000

And expense
3000 03 01 22 480
3000 03 01 23 187
3000 03 02 22 420
3000 03 02 23 246
3000 03 02 24 90

When I join those two tables, this is the result (without sum):

fund yr mo rev exp
3000 03 01 1500 480
3000 03 01 1500 187
3000 03 02 1000 420
3000 03 02 1000 246
3000 03 02 1000 90

And when I used sum, I have:

fund yr mo rev exp
3000 03 01 3000 667
3000 03 02 3000 756

Another word, the revenue amount is accumulated based on the number of expense.
What can I do to have this result:

Detail:
fund yr mo rev exp
3000 03 01 1500 480
3000 03 01 187
3000 03 02 1000 420
3000 03 02 246
3000 03 02 90

and (summary):

3000 03 01 1500 667
3000 03 02 1000 756

Your help is very much appreciated.

Mia

 

[Rolls]

Suggestion:

Append the two tables or use a union query so that the result appears in one
object. Then use another query to filter the first result and summarize it.
If you want to see the rev exp fields in two columns use two IIF statements
to select the criteria. Use the expression builder to generate this: Rev:
IIF([type]=11,amt,0). Another way to obtain summary totals is to build a
query containing a WHERE clause and the criteria to filter type = 11 (rev)
or type > 11 (exp).