Does Access have this functionality?

[darsunt]

I want to have a program where the balance of an account is always updated and stored in a column. In oracle I would use pl/sql, does Access have a similar capability?

Also I want to be able to add and subtract date types, for example, to get entries from the last month. Oracle date type can do this, does the Access date/time datatype do this?

 

[statsman]

Question 1
You can add a column to the table that will store the last update. You can add it manually, or set the default value of the field on your form to =Now() or =Date().

Question 2
Do a search on DateDiff() on the forum

 

[neileg]

I want to have a program where the balance of an account is always updated and stored in a column. In oracle I would use pl/sql, does Access have a similar capability?

Is is bad design to store a balance. Any change in the underlying data will make the stored value incorrect, plus it's a waste of space. You should calculate the balance in a query (for example) any time you need it.

 

[darsunt]

Thanks, DateDiff looks very promising. It will be for showing entries in the last month, last week, etc.

Also thanks Neileg. I agree, I've been thinking it over, and I'll do it by query. I already calculate the balance every time I do an entry, so I'll just store that value in the balance column.

 

[neileg]

...so I'll just store that value in the balance column.

So why are you storing it? Just calculate it.

 

[GaryPanic]

I know this has been answer and i agree with everyones comments bad practice etc

however this is something that i will need at some point to jazz up my development

what it is is on an account I need a running total (Yes stay with me on this)

reason for this is once I reach a certain amount - I need a warning to flash up
so lets say I have a warning limit in a table of say £100,000 and my acount is 95k all fine and dandy - however I need a warning once i am at £100,001

I know I could run reports to check - but hell thats a nightmare solution to a problem a running total writen back to my table where the warning liit is makes kinda sence as then it will go if x> than y then warning flash or whatever I decide to do - now I run monthly reports that could show this but what happens mid month - I am in serious sh*t if i break the limit and I have not informed someone - a running total is the only way I can think of doing this

can anyone one else come up with an alternative ??????

g

(I have not got to this stage yet its on the back burner) but as its been raised

 

[darsunt]

Neil, the purpose of keeping a column for balance total is to know what the total on the account was at the time of that entry. Right now if I need to know what the balance of my account was at, say January 3, that information is lost.

Gary, I'm not sure I understand what you are asking for.

 

[pbaldy]

Darsunt, I would still not store the balance. The balance for any given date is the sum of the transactions up to that date. That of course presumes you have some sort of transactions table from which that can be calculated.

GaryPanic, along similar lines I would not store the running total. I have an application that includes charges and payments made by customers. I have a process that runs nightly that calculates the current balance for each customer and compares it to a field in the customer table with their maximum allowed balance. For any customers that have exceeded their maximum, an email is sent to appropriate staff for followup.

 

[neileg]

I'm with pbaldy on this one. A stored running total is a definite no-no.