Balance?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Im just starting with access.
I want to make a database of an Excel sheet that has "In","Out" and
"Remaining" fields, my problem starts when I want to make the calculation for
the remaining field who is the result of the (In-Out) of any day plus the
(remaining) of the day before wich also has to have the same calculation, and
so on.

Day In Out Remaining
1 10 5 5+0=5
2 10 4 5+6=11
3 10 8 11+2=13

How I do that on a Table...? simple using Excel but I cant find the way to
do it using Access, help!

Regards
Totti
 
This is most easily accomplished with a report. First, the basic things you
need in your base table are the TransactionDate, QuantityIn, and
QuantityOut. Create a query from the table as follows:

Select TransactionDate, Sum(QuantityIn) AS QtyIn , Sum(QuantityOut) AS
QtyOut,
Sum(QuantityIn) - Sum(QuantityOut) AS DailyBalance
From YourTable
Group By TransactionDate;

Then create a report that uses this query as it's Record Source. Place the 4
fields in your report's Detail section. Cut the labels and paste them in the
Page Header Section. Right click on the DailyBalance field and select
Properties. On the Property sheet, in the Data tab, set the Running Sum
property for that field to Over All.
 
Thanks!!

Lynn Trapp said:
This is most easily accomplished with a report. First, the basic things you
need in your base table are the TransactionDate, QuantityIn, and
QuantityOut. Create a query from the table as follows:

Select TransactionDate, Sum(QuantityIn) AS QtyIn , Sum(QuantityOut) AS
QtyOut,
Sum(QuantityIn) - Sum(QuantityOut) AS DailyBalance
From YourTable
Group By TransactionDate;

Then create a report that uses this query as it's Record Source. Place the 4
fields in your report's Detail section. Cut the labels and paste them in the
Page Header Section. Right click on the DailyBalance field and select
Properties. On the Property sheet, in the Data tab, set the Running Sum
property for that field to Over All.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Click to expand...
 
Back
Top