Skip to content

Annual MS Access data updating…..

July 10, 2015

I have this old Access application that I support.  My customers have been using it for many years.  Yet, they seem to not use it the way it was designed.  Sound familiar?  She contacted me this week and said “some of the bookings that I did for this year are showing the old cost.”  What she did not say is “I must be doing something out of sequence or I think I did not select this option when I created the bookings“.  Its always the system that is the fail point.  Sound familiar?

The reality is the problem almost always is not in the application, but rather in the person executing the application.  To say another way, the problem is usually between the chair and the keyboard.

This is not a judgement on my customer.  Trust me, I have done the same thing in other scenarios.  I just wish she would take a little ownership of the mistakes or process misunderstandings that she has.  Also, to be forthcoming with what I am working on.  To my point for next year.

What to do when the kit booking cost need updating in year 16-17.  Take these steps

  • Open the kits table – ensure the cost for the kits is up to date
  • open the bookings table to verify that bookings for the school year are in there – should be schoolyearid 19
  • create a new query and use an update statement to update the incorrect booking costs
  • Pull up the the kits table, hiding the columns not needed – showing just the id and the cost
  • Next to the kits table – position the query window – so its easy to see both and scroll down the kits table
  • This past year, I also printed out the appropriate kit booking records – so i could see which kits i actually needed to run the update on.

estec booking updates

A simple little SQL statement to update the records

UPDATE tablename

SET fieldname = xx

WHERE kitid = xx AND schoolyearid = xx

*please don’t forget the WHERE clause when doing updates like this…..;)

Advertisements

From → MS Access

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: