sql subtract value...

[burnside]

Hi,
i want to subtract `spend` from `target` as `diff`

[target]-[spend] AS diff

but i get error on above line
have the following statement;

strsql = "SELECT tbl_item.itemid, tbl_item.item, budgmonid, budgyearid, SUM(budgexp) as spend, SUM(budgtar) as target, tbl_loc.locname, tbl_fran.fran FROM tbl_budg INNER JOIN tbl_item ON tbl_budg.itemid = tbl_item.itemid, [target]-[spend] AS diff INNER JOIN tbl_fran ON tbl_item.franid = tbl_fran.franid INNER JOIN tbl_loc ON tbl_item.siteid = tbl_loc.locid WHERE budgyearid = 2 GROUP BY tbl_item.itemid, item, budgmonid, budgyearid ORDER BY itemid, budgyearid, budgmonid

 

[PHV]

strsql = "SELECT tbl_item.itemid, tbl_item.item, budgmonid, budgyearid, Sum(budgexp) AS spend, Sum(budgtar) AS target, Sum(budgtar)-SUM(budgexp) AS diff, tbl_loc.locname, tbl_fran.fran FROM ((tbl_budg INNER JOIN tbl_item ON tbl_budg.itemid = tbl_item.itemid) INNER JOIN tbl_fran ON tbl_item.franid = tbl_fran.franid) INNER JOIN tbl_loc ON tbl_item.siteid = tbl_loc.locid WHERE budgyearid = 2 GROUP BY tbl_item.itemid, tbl_item.item, budgmonid, budgyearid,tbl_loc.locname, tbl_fran.fran ORDER BY itemid, budgyearid, budgmonid"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BobStubbs]

Your problem may be that [target] and [spend] are themselves calculated fields. Try using:

SUM(budgtar) - SUM(budgexp) AS diff

... so that 'diff' is calculated directly from the 'budgexp' and 'budgetar' columns.

I hope that this helps.


Bob Stubbs

 

[burnside]

thanks v much both of you