Adding Combo Boxes to Your Access Forms

Combo boxes are a great way to increase data entry speed and decrease data entry errors. Here is a standard form, mostly filled in.

Note that the Doctor field has been placed as a standard field. The data entry person can type the name in any format (last, first - first, last - upper or lower case, etc.). It then becomes almost impossible to take advantage of your database and sort your patients by doctor. Replacing the Doctor field with a combo box that draws its names from another table or from a hard-wired list solves this problem. Here is the finished form with a combo box:

Note that the data entry person needs only to click on an item on the list to achieve a standardized list.

Here are the simple steps to create that combo box from another table that contains the doctors' names. Using a table rather than typing the list allows you to keep your list up to date without modifying your form every time you add a new doctor or one leaves. Here is the simple table of doctors, named "Doctors"

Step 1: Delete the Doctor field from your form design view and replace it with a combo box

After deleting the Doctor field, click the combo box icon in the form design tab

Then, click on your form to place the unbound combo box. It will start the combo box wizard. Note the new field labeled Combo 23 with the Unbound control.

Step 2: Choose the file where the list resides.

In this case, the list comes from the Doctors table and will be entered into the Patient Information table. Note that you may access your list in a query as well as a form.

Step 3: Choose the field that contains your list.

In this case there are two fields, Doctor ID, the primary key, containing a numeric code for the doctor and Doctor Name, a field containing the list we want to access. The Doctor ID field is unnecessary. It is only included to demonstrate a later choice in the process.

Step 4: Choose the order of your list

In this case we want the list alphabetic by the doctor's name. When you pull down the sort field, the wizard will give you a list of all fields in the Doctors table. You may sort the list by any of the fields in the Doctors table.

Step 5: Size the column and if necessary, hide the column containing the primary key of the list table. The hide key column check box gives you the option of displaying the primary key from the Doctors table and inserting that field rather than the name. While that saves a few bytes of storage, it requires you to set a relationship to link and display the name when printing reports or viewing forms. Given the low cost of hard disk space, it is generally easier to insert the entire field into the new table and avoid linking the files.

Step 6: Choose the field in the Patients table for the choice made.

Step 7: Label the field

Note that you can type anything you want in the label box. It doesn't change anything other than the view on the form.

Step 8: Place your combo box field and label and format them to look as you want.

You have completed your first combo box. It's easy and useful.

Two-Year Business Grads May Be A Bargain For Your Small Business

I have a friend whose grandson recently graduated from a prestigious college with a degree in philosophy. He is an extremely intelligent young man with a great future, but he is now stuck in a low-paying job, selling furniture. My friend asked him, "What skills do you have?" The young man was hard pressed to come up with any. He has proven he is smart and has the personal initiative to excel in college, but has few other skills.

I compared his situation with that of a two-year business graduate from our program at the Thompson School Applied Business Management program at UNH. Every graduate of our program has shown proficiency in the following areas:

Financial Accounting - Every student has learned to make journal entries for small business. They have created financial statements from company trial balances. They have made end of period adjustments and closed annual books. They have dealt with depreciation and inventory valuation. They have learned the basics of payroll.

Managerial Accounting - Every graduate has used ratio analysis to analyze financial statements to make informed decisions. They all have done break-even analysis and product contribution margin calculations.

Sales - Every graduate from our program has learned and demonstrated the ability to close a sale with any of 14 different closes. They are schooled in proper objection handling techniques. They have learned dozens of approaches and many prospecting techniques.

Human Resource Management - Each student has learned how to interview and check references. They have been taught the basics of motivation and human needs both from a scientific perspective and from a business management point of view.

Computer Skills - Every graduate of our program has created at least 80 Excel spreadsheets and at least 25 Access databases. They have used Access to produce custom mailing labels and custom reports and invoices. They have created professional presentations with PowerPoint for sales calls and for stand-alone kiosks.

Marketing Skills - Each student has studied marketing and advertising from a small business point of view. Our courses include retail layout and product placement.

Business Law - While our graduates are certainly not legal scholars, each of them has demonstrated an understanding of the parts of a legal contract and has a good idea how to stay out of legal trouble and when to consult an expert.

General Business Skills - Each graduate is familiar with and most have created their own professional business plans. As students they have organized and operated their own business raising funds for scholarships. To date, they have raised almost $100,000.

Now, let's consider the four-year business graduate as a comparison. Most four-year business degrees are big business oriented. Their graduates have good financial statement analysis skills, but most have little or no knowledge of the day-to-day journal entries and bookkeeping tasks necessary to create those documents. They understand high finance and microeconomics, but couldn't perform a standing-room-only close or handle a price objection. They know Maslow's hierarchy of needs, but couldn't give you a practical list of ways to motivate your employees to perform at the top of their abilities. They understand bond markets, but not convenience markets.

