Please Help!! Automatically updating a field in a table. (1 Viewer)

[dannytuk_1982]

Hey,

I need to update a field in a database table based on another field in that table. In fact I need to do it on a number of fields but one would be a start!!

I managed to do it in a form using control source, however this will only display the data in the form and doesn't bind it to the table itself.

I have a field called "Proposed_Completion_Date" and a field called "Days_Remaining". I need the "Days_remaining" to display the number of days left until the job is overdue.

I want to do this using the DateDiff expression. i.e.

=DateDiff("d",Date(),[Proposed_Completion_Date])

How do I do this either in the table/form so that it binds to the table, also shows in the form and I can run queries on the table data showing overdue jobs?

 

[namliam]

Thats the whole point...

You shouldnt store calculated information in your table, because it is more trouble than it is worth (among other things).

Just use the formula to calculate it on the fly as needed.

 

[JANR]

Thats the whole point...

You shouldnt store calculated information in your table, because it is more trouble than it is worth (among other things).

Just use the formula to calculate it on the fly as needed.

Good advice!!! But if you must then:

UPDATE yourTable SET yourTable.Days_Remaining = DateDiff("d",Date(),[Proposed_Completion_Date]);

But remember your table will now always be in a "suspected state" because tomorrow all the calculation WILL be wrong..... You have to run this update every single day NO exeptions.

JR

 

[dannytuk_1982]

Thanks for that,

Ideally I would be doing something like this in .Net, however I've been asked if its possible in Access. However, I've hardly used Access and have been pleasantly surprised with its functionality!!!

If I Implement the update query is there a way it can run every time a form is opened rather than pressing an update button?

I also need to do a similiar thing using a module I've created. The code converts minutes into hours and minutes and again I need to bind this to the table if possible. The code is as follows:

Public Function TimeConversion(dblValue As Double) As String

Dim IntHours As Integer
Dim IntMinutes As Integer

IntHours = Int((dblValue / 60))
IntMinutes = dblValue - (IntHours * 60)
TimeConversion = Format(CStr(IntHours), "00") & ":" & Format(CStr(IntMinutes), "00")


End Function

As I said, I originally put this into the control source and it worked in the form but didn't update the table, so I'm assuming I'd need another update query for this?

 

[JANR]

If I Implement the update query is there a way it can run every time a form is opened rather than pressing an update button?

Yes either will work.

so I'm assuming I'd need another update query for this?

Yes.

UPDATE yourTable SET yourTable.yourUpdateField = TimeConversion([YourCoversionField]);

should do it.

JR

 

[dannytuk_1982]

How? Which Control will do this?

 

[namliam]

Yes either will work.

NO !

Yes.

NO !

Sorry but... NO NO NO <yells at the highest shapest loudest possible sound> NOOOOOOOOOOOOOOOO! </yell>

Do NOT store calcluated values.
Do NOT store calcluated values.
Do NOT store calcluated values.
Do NOT store calcluated values. If I must repeat it 10 biljoen times I will...
Consider the update query, now running over 10 records in half a second. In a week 20 records a month 100, a year 10000 etc... It is not funny ...

This rule is there for a reason. WHY do you want to update a table everytime a form is opened? Simply use a query and calculate the value on the fly in the query at NO expense of performance ! ! ! ! Instead of an update query which will get slower s l o w e r and s l o o o o o o o w w w w w e r....

Adhere to the rules of database development!! They are there for reasons you probably cannot even start to understand right now. They do really do something and should be followed with very very few exceptions!

Do NOT store calcluated values.

 

[JANR]

Sorry but... NO NO NO <yells at the highest shapest loudest possible sound> NOOOOOOOOOOOOOOOO! </yell>

The OP's question wasn't IF it should be done, but if it COULD be done. And I warned about the dangers!!!!

I agree that in this case it shoulden't be done.

JR

 

[dannytuk_1982]

Thanks namliam,

I understand that this could cause performance issues and this is far from ideal and often overlooked, in SQL Server stuff like this is done using stored procedures. Does Access cater for anything similiar?

 

[namliam]

Yes access caters to this OFFCOURSE !

You can make functions in Modules or even in this case just do it in a query (SQL Server lingo: View) even in SQL server I wouldnt use a SP, but simply a calculated field.

Just because you are reverting to what you (probably) consider to be a "lower form of database" that you abandon all good developing rules...

 

[Atomic Shrimp]

