May be a strange way to do this, but...

[DaltonH]

it's been a while since i have done anything with databases, and i am play around with this as a relpacement to a spreadsheet my boss keeps messing up.



basically i create a daily financial report called a greensheet every day. this report has daily, month to date, and previous year: month to date categories...


however two very important fields are [Previous Outstanding] and [Total Outstanding To Date], this is the most important field on the report, and is the one that if i have a day off, my boss attempts to do and i have to fix.... pretty much every time.

here is the logic behind the Previous Outstanding
If today is the 1st day of the month, this will be the same yesterday's Total Outstanding to Date.
If today is not the 1st day of the month, this will be the same as yesterdays Previous Outstanding (this number only changes once a month, but it is on every greensheet)
If Today is the very 1st record, the todays value is 0

is there any way for me to define the field so that that logic is automatic?

once i get this figured out, i will probably be bugging you guys again. btw, i am using access 2010 on microsoft xp. thanks folks!

[June7]

Essentially, No. Calculation that depends on value in another record of same table is not easy. Can require subquery or domain aggregate function. Saving the calc result will require code. However, advise against saving.

If you want to provide db for analysis, follow instructions at bottom of my post.

[ssanfu]

I agree with June. I would be very hard to do.
The problem in Access is that if the calculated value is stored, and someone edits a value from last month, the calculated value is incorrect - it won't be automatically updated - unless you have a ton of VBA code to ensure the calculated field is recalculated.

You haven't said anything about the data or the table structure.

How do you calculate "Previous Outstanding"?
How do you calculate "Total Outstanding to Date"?

Are the reports once a month and done or do you go back and pull reports for 2 or 3 months (or a year) ago?
Maybe (again maybe) with an unbound form, unbound controls and a lot of VBA/SQL the report could be created.

[cphelps]

I understand the statements about calculated fields and agree. However if this was something that I needed I would probably think about trying to have a macro that used a MakeTable query to capture the required data temporarily and then to move that record to the live table.

I am not sure if this would work but if it did the macro could be assigned a button on a form and run by the boss without giving him the ability to get it wrong.

[June7]

Any code that would accomplish a MAKE TABLE action could just as easily be an INSERT or UPDATE action to existing table.