We cannot claim the each graduate is an expert in every area. As with all students, some are really excellent and others barely pass with a minimum grade. Each student can, however, provide grade transcripts to prospective employers. The transcripts tell a story. They not only show the individual grades achieved in individual courses, but they also show the growth and motivation of the student over time. The next time you are looking for a new employee, consider the two-year business graduate. They have skills that most four-year grads don't. They are ready to hit the ground running and earn profits for your company.

How To Write A Secure Password

Every business computer contains information you want to keep secure. You don't want people reading or corrupting your accounting records, employee records, customer records or business communications. The first line of defense is a good password.

To create a good password, you must first understand what makes a bad password.

• Your user name repeated
• Any personal information like your birthday; any family member's name; your pet's name; your SSN, etc.
• Your license plate number
• Obscene words
• A combination of your kid's birthdays or names
• Anything from geeky computer games like "plugh" or "BFG9000"
• Unmodified words from the dictionary

Any hacker worth his salt will try all of the above. A really good hacker will have a program that tries different passwords, including a wide variety of dictionary words. The programs will even be smart enough to substitute special characters for letters, like "@" for "a" and "$" for "s".

What Makes A Secure Password

Any good password is at least 6 characters long. Longer is usually better, but be aware that some Unix/Linux systems truncate passwords at 8 characters. Anything beyond 8 is ignored. Here are some other techniques that help:

• Combine words and numbers. Stick a number in the middle of a word (Toi6let).
• Combine two or more words with a number or symbol (diet!pepsi).
• Mix a date and a word (F1o9r6d5), a combination of Ford and 1965.
• A part number or serial number from a piece of equipment.
• A score you can remember (USA3Japan2).
• A license plate number that doesn't belong to you.
• How about a mnemonic for a phrase you know? "trissmont" (The rain in Spain stays mainly on the plain) Stick a percent sign or explanation point in the middle and you have a great password.
• Don't forget to use upper case and lower case characters. They are different.

What's Next

Once you have created a good password, you need to secure it. Here are some rules to keep it secure. Some are obvious, others not so much.

1. Don't write it down anywhere near your computer. If you must write it down, store it some place secure.
2. Never use an important password on more than one system. The habit of using the same username and password on multiple accounts is the easiest way to make a secure system, insecure.
3. Don't tell anyone your password (one of the obvious ones). It is amazing how many people give out the passwords to computers with sensitive information.
4. Check frequently for key logging devices attached to your computer. They may be installed between the keyboard and the computer or may look like flash drives, plugged into a USB port. If you find one, you have a determined hacker nearby.
5. Change your password frequently. This one can be troublesome. If you change it too often, you will not remember it and will have to write it down somewhere it can be accessed easily, a violation of rule 1.
6. Never save your password in an email or Outlook note.

Finally, be aware of people looking over your shoulder. While most aren't trying to steal your password, some are.

Now that you have a secure password, think about securing your computer. If your BIOS isn't password protected, anyone can stick a Linux CD into your CD drive and boot the computer, gaining access to your hard drive. Encryption programs are available for your critical data. Hardware and software firewalls keep network users out. Lock your office door. Lock your keyboard when you leave your desk. Install a locking device to avoid a snatch and run thief.

Computer security is a serious matter. A good password is a good start.

Written by Steven Tuttle - sdt@unh.edu

Should You Install Microsoft Vista or Stick With XP

Now that Microsoft Vista has been around for a couple of years, is it time to switch from XP? The answer is a resounding NO! Here’s why.

For most small business users, the operating system of a computer is all but invisible. We use applications and share files. The technical stuff in the background might as well be operating in ancient Aramaic. We want it to run our word processing, spreadsheet, database, accounting and point of sale programs reliably, securely and economically.

Reliability

The latest versions of XP are quite reliable. Even though Apple wants you to believe you are dealing with mass occurrences of the blue screen of death (BSOD), you aren’t. You and your employees turn on your computers in the morning and they work. Vista doesn’t have any advantage here. Verdict: It’s a tie.

Security

Neither Vista nor XP are devoid of security issues. You need a good Internet firewall and good, up-to-date virus protection. It helps to train your employees not to click on everything that appears on their Internet browser screen as well. While you can’t be 100% safe from employee stupidity, you can follow a reasonable regimen of backups, adware and spyware removal. Neither operating system has an advantage here. Verdict: It’s a tie.

Economic Costs

Here is where Windows XP beats Vista by a mile. I’ll divide this category into three sub-categories: Software, hardware and training costs.

Software

All of the current software for Windows PCs runs on XP. Some of the current software doesn’t run on Vista. That’s right. You may have to upgrade your software to run on Vista. There is no advantage to changing to Vista and there may be significant costs to do so.