The key thing here - I think - is that you can create ordinary SELECT queries in Access, then treat them exactly as if they were tables (i.e. run another query against them, use them as a record source in forms, etc. The query will then be run on demand each time it is used.

 

[The_Doc_Man]

And the reason is that as CPUs have gotten faster by whole orders of magnitude over the years, disks have gotten faster by a few percent.

In 1988, we had a VAX 3900 computer that was really HOT stuff, 3 million instructions per second. Disks spun at 3600 rpm. So that meant that the index track passed the disk heads every 16.6666 milliseconds. Expectation of random distribution of reads would predict an average seek time of half that, or 8.33333 milliseconds. Not counting the purely mechanical seek time for head movement, you added 8 msec for every disk read. So in that era, a disk with an 8 msec rotational latency gave you enough time to execute 0.008333 x 3,000,000 instructions. Call it 25K instructions during that one turn of the disk. That was a lot, really.

OK, my wife now runs an HP Pavilion at home. 3.2 GHz for the internal clock, but because of using multiple clock cycles for many instructions, call it more like 1.5 Gips. That is 500 times the VAX 3900 speed. The disk spins at 7200 RPM. Twice as fast as 20 years ago. So your average read delay is 4.16666 msec. If you do the math again, that is 6.25 million instructions per disk read not counting head movement.

The UPDATE query you are talking about will hit the disk twice per update (one read, one write) or at best twice per disk buffer, which will probably not be more than a small number of records sharing the buffer. (And to maintain atomicity, consistency, and integrity, I wouldn't be surprised if it really WAS two latencies per updated record.)

Doing the computation entirely in a query will read the query itself once, then read each record once and compute the result for you. This is twice as fast. And with 6.25 million instructions available between each READ operation, it would be trivial for Access to provide the query results VERY quickly.

Bottom line: Never store what you can compute. You have the time for it.

 

[namliam]

Wow... educational post Docman Rep bumped

 

[The_Doc_Man]

Thank you, namliam!

I had to go through that sort of exercise a long time ago in a context about setting a system to do (or not do) paging and swapping, and to consider/compute the cost. For ANY system today, you want to minimize the amount of disk I/O you do, precisely because disk speeds haven't caught up with CPU speeds, and even a USB drive, because of the serialization, isn't that much faster.

Which is why on a Windows home or small business box, since you have no control over the swap/page ratios, program working set control, and other factors, your best action to make your system faster than a speeding bullet is to bolster its RAM. When the swapper runs, given its priority, you aren't doing anything except mucking memory management slots. Which is long-term not productive.

Thus speaks an old tuning guru. And the reason this works is:

Dave Cutler was one of the principle authors of OpenVMS (my primary system) but his next feather in his cap was Windows NT - on which all subsequent Windows Kernels have been based. Yes, I have looked a little bit under the hood. Dave's touch is definitely there, particularly in the security kernel.

 

[Pat Hartman]

Yes, computing power has come a long way since my early mainframe days and because of it, programmers have gotten sloppy. But there are still principles that you should adhere to even if it isn't causing you a proglem at the momemt and that is - disk reads are expensive and disk writes are even more expensive so if you don't absolutely need I/O, don't use it.

But, it is not computing power at issue here. It is the correctness of data. It is just plan wrong to store a value that could be incorrect the next time the record is read. There are reasons for storing calculated values but this is NOT one of them.

Danny, since you're new to Access you probably don't understand how to make use of queries. Someone mentioned earlier that Access treats queries and tables interchangeably. It is considered good practice to base all forms on queries rather than tables and this is just one reason. Add the calculation to your select query, use the select query as the RecordSource for the form and voila! Every time you open the form, the value will be correct

 

[namliam]

Dave's touch is definitely there, particularly in the security kernel.

I wouldnt be all that proud of that security though, with all the problems windows has had over the years with its security. And now even the new 7 seems already to show serious problems in that area

Then again, any security can be breached... Just a matter of delay rather than obstruct...

It is considered good practice to base all forms on queries rather than tables and this is just one reason.

Offcourse the main principles of not storing calculated values in the normalization rules should prevent anyone from doing so. However...

And "good practice" .... *erm* that would be the understatement of the week, maybe even month on this forum now already

 

[dannytuk_1982]

Wow Thanks Guys!!! I always remember Analysis Of Algorithms and Computer Architecture from my uni days as the most long-winded sleep inducing lectures ever!!!

However they did have a purpose and shouldn't be overlooked and I completely agree that the increases in processing power has psychologically negated the attitudes of best practice in modern programming.

So to get this straight I should....

A) Base my form on a select query.
B) Run my Update Queries on this query as opposed to the original table.

This is where I'm struggling to see the logic or where I don't understand how access works...

If I have a select query that I update then will this updated query stay the same the next time that I load Access or will it pick up the original data from the table the query is based on??

Presumably the next time I open the form the select query will pick up the data from the original table? Does that make sense??? Does it mean that I will have to save the updated query as the table so I can revert to that data next time I open the form?

Or does the query save itself as a sort of table that can be manipulated as soon as it is run?

How does this work?

 

[namliam]

B) Run my Update Queries on this query as opposed to the original table.

No no no... you dont run update queries PERIOD

The update query is a NO-NO!

The select query does the calculating automaticaly and on the fly for you.

Do this small excercise and find out...
make a table with two number columns name the columns A and B
Fill these two with some numbers for like 5 rows or something
Now make a query add columns A and B to the design grid.
Now in the third column put: Total: A+B

Run the query, ITS MAGIC

 

[dannytuk_1982]

Ok thanks namliam that makes it a lot clearer. I've had a look at this and created queries to produce the data I need.

What I need to be clear on in my head now is how this all links together.

To me so far I have a form that is linked to a query. When I insert new data or edit data in the form then this will insert data where it is bound to, the query. So far so good. The select query calculates the data I need as its created so I don't have to worry about using update queries with any calculated values. Now as for any new data, is this automatically inserted into the table for future use?

So essentially, assuming it is:

Form ----passes data to----> Query---passes data to---> Table

and then when I reopen the form, the select query runs against the table which it has data stored from the previous time the form was open.

Also are there any issues with primary keys and foreign keys with respect to using queries to create forms and subforms?

Apologies for all the questions but I'm one of those people who needs to know why and how you do something in order for it to make sense to me!!!

 

[namliam]

Form > Query > table > Query > Form

Yes... the only storage is the table... Data entered on the form is stored in the table and once the form opens it is fetched again from the same table.
The query is basicaly a set of instructions for the form on how to fetch (and calculate) the data from the table.

No issues on Keys/Foreign keys other than your normal normalization rules and stuff.

No problems on questions, I prefer telling the story than giving the straight answer... something about " If you give a man a fish... "