minus date wise sales a=n?

[alex_raju]

sir,



i have two table named "sales" and "salesrtn"

in sales table fields are [saldate],[code],[model],[inv_no]autonumber,[price],[salqty].[salamount]
in salesrtn table fields are [rtndate],[code].[model].[rtninv_no]autonumber'[rtnqty],[rtnamount]

how i can design a query that datewise sales amount minus from sales return ( [salamount]-rtnamount] date wise? (how to manage sales date and sales return date in a query)

[apr pillai]

Place the Sales and Salesrtn Tables LEFT JOINED on Code field in a new Query.
Place all the fields from Sales Table and place rtndate, rtnqty, and rtnamount from the Salesrtn table. The selected fields' list is given below:

saldate, rtndate,[code],[model],[inv_no],[autonumber],[price],[salqty],[salamount], rtnqty, rtnamount, sale_qty:[salqty]-nz([rtnqty],0), sale_amount:[salamount]-Nz([rtnamount])

All Sales recordS may not have matching sales return records. Once you LEFT JOIN the sales return table only the matching records will have values in the rtnqty and rtnamount columns. To fill such fields with zero values we must use the Nz() function to evaluate and change the field value to 0 so that the the calculation will not end up to Null values.