INFO 1012 ELECTRONIC FILING AND CALCULATING

Week One, LESSON 1

ELECTRONIC FILING USING ACCESS 2003

Some companies use Access or another database program to handle their filing via computer, or electronically. In this exercise you will learn how to file alphabetically using Access 2003.

Open your Access program

Click on the NEW button in the toolbar (looks like a little piece of paper).

In the New File Task Pane at the right, click on the hyperlink labeled Blank Database.

Click on the dropdown list beside the Save In textbox and choose where to save the database. Saving to your desktop and/or a flash drive is fine.

Click in the textbox beside File Name: and replace the name that is there (db1.mdb) with the file name M1L1Customer.mdb

Click the Create button.

You should now see your database window as shown below:

Define the fields in the new table of your database by clicking on the NEW button with “Create Table in Design View” highlighted in the list in the white portion of the database window.

When the New Table dialog box pops up, click on Design View, and click OK.

Each field will take up so much space as a column in the table when you see it in datasheet view. You have to tell Access how wide you want the column. Usually, you count the letters in the indexed name or other column and if the longest name has 30 letters, that’s the size you set for Field Size, so that all 30 letters will fit in the column. We have decided the field sizes for you, so use those in Figure 1.

Use the information in FIGURE 1 to fill in the design grid for your table (refer to the screen capture of that design grid in FIGURE 2). Then, to set the Field size, click on each row of the Customer: Table design grid at the top, and look toward the bottom each time to set the Field Properties for each row (field) in the grid. Replace the numbers in the Field Size textbox in the Field Properties portion of the grid (at the bottom of the screen) with the numbers in the table in FIGURE 1.

FIGURE 1:

Field NameData TypeField SizeField Description

Customer NumberText2Customer Number (Primary Key)

Indexed NameText90Name in Indexed order

Street NameText50Street name in address

Street #Text6Number of street address

Address 2Text50Second line of address

CityText15Name of city

StateText2Name of state (2 letter abbreviation)

FIGURE 2:

Click on the square to the left of the Customer Number row. This should select the whole row. You need to set a Primary Key (unique identifier for each record). With the row still highlighted, click on the Primary Key button on the toolbar (looks like a small key). Every record needs a unique identifier, used to identify that record, and only that record, much in the same way a Social Security number is used to identify one person, & only that person.

Once you have entered the information in the table in design view and set the Primary Key, click on the close box in the upper right corner of the Table1: Table window title bar to close the table, not the Access program.

When the popup dialog box asks if you want to save the changes to Table 1, click Yes.

When the Save As dialog box pops up, name the table Customer, and click OK.

Double click on the Customer table title (the fourth line in the database window) and it will open the Customer Table in datasheet view.

Enter the records for the following customers, filling in the Customer table columns. You must enter the records in all capital letters, with no punctuation, for this to work properly. Also, you must decide on the proper indexing order and format of the names and addresses and type them in properly, according to the filing rules you have studied. The first name is given for you to use as an example. You fill in the other nine names and addresses, starting with Customer # 2 and finishing with Customer # 10.

Cust # Name StreetName Street # 2nd Address City State

01 / Cathy-Sue Dennison / W. Markham Avenue / 4400 / Little Rock / AR
02 / Calif. Attys. Commission / Monterey Drive / 1630 / Santa Rosa / CA
03 / Ms. Rachel Coorier, DDS / Burnham Avenue / 41 / Providence / RI
04 / Cho Fung / VistaDel Monte / 27 / Costa Mesa / CA
05 / Entrepreneur USA / Broadway / 650 / Englewood / NJ
06 / Fast and E-Z Tax Service / Fourth Street / 556 / Charlotte / NC
07 / East View Recreation Ctr. / Lafayette Place / 4029 / Portland / OR
08 / E Z Software Co. / Terrace NW / 114 / Miami / FL
09 / Covr-All Carpet/Tile / Kensington Avenue / 3699 / Philadelphia / PA
10 / Courier and Sons, Inc. / Monterey Drive / 843 / Santa Rosa / CA

Once you get all the names and addresses typed in, notice that the names aren’t in proper filing order. Dennison is listed before Coorier, and you have some more names starting with a “C” at the very end of the list. Now, close the table (not the Access program), and if it asks if you want to save the changes, click Yes.

In electronic filing, once you have entered the names into the database, you can issue commands to put the names in proper filing order, instead of having to file them manually. In order for the filing order to sort correctly, you MUST have indexed the names and addresses correctly, according to the filing rules you learned, and you CANNOT have any misspelled words, spacing errors, or typographical errors. Any errors of any kind may cause the program to sort improperly. Any electronic filing program is only as accurate as the data you put into it.

To electronically alphabetize this list of names, double click on the name of the table again (4th one in the list in the database window). This should open the table again with all 10 names in numerical order….Customer # 1 at the top and Customer # 10 at the bottom of the list.

To put the table in alphabetical order according to the Indexed Name column, click with your left mouse button on the Column Head entitled “Indexed Name.” This should highlight that whole column. Now, click on the Sort Ascending button in the toolbar (looks like an A to Z with a downpointing arrow). This should immediately put the 10 names in ascending alphabetical order, with the Cs at the top of the list and the Fs at the end. Your Customer # column should now be as shown below. If yours didn’t end up in this order, you may have either: misspelled something, have improper spacing, improperly indexed the name, or otherwise didn’t issue the command properly. Check your own work with the key below:

Also try sorting the column in descending alphabetical order (Z-A button in the toolbar). We will try other electronic filing sorts and queries in future modules; therefore, you will want to keep this Access database file available for use later in the course.

M1 L1 Access FilingPage 1