making sales return(amount minus) on particular sales date...

[alex_raju]

dear sir,



first of all i would like to say many many thanks to accessforum (mr.pillai & all)coz, i have gain many useful tips from accessforums.net.

-----------------------------

how to compare and minus sales return from sales date wise ( like, on 23-06-2011 sales is 500$ at the same date 20$ was sales return )


date sales salesreturn netsales

1-1-2011 500$ 20$ 480$
2-1-2011 1000$ 0$ 1000$
3-1-2011 800$ 50$ 750$


like

[June7]

Looks like this would be a simple expression in query to subtract one field from another.

[khalid]

.... and the netsales would be a calculated field subtracting sales from salesreturn.

SELECT date, sales, salesreturn, sum([sales]-[salesreturn]) as netsales From [YourTableName]

[June7]

Sum is an aggregate function. Using it requires a Totals query with GROUP BY clause. Exclude the Sum and simply: [sales]-[salesreturn] As NetSale. Then in a report use Grouping & Sorting with aggregate calcs to further manipulate the data.

[alex_raju]

.... and the netsales would be a calculated field subtracting sales from salesreturn.

SELECT date, sales, salesreturn, sum([sales]-[salesreturn]) as netsales From [YourTableName]

there are four table :

main_sales -pk
sales_details-fk

main_return -pk
return_details_fk

when i make like you say, non return days also happening duplicate

[June7]

Definitely complicates the situation and not at all like the data structure you presented in first post. Need to manipulate the data to get to that structure. Will have to do a Totals query for the sales and a Totals query for the returns and then join these on the value used for grouping, which from your sample looks like date.