HCS Consulting Group.
We make Complex Systems simple

Batch processing in ms-access.

By Albert D. Kallal
Friday, May 25, 2006

Data Processing in ms-access?

What ever happened to the punched card?.

 IBM announced on December 1, 1964, that round corner cards would be a regular feature on its general purpose punched cards. Round corner cards, announced a year earlier by IBM, accounted for more than two-thirds of the company's punched card volume (measured in the tens of billions a year) by 1965. Virtually all punched cards had been manufactured with square corners since they were first used in the 1890 census.


IBM announced on December 1, 1964, that round corner cards would be a regular feature on its general purpose punched cards. Round corner cards, announced a year earlier by IBM, accounted for more than two-thirds of the company's punched card volume (measured in the tens of billions a year) by 1965. Virtually all punched cards had been manufactured with square corners since they were first used in the 1890 census.







Ok, why bring up punched cards that hark back the electro mechanical computing age? What great lessons about data processing can we learn from that golden age of computing? A lost concept that punched cards teach us is reducing human interaction with the machine will reduce the amount of human labor and time to complete a task.

In all fairness and kindness to punched cards, they were still widely used well into the late 1970's. So, a 100 year run for that technology is not bad in today's short lived, disposable razor era.

To be honest, I never actually worked at a real job that used punched cards. The punch card machines for students at my University were hauled away during my first year of computing science. That time period was the 1980's, and the pc was all the rage. Before that keypunch machine was hauled away I did take time to play with it. I had a book that from a data processing course from a local college and it had chapters on punched cards! I even learned how to make a "control" card for easy key punch data entry. And, I ran some batch jobs on the mainframe.

Today with rich application interfaces we tend to give users TOO MANY choices. This is much like the difference between a car on a road, and a train on rails! Most of us prefer a car, but when you haul huge amounts of goods, then obviously the rail car is the best choice. The same concept of limiting choices often applies to software. When you *reduce* the choices for your users, and setup a streamlined process you often achieve INCREDIBLE levels of productivity.  In fact, punched cards systems are often MORE efficient then modern computers today!

For paper punched cards to endure such a long run in history there MUST be something inherent in punch cards that made them so valuable. Remember, computing is centered around saving labor costs (at least, I thought that is why clients pay me money for this stuff). So, why was it SO DIFFICULT go get rid of punched cars? What is it about punched cards that warrant so much respect?

What can we learn from those punched cards? There HAS to be something more then "it was just the way it was".

In a sad way I missed the punch card era, and never did work at a company that used punched cards. I would love to actually experience working with punched cards. To be fair, I suppose my romantic feelings for this era are just a false emotion that longs for the simple past, much like those late night commercials on TV that flog music from the 50's "malt shop era".

We don't have punched cards or malt shops anymore! As a young child, I do remember the major department stores in my city (the Bay) did actually have a malt shop (or what is called a soda fountain shop?). I have often wondered why malt shops have not made a comeback? Well, ok, in place of malt shops, you certainly do see yogurt and fruit smoothie shops springing up in the malls. I suspect the smoothie shops is a result of our health conscious society.

Famous Peters Drive - IN Calgary, Alberta, Canada - Photo by Albert D. Kallal (2002)


Time does march on, but good concepts never die. Often, those concepts re-invent themselves over time, be it malt shops to smoothie shops, or punched cards to magnetic strip cards.

My first memories of punched cards centers around seeing my parents open up bills on the kitchen table as a young child. I remember department store bills came with a return envelope, and a punched card. You would simply write out the balance due on a check, and then enclose the punched card and check in the provided return envelope.

Let’s contrast how an ms-access application today compares to the punched card example. In our example, we are obviously sending out bills to customers, and processing them as they return. Quite a typical business process, and one that is much the same today, as it was 30 years ago.

So, we send out the bills, and customers send in payments.

Let’s look at the punched card example.

The mail room people will open and separate out the incoming mail. Any envelope that contains a bill payment will be separated into a pile work that will be delivered to the keypunch operators in the billing department. The mail room will organize a stack of punched cards and stack of checks in order that MATCHES. This “stack” of work will then be assigned to a key punch operator. This key punch card operator would load up the stack cards into the card punch machine, and then sit down to process the stack of checks. 