Hardware

Windows XP runs well on computers with any of the Intel or AMD processors made in the last five years. It will run with 512 megabytes of random access memory (RAM), although I prefer at least one gigabyte. Vista is a slow, bloated memory hog by comparison. Microsoft say is will run on less than one gigabyte of RAM, but I wouldn’t consider running it with less than three. Even then Vista is much slower than XP. The level of equipment needed to run Vista means that you will probably have to replace computers over two years old.

Next, you have to consider the hardware you have plugged into your computers. Printers, scanners, cash drawers and even some of the plug-in cards inside your computers may not work with Vista. For many of your hardware devices, you will need to install new drivers (programs that tell Vista how to run them). For some, there are no drivers available. In some cases, you will have to replace the hardware. Vista will surprise you with many hidden costs in this category.

Training

Vista doesn’t look like Windows XP. Your employees will not be able to find some of the functions they are accustomed to using on a daily basis. This means training costs and time wasted searching for OS functions. This may be the most expensive part of the whole changeover.

Summary

Microsoft is claiming that they will stop supporting XP at the end of June. I don’t believe it. Recently, Microsoft announced that a copy of XP will come with every new copy of Vista. All you will have to do is push the “go back to XP” button to uninstall Vista and install XP in its place. They have admitted defeat. XP is good for at least two more years.

The next version of Windows, Windows 7 is expected to ship at the end of 2009 and is expected to address many of the negatives of Vista.

My evaluation: Skip Vista and wait for Windows 7. There is no good reason to change from XP to Vista and there are lots of reasons to keep XP. If you have already bought some Vista machines, keep an eye out for a Microsoft offer to revert to XP. Your Vista machines may function without problems, but if they do wreak havoc with your office, go back to XP.

Written by Steven Tuttle - sdt@unh.edu

Editor's Note: This month we present the first in a series of management articles for the restaurant owner. Charlie Caramihalis is putting his decades of restaurant management experience into print. Each article will present a technique that can be used to increase profits for your business.

Calculating Actual Cost of Beef Entrees

By Charles Caramihalis, Associate Professor
Thompson School of Applied Science, University of New Hampshire

Food cost continues to remain one of the highest variable costs faced by any restaurateur. This fact coupled with continued price increases of commodities such as meat and increased competition among restaurants for their share of the customer base in this economy make it even more difficult to turn a profit in our business.

Many owners or even worse the chefs they hire improperly establish selling prices for the steaks they sell because they confuse price per pound of beef purchased with actual yield of beef following fabrication in the restaurant kitchen.

Establishing the actual raw cost per portion of beef entrees is simpler than one might think. To illustrate this point, consider the following scenario. A restaurant purchases a tenderloin strip which it cuts into 8 ounce filet mignons. The purchase weight of the strip is 8 pounds at a cost of $8.10 per pound for a total cost of $64.80. Correct? “Yes and no,” the smart restaurateur would respond. While the amount paid to the purveyor for the beef is $64.80, this amount is not reflective of the actual cost to the operation of each steak hitting the plate when ordered by the guest.  Many owners/chefs mistakenly divide the cost per pound shown on the invoice by the individual serving size without taking into account the actual yield after trim. Assuming this is done given the scenario above, the cost of each steak would be incorrectly computed to be $4.05.

 Although initially time consuming, the correct method requires the chef to take into account average trim loss when breaking down the whole tenderloin. If the average waste is deemed to be 20%, then the actual per pound cost would compute to be higher than $8.10. To correctly figure raw portion cost, simply multiply the invoice cost per pound ($8.10) by the additional percent lost in trim (20%) to get the true cost per pound:
                $8.10 x 1.2 = $9.72.
Therefore, the true raw cost per portion of each steak sold is determined to be $4.80, not $4.05.

As one can easily determine from the example above, incrementally this cost discrepancy represents huge potential losses in revenue for the operation. The same analysis must be completed for every center of the plate item fabricated in the restaurant kitchen to avoid these hidden losses.

Creating an Excel Loan Payoff Sheet

In previous articles we explored the Excel payment function (pmt) and the use of absolute references in formulas. Here is a useful spreadsheet that uses both. Let's assume that you purchased a new house in January of 1998 with the first payment on February 1. You borrowed $250,000 at 5.9% for 30 years and have been making payments on the first of the month for the last 10 years. How much do you owe the bank on February 1 of this year? This spreadsheet will give you the loan payoff balance for any month, for any loan of any amount and any interest rate. It assumes monthly payments but you can adjust for different periods.

First, here is the basic setup of the spreadsheet:

