Make the ending balance the next days beginning balance in MS Acce

[Guest]

On a form and report I am trying to make the one days ending balance appear
as the following days beginning balance (e.g)

Day Beginning Balance Income Expenses Ending Balance
Mon 10 15 5
20
Tue 20 10 15
15
Wed 15 5 15
5
.....

I am using a crosstab query to calculate my income and expense column
numbers and I can do the math on the form to get the ending balance, but I am
not able to make my ending balance the next days beginning balance.

Thanks for your advice

 

[Jeff Boyce]

Jim

Where are you getting the numbers from (i.e., what data/table structure are
you using)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

The numbers are coming from a table. This table is the source for the
crosstab query. The crosstab query has the date as the row heading, name
(Income/Expense) as the column heading, and the amount as a value.

 

[Jeff Boyce]

Sorry, I should have been more explicit.

What data structure (fields, data types) are you using as your source?
Since queries, including crosstabs are based on the actual structure, it's
tough to suggest approaches without knowing that (explicit) structure.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Date - Number (YYYYMMDD)
Income/Expenses - Text
Amount - Number

 

[Jeff Boyce]

Jim

Access has a Date/Time data type -- and a host of date- and time-related
functions that can manipulate date arithmetic. You might be able to get
more out of Access if your "Date" field were an Access date/time data type.
By the way, if your field is actually named "Date", you (and Access) will be
confused. Access uses that as a reserved word, and may get confused when
you use it to mean something else.

If I'm reading between the lines correctly, you have two records for each
date. One record with "Income" and one with "Expense". Or do you have
zero, one or many records for each date?

If you build a report and use running sum, you can display the current
"balance" in a control.

If the beginning balance tomorrow is the same as the ending balance today,
do you need to show the same number twice? Another approach, as mentioned
in the previous paragraph, would be to have the report calculate a value for
the running sum (basically the sum of income less the sum of expense).

Regards

Jeff Boyce
Microsoft Office/Access MVP