With the punch card system, the card loads, and moves forward to the correct place in the card for enter of the check number. Did I forget to mention that we did create a programmable punch card known as a "control" card that is wrapped around that little rubber drum thingy in the punch card machine? This card would be programmed for the GIVEN TASK to advance the punch card to the check number position. (the control card would also enter today's date). While I never did use punch cards at work, I did play with, and learn HOW to use a punch machine. In fact, I did this at my University BEFORE I was a student there!!! – The punch card machines were open the public anyway (not like they had some type of log on id!!) Hence, I had a chance to learn and play with punched card machines before the University hauled them away (by the way, that was JUST few months after I had finished playing with them!!!).

With the card positioned at check number field, the check number is entered. The punched card then travels to the bill amount field, and the operator punches in the check amount such as 6257 (that means 62 dollars and 75 cents).  Now, the enter key (actually, it was the feed key, or RELease key) is hit, and the card then exits, and the next card loads, and the "control" card again moves the card to the CORRECT field position. I don't think much imagination is needed here to realize how incredible fast and efficient this data entry process is.

The person then turns over the check, and starts data entry on the next check. The user hardly takes hands off of the keyboard, and the ONLY reason to move hands is to turn over the check to start working on the next bill payment.

To say that the above process is blindly quick is an understatement.

Now, let’s process the same data using a new advanced electronic computers and ms-access.

The mail room in our modern era (to keep this comparison even) will once again separate out all of our billing payments into a pile. We will receive two piles just as before:

  • the customer names (we provide a tear off ‘stub’, or simply a "return" this portion of the bill feature)
  • the pile of checks.

Ok, so, just like the key punch operator, we sit down to process these bill payments.

We first have to place our cursor in our customer number “search box”. Our hands move from the keyboard, to the mouse, and we click on the customer number search box. Our hands then move back to the keyboard, and we type in the customer number, and hit tab. The customer is displayed on the screen. (In the mean time, our old fashioned key punch operator has likely process about two more checks in the same time period). 

Now that our ms-access operator has found the correct customer, you then need to place your cursor into the sub-form, and start a new payment details record. So, you move your cursor into the check number field (mean while, the keypunch operator has likely processed another two customers!!). Ok, in the check number field, we enter the check number, and tab into the payment amount field.

You then enter in the amount for the payment, and then either close the form, or use your mouse and move the cursor back into the customer number field, and are now ready to process the next field.

Looking at the above, it would seem that the old punch card operator will do about 3-5 customers as compared to your 1. So, you can hire 5 people to process this using ms-access, or have one person do the work using an old fashioned punch card machine. Hum, this is a bit sobering.

 As you can see, the punch card example is rather impressive in terms of data processing. I don't have space, or time in this article to explain what is done with those completed punched cards, but suffice to say, that the steps to generate the list of customers to paid, or perhaps generate the list of customers who still owe money is very efficient.

The reasons for such incredible streamlined processing with those punched cards centers around a task orientated system. My original point about how removing choices from the user increases productivity is now more clear in your mind.

With the punch card system the operator is relieved of

  • Having to search, find and bring up the customer name on the screen (the customer number is already on the punched card)
  • Having to TYPE or enter a customer number is eliminated (this is likely the #1 reason for such efficient data entry)
  • After having found the customer (and we are ignore HOW EASY it is to bring up the wrong customer), we then have to navigate to the CORRECT SPOT on the forms (likely a sub-form), again, the punched card eliminates all of this.
  • The check number is entered, then the amount is entered, and THEN THE operator has to MOVE THE cursor BACK TO the search field. (And, let’s hope you did not setup a key that you have to press to "save" your work. (And people wonder why I am so harsh against a save button on an access form).

Again, it doesn’t take much thought to realize that the punch card system RUNS ABSOLUTE CIRCLES around the ms-access application in terms of sheer speed and ease of use during data entry time.

The operator does not have to search, lookup, or even verify the customer number. The operator will likely glance at the punch card to verify that the check name matches the name they see on the punched card. This "observation" by the keypunch operator to match name on the check does not slow down the data entry process. Further, the keypunch operation does not have to tab, or move the cursor around on a screen to add this payment detail information. Further, the operator does NOT have to even enter a decimal point. All of these things add up to a VERY rapid data entry process.

My bets are that the punched card system is up to 8, or even 10 times faster then the ms-access system.

Really, as we stand rig now, ONE punch card operator can replace a room full of 8, or 10 people in cubicles using ms-access.

I think at this point, you realize that my nostalgic stories about punched cards are not so silly anymore.

Just what happened here? How can we refine the ms-access example?

What we need to do here is eliminate the punched cards, but apply the lessons learned here to our ms-access example.

We want to achieve SIMILAR RATES of data entry for the operator.

If a new computer system can't match a 30 year old punch card machine, then I think we lost some real lessons in history along the way.

To be fair, the ms-access screen could be optimized that you JUST enter customer number, then check number, and then amount. The application would then save the data, and the cursor would then return to the customer # field. This would go a LONG way towards speeding up the data process. However, we STILL have to read, look and find the customer number. We then still have to enter the customer number. So, ms-access is still more work, and still subject to *more* data entry errors (and, if you classify a miss-typed account # as an error, you will then send a bill to the WRONG client! This is a SERIOUS mistake that loose your customers. (so, it needs pointing out that entering a phone number wrong is a simply data entry error, but entering a wrong customer number and thus sending a bill to that customer is the same type of data entry mistake, but the results are far more serious).

The first issue we have to solve is eliminating that operator time to search, bring up, and FIND the customer. The solution for this is rather simple, and OFTEN over looked. What you do is simply purchase an OCR wand reader. These readers are cheap, and easy to use/setup. They are often what we call a keyboard wedge. (They simply type characters as if you typed them in your self). You can also consider using a barcode reader, but OCR wands are usually *much* more useful for bill payments type stuff. With a wand, you can read in the bill amount you also printed on the return portion of the invoice.

With a simple OCR wand, we have now much re-created, and re-produced the *efficiently* of the punch card system.

Like the punch card system, when you send out a bill, you will have an ENCLOSED return portion of the statement. I also assume you include a return envelope also.  You *can* consider placing the customer number on the OUTSIDE of the envelope and thus eliminate a customer forgetting to include the return statement along with the check.

Hence, our mail room now does the same thing they did with the punched card system. They will prepare those piles of work for each operator. Of course, we included the bill amount and account# on the return statement in OCR character format. So, two quick wand scans...and you move on to the next bill to process....it is fast...and in fact LESS typing then the punched cards.

Note that if you actually have to enter cheques numbers, then you can scan that also with the wand reader. In fact, if you have a lot of processing, then consider purchasing a cheque scanner, as it will also enter the customer bank branch number, and cheque number for you. The only think manual process you have left is to enter the check amount.

And, if you are REALLY smart, you separate the pile of work into two piles. One pile is where the cheque amount is THE SAME AS THE BILL amount. That way, you can just scan the account number, and ASSUME the bill amount is the same as the check amount that you SENT out. Now we talking! We are surpassing those old silly punched cards real easy now! We likely can process one customer bill in about 2 seconds of time. Yes Mr. Computer guy, we can save you the labor of 10 people here!!!

So, this simply means we replace the punched card station with a computer and a hand held wand reader. Using a simple wand reader like this can cut down you billing processing department labor by a factor of 10 times easy.  (you can lay off 9 people!!). I was involved in setting up these systems for a number of companies. I don't think we need to much run the numbers as to how much you save by eliminating the labor of one employee in a month, let alone 10.

So, the lesson here is that punched cards enabled you to create a processing "loop" in which material RETURNED FROM the customer. So, this concept of using a piece of information RETURNED FROM the customer is the key concept. In fact, any type of customer survey, or especially political donations processing is a real great caudate for this type of process. For example, you make a card with 3 or 4 check boxes as to the donation amounts (nice easy round numbers like 5, 15, 20, 25). They send the card back (that has their customer id). When you use the OCR, you scan their customer number, and then scan a TEMPLATE SHEET with the donation amount (e.g.: 25). Note that the customer simply crossed out a particular check box with their pen, but you use your OCR wand to scan from a template card you affixed to you desk. So, an OCR wand can even be used for data entry when the amount, or answers to questions are marked via a pen.

I hope this little blast from the past sparks a genuine interest data processing. Some incredible concepts from 30 years ago can still cut down the labor in your bill processing department by many factors. Of course, ultimately, more of this stuff will be electronic, and web based

So, now that your 3 staff has processed a HUGE number of bills today, you now need to send out to the customers a statement showing their balance is zero. Further, this printout job will be a nightly batch job, one you run at 7pm...And it likely takes a good deal of time as your printers’ printout the new statements and envelopes that you send to the customer. So, now, we are talking about a nightly batch job in access.

That brings us to the following article:

how to run a batch job in ms-access

Batch Jobs in Ms-access