The payment function is =pmt(c2/12,d2*12,-b2). Note that the interest rate had to be divided by 12 to calculate monthly interest and that the term of the loan had to be multiplied by 12 to correctly calculate the total number of payments. The minus sign in front of the loan amount (principal) makes Excel return a positive result rather than the negative liability figure that is the default.

Our task is to write the formulas necessary to initialize the first payment and then to complete the necessary formulas for each successive row.

The First Row

The first row is different from all the others because it gets the initial balance from cell B2. After this row, every successive row will get its starting balance from the ending balance on the previous row. The formula for cell B5 is simply =B2. That puts the beginning loan balance into cell B5. By doing it this way, when we change the loan amount in cell B5, the entire spreadsheet will recalculate with the new amount.

Now it gets just a bit more complex. To calculate the interest paid for the first month, we need to multiply the starting balance by the monthly interest rate. Here's the formula: =b5*c2/12. Note that we had to divide the interest rate by 12. The rate is written as the annual interest rate. We needed the interest rate for one month. Here is a picture of what we have so far:

To calculate the principal paid, we can simply subtract the interest paid from the payment. That formula will be: =E2-C5. The Ending balance then can be calculated by subtracting the principal paid from the starting balance for this payment: =B5-D5. Here is the complete row of formulas and the results.

It appears that we have finished the first row, but we have not. Only the starting balance formula is different from the formulas on successive rows. Using absolute references, we can adjust the interest paid and principal paid formulas to work on all rows. The ending balance formula is ready for the rest of the spreadsheet.

Notice that we need the interest rate for every item in the interest paid column. Since the interest rate is always at cell C2, we can adjust the row 5 interest paid formula to read =B5*$C$2/12. Note that when we copy this formula down the column that the B5 will become B6, B7, B8, etc. It will refer to the starting balance for each payment. The C2 will continue to be C2 because it is locked by the dollar signs. By doing that, the formula will always refer to the interest rate at cell C2. That allows us to change C2 and automatically adjust the entire sheet to a new interest rate.

Now, we will do the same thing with the principal paid formula. The payment is always at cell E2. By putting dollar signs before the E and the 2, the formula will always refer to that cell to find the payment amount. Here's what we have so far and the results.

Note that the amounts didn't change when we used the absolute references.

Subsequent Rows

All that remains is to complete row 6 and copy it down as far as we want. The starting balance for the second payment is the ending balance for the first payment. The formua is simply: =E5. When we copy that down, the row number will adjust appropriately for each successive row. Since we adjusted the other three formulas, you may copy them down to row 6 now. Here are the resulting formulas and numbers:

You may now copy the row 6 formulas down the spreadsheet using the fill handle. Our original quest was to find the balance on February 1 of 2008. Let's see what we find.

Our balance before the February 1 payment is $208,652.81. After the payment it is $208,195.85.

One more note. To create the dates in column A you can use the autofill function. All you have to do is type the first two dates in A5 and A6. Then select both dates and use the fill handle to increment down the column for as many rows as you like. But... Autofill is a different topic for a different time.

Written by Steven Tuttle - sdt@unh.edu

Help Your Employees Manage Their Careers

Creating a Career Ladder

By William H. Scott
Professor of Applied Business Management
University of New Hampshire

Motivated employees require a vision of how efficient performance in their present position relates to their long range career goals.

The first step in visualizing your path is to know what your goal is.  According to the Cheshire Cat in Alice in Wonderland, "If You Don't Know Where You're Going, Any Road Will Get You There." To get there you have to know where there is.

I like to use a magic wand to help employees visualize a career objective.  If I had a magic wand and could grant you any job in the world, what would you choose?  The answer to that question is the top rung on your employee’s career ladder.  Now all we have to do is determine the steps to reach that goal.  Obviously the path will be different for each career.

Since I work with students, it is relatively easy to give them an assignment to contact 10 people in their chosen career and to interview them with the primary question being:  How did you get your present position?  Other questions would be:  Is college necessary?  What courses will help the most?  What jobs did you have that gave you the experience to be prepared for your position?  What do you like/dislike about your present position?

This procedure can easily be accomplished by a non-student.  It is very flattering to be asked to help a younger person to reach your position in life. Nine out of ten people will answer your questions if asked.

As a result of questioning 10 role models they will learn the most probable path to take.  A second result is they may find a mentor who will advise them on an on-going basis. Once the goal and steps to reach the goal are determined, the employer should provide encouragement to attend the correct seminars or colleges to gain the necessary experience.

A career ladder with dates should be developed.  For a Human Resource Manager that ladder would look something like this:

For a Restaurant Manager/Owner the ladder may look like this:

Throughout the rungs it is expected that workshops/seminars/courses will be taken as suggested by mentors or advisors. By helping your employees create their career ladders, you provide them with the growth opportunities they need to manage their careers.