Hello,

I have a table: tblMonthly.
w/ fields:
fldID:Auto
fldDate http://www.access-programmers.co.uk/ubb/biggrin.gifate
fldType:Text(Revenue or Expense)
fldEstimate:$

I would like to subtract estimated revenues and expenses for one period. However, I would need to use two queries to do so.

1.)SELECT tblMonyhly.fldType, Sum(tblMonyhly.fldEstimate)
FROM tblMonyhly
GROUP BY tblMonyhly.fldDate, tblMonyhly.fldType
HAVING (((tblMonyhly.fldType)="revenue"))
ORDER BY tblMonyhly.fldDate;

2.)SELECT tblMonyhly.fldType, Sum(tblMonyhly.fldEstimate)
FROM tblMonyhly
GROUP BY tblMonyhly.fldDate, tblMonyhly.fldType
HAVING (((tblMonyhly.fldType)="expense"))
ORDER BY tblMonyhly.fldDate;

Is there a way to subtract these? I'm stumped.

Thanks.

[This message has been edited by casey (edited 03-08-2002).]

[This message has been edited by casey (edited 03-08-2002).]

[This message has been edited by casey (edited 03-08-2002).]

[This message has been edited by casey (edited 03-08-2002).]

[This message has been edited by casey (edited 03-09-2002).]

You can do this with a single query if you don't need separate numbers for revenue and expense:

SELECT Sum(IIf(M.fldType = "revenue",M.fldEstimate,M.fldEstimate * -1)) As NetAmt
FROM tblMonyhly As M;

If you want to see details, use a report.

If you want to get Expenses and Revenue in separate columns use:

SELECT Sum(IIf(M.fldType = "revenue",M.fldEstimate,0)) As RevAmt, Sum(IIf(M.fldType = "expense",M.fldEstimate,0)) As ExpAmt, Sum(IIf(M.fldType = "revenue",M.fldEstimate,0)) - Sum(IIf(M.fldType = "expense",M.fldEstimate,0)) As NetAmt
FROM tblMonhly As M;

Thanks for the response. That helps.

One question though...Is there a way to group the results by fldDate?

Thanks for the help, Pat.

I just added [GROUP BY M.fldDate] to the query you gave me. It's exactly what I need.

SELECT Sum(IIf(M.fldType="revenue",M.fldEstimate,0))-Sum(IIf(M.fldType="expense",M.fldEstimate,0)) AS NetAmt, M.fldDate
FROM tblMonthly AS M
GROUP BY M.fldDate;


[This message has been edited by casey (edited 03-09-2002).]