Need Help

[Omer Aslam]

I want the query or any other formula for this result..
Net_Total (in 1st row) = Price (of 1st row) - Discount (of first row) and
Net_Total (in 2nd row) = Net_Total (of 1st row) + Price (of 2nd row) - Discount(of 2nd row) and
Net_Total (in 3rd row) = Net_Total (of 2nd row) + Price (of 3rd row) - Discount (of 3rd row)....and so on

see the picture

 

[Omer Aslam]

Any one here???

 

[Beetle]

You can see an example here of creating a running sum in a query. The example uses a date field to determine which row is previous to the current record for calculating the sum, so it may need some modification for your circumstances. In your case you may need to create one query to calculate the Net Total for each row, since that is the value you want to sum, then another query, based on the first query, that handles the running sum.

If you need more help, post a copy of your db with just dummy data if you can so we can see exactly what er're working with.

 

[Omer Aslam]

I attached my file....i want Net_Total field as i told in my previous post...

 

[Beetle]

Won't be able to look at that until Monday as I only have A2003 where I am now. Maybe someone else will jump in in the interim.

 

[sparks80]

I have Access 2007 so can help.

As Beetle suggests you can create a running total using the DSum function. By summing the prices and discounts separately you can do this in a single query.

Create a new query with the following SQL:

SELECT Table1.[No], Table1.Price, Table1.Discount, DSum("Price","Table1","[No]<=" & [No])-DSum("Discount","Table1","[No]<=" & [No]) AS RunningTotal
FROM Table1;

 

[Omer Aslam]

I have Access 2007 so can help.

As Beetle suggests you can create a running total using the DSum function. By summing the prices and discounts separately you can do this in a single query.

Create a new query with the following SQL:

SELECT Table1.[No], Table1.Price, Table1.Discount, DSum("Price","Table1","[No]<=" & [No])-DSum("Discount","Table1","[No]<=" & [No]) AS RunningTotal
FROM Table1;

Thankyou all of you.Problem Solved