How do I run a Balance in Microsoft Access

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

Guest

I want to run an inventory where I have a running balance I want to have to
columns one for Issues and the other for Recieved. I also want it to be bale
to run with null values
Ex.
Issues Recieved Balance
|100 | 100
20 | | 80
| 100 | 180
| | 180
 
Hello Brilandboy20.

Brilandboy20 said:
I want to run an inventory where I have a running balance
I want to have to columns one for Issues and the other for Recieved.
I also want it to be bale to run with null values.
Ex.
Issues Recieved Balance
|100 | 100
20 | | 80
| 100 | 180
| | 180
Click to expand...

You can create a report that lists the two columns and add a
third column with controlsource
=Nz([Received],0)-Nz([Issues],0)
Change the "Running Sum" property to "Over all".
 
In case you are a commercial developer developing the application for a
client, the correct spelling is "Received", not "Recieved".

The application deosn't look profssional if you have errors like above.
 
Yes thanks for your help with this problem. While this works for a report I
was trying to put the results in a query. If you can help me with that i will
be very grateful.

Wolfgang Kais said:
Hello Brilandboy20.

Brilandboy20 said:
I want to run an inventory where I have a running balance
I want to have to columns one for Issues and the other for Recieved.
I also want it to be bale to run with null values.
Ex.
Issues Recieved Balance
|100 | 100
20 | | 80
| 100 | 180
| | 180
Click to expand...

You can create a report that lists the two columns and add a
third column with controlsource
=Nz([Received],0)-Nz([Issues],0)
Change the "Running Sum" property to "Over all".
Click to expand...
 
Hello Brilandboy20.

Brilandboy20 said:
Wolfgang said:
Brilandboy20 said:
I want to run an inventory where I have a running balance
I want to have to columns one for Issues and the other for Recieved.
I also want it to be bale to run with null values.
Ex.
Issues Recieved Balance
|100 | 100
20 | | 80
| 100 | 180
| | 180
Click to expand...
You can create a report that lists the two columns and add a
third column with controlsource
=Nz([Received],0)-Nz([Issues],0)
Change the "Running Sum" property to "Over all".
Click to expand...
Click to expand...
Yes thanks for your help with this problem. While this works for a
report I was trying to put the results in a query. If you can help me
with that i will be very grateful.
Click to expand...

If you table has a primary key, say an InvID colunm, try the following:

SELECT i1.InvID, i1.Issues, i1.Received,
Sum(Nz(i2.Received,0)-Nz(i2.Issues,0)) AS Balance
FROM Inventory AS i1, Inventory AS i2
WHERE i2.InvID<=i1.InvID
GROUP BY i1.InvID, i1.Issues, i1.Received;
 
Back
Top