How to Set Up Payroll in Microsoft Access

Microsoft Access brings a database with "search-query language" (SQL) to small businesses. Its flexibility and features deliver a lot of computing power for an employer. Setting up payroll in Microsoft Access is a good tool to track employee records. Setting up a full payroll system calculating gross pay, deductions, and employee net pay is requires a highly sophisticated database programmer. An employer with intermediate to advanced Access skills can set up a payroll record system.

Step 1

Name and create a new database from the "New" command on the "File" tab. Select the "Home" tab, and a new table is opened. Switch to "Design View."

Step 2

Enter the name of the "Primary Key" in the "Field Name" column on row 1. "ID" is the default with an "AutoNumber" in the "Data Type" column. This can be kept or changed to another unique record identifier, such as an Employee ID number or Social Security Number. The "Data Type" can be kept as an "AutoNumber," or changed to a manual entry, as long as the "Indexed" property is set to "Yes (No Duplicates)."

Step 3

Create fields for all of the information required from new employees. This includes: 1. First and last names, initials or middle names, if desired 2. Home addresses and mailing addresses 3. Work, home and mobile phone numbers 4. Number of deductions for Federal and state income tax withholding 5. Social Security Number 6. Driver's License or other state identification number 7. A check box ensuring that "proof of employment" eligibility has been verified 8. A text box to record the documents verified 9. Official employment start date 10. Official employment termination date (to be filled when the employee leaves) 11. Wage rate 12. Job title and other information as desired by the employer Resumes and applications can be digitized and attached to the record, if desired

Step 4

Assign data type to each field setting the required properties to format numbers, define number of characters permitted in a field and determine whether the data are to be indexed. In building a database, it is also prudent to add a field description defining its purpose, an action to jog memory later when the database needs to be modified or revised.

Step 5

Save the Table, naming it "Employees" or other meaningful table name.

Step 6

Select the "Create" tab and click the "Form" button in the "Form" group. Add all or some fields to the form and save it. Format field properties to match the Table properties. Save it once more before returning to "Form View" on the "View" button of the "Home" tab. Use the form for data entry.

Step 7

Select the "Create" tab and click the "Report" button in the "Report" group. Add all or some of the fields and then switch to "Layout" view. Modify column widths and format fields and labels. Modify fonts, colors or select a pre-formatted design from the "Design" dropdown box. Save the report. The Report template will let you print payroll reports.

Step 8

Encrypt the database for security. Close the database, reopen in Exclusive Mode using the down arrow on the "Open File" window's "Open" button and selecting "Open Exclusive." Click the "File" tab, select the Info command, and then click "Encrypt with Password" on the "Info" pane. When the "Set Database Password" dialog box appears, set a password and type it a second time for verification. Click OK. The next time the database opens, the password will be required. Type your password in the Password box, type it again in the Verify box, and then click OK.

Tip

Strong passwords are a minimum of eight characters and include numbers, upper and lowercase letters, and a special character, such as “!” or “&.” Use “Caption” properties to give fields plain language names. Changing the field name “FirstName” to “First Name” looks more professional on reports. There are online and off-the-shelf payroll software packages that handle time card and paycheck calculations. To do so in Access requires extremely sophisticated programming skill and understanding beyond the content of a summary article.

Warning

To calculate timecard and payroll deductions, there are many affordable proprietary and off-the-shelf small business bookkeeper solutions; in most cases these will be far easier to use and require less time to set up than making your own payroll calculations database.

references