how to create tables in Microsoft Access

1. OBJECTIVE

The primary objective of this assignment is to help you learn how to create tables in Microsoft Access. By completing this assignment, you will be able to gain DBMS skills such as (1) setting up table structures and field properties and (2) establishing table relationships. You will also gain familiarity with DBMS concepts such as (1) primary and foreign keys, (2) one-to-many and many-to-many relationships, (3) a relationship table (i.e., a bridge entity), (4) referential integrity, and (5) a REAL diagram.

1. COMPANY BACKGROUND

Almost a year ago, Linda was approached about an idea for a new business venture by one of her college pals, Jim. Jim had been considering starting a merchandising business to distribute various table tennis products to schools, private clubs, and individual players in the Midwest area of the United States. Jim asked Linda to fund the initial investment to establish the company, uSPiN, and purchase some inventories. Jim works for Linda as the business manager handling purchases and sales. Linda is responsible for the office administrative tasks and finance functions. Linda and Jim started operations in January this year.

Linda and Jim have been using a manual system to handle the operations of the business. This approach serves their business well because they have only one vendor, i.e., Butterfly, 5 schools and clubs, and 20+ individual players. However, they plan to expand the brands of products that they carry by purchasing some economical products from China. It is likely they will add at least two Chinese vendors, i.e., Double Fish and Double Happiness, by the end of this year. In addition, they also anticipate an increase in their customer base by adding another 50 schools and clubs, and 500+ players by the end of this year. Due to their expectations for the business growth, they hired us to design and come up with a relational database to handle certain operations for their business.

After we studied and analyzed their business needs and operations, we applied relational database concepts and came up with the design below.

• PARTIAL REAL DIAGRAM

Below shows selected entities and relationships for uSPiN. The database that we have designed here will assist uSPiN to operate their business in the next several years. After that, the company will probably need to either expand this database or come up with a new design. This database covers the following areas: (1) vendor records, (2) sales invoice records, (3) customer records, (4) purchase records, and (5) inventory management.

Below is a partial REAL diagram showing the relationships between the tables in the database for uSPiN.

*A Many-to-Many (∞-to-∞) relationship between Purchases and Products (as well as between Sales and Products) can not be implemented directly by Microsoft Access and requires a relationship table (or bridge entity) which results in the PurProd (and SalProd) table. The relationship table titled “PurProd” is necessary because product items have a many-to-many relationship with purchases. This means that any particular product item (e.g., racket) can be ordered and purchased many times on different dates while any one purchase may include several different product items. It is the same logic for the table “SalProd”. Microsoft Access is a relational database management system which does not have the capacity to implement the Many-to-Many relationship. As a result, we need to convert Many-to-Many relationships to One-to-Many (or Many-to-One) relationships by creating relationship tables to bridge these tables.

The attributes (or fields) required in the tables are shown as follows:

Vendors (VendorID, VendorName, Address, City, State, Zip, Tel, Contact)

Purchases (PurID, DatePur, [VendorID], TotalPur, [CashID])

PurProd ([PurID], [ProdID], QtyPur)

Products (ProdID, Description, Cost, SellPrice)

SalProd ([InvoiceNo], [ProdID], QtySal)

Sales (InvoiceNo, DateSal, [CustID], TotalSal, [CashID])

Customers (CustID, CustName, Address, City, State, Zip, Tel, Affiliation)

Cash (CashID, DateCash, CashCode, PayeeID, CheckNo,TotalAmt)

The underline indicates the table’s primary key and the square brackets, [ ], designate a foreign key.  Do not include the underline or [ ] symbol in the field name when creating the tables in Microsoft Access. The primary key is the unique identifier for each item in the table. The foreign keys are derived from the relationships between the entities. The rule followed is this: the primary key of the “one” side of the table is placed in the table of the “many” size as the foreign key.

IV. GENERAL INSTRUCTIONS FOR CREATING A DATABASE AND A TABLE, AND ESTABLISHING TABLE RELATIONSHIPS

First, you will (1) create eight tables indicated above and then you will (2) set the primary key(s) for the tables before you save them. After that, you will (3) establish the relationships between the tables according to the REAL diagram. Be aware of the location where you store the database and make a copy of the file somewhere when you are done for the day.

To Create A Database

• Open a new database:
  1. After you click on Microsoft Access from the Start button (i.e., via All Programs/Microsoft Office for Windows XP, 2000 versions or click on the down arrow button to go the Apps for Windows 7 and 8 versions), you should see the below interface appear on your screen (see Figure 1).

Figure 1. The First Screen (user interface) in Microsoft Access 2016

Microsoft provides many templates for you to start with. You can access them from its first screen (see Figure 1 above).

2. After you launch MS Access, select Blank Desktop Database.
3. If you don’t have access to the hard drive of the computer, insert a USB flash drive. It’s better to work directly on a hard drive. Change the “File Name” box and the directory to a folder under the hard drive (i.e., Desktop) where you can easily find it or to a USB device.
4. Enter uSPiN.
5. Then, click the Create

Once you created a new database file (i.e., uSPiN.accdb), you will see the screen below (see Figure 2). There are three components on the screen, i.e., the Ribbon, the Navigation Pane, and the Backstage View.

Figure 2. Microsoft Access Main Screen

Figure 3. The Ribbon Display Under the Datasheet View of the Tables Object

There are five groups of toolbars in the Ribbon area under the Datasheet View of the Tables Object shown in Figure 3 above: (1) Views, (2) Add & Delete, (3) Properties, (4) Formatting, and (5) Field Validation.

When you click the Table tab below the highlighted “Table Tools” on top of the screen, you can see that the groups of toolbars change to (1) Properties, (2) Before Events, (3) After Events, (4) Named Macros, and (5) Relationships (see Figure 4). The reason for the change is because the tools available under the Fields are different from those available under the Table. So, you need to be aware of the screen that you are accessing at the moment in order to see the groups of toolbars that you are looking for.

Figure 4. The Ribbon Display Under the Table Tab of the Tables Object

To Create The Tables

• Create a table:
  1. Make sure that the Fields under the Table Tools in the Ribbon is selected, i.e., highlighted (shown in Figure 2).
  2. Click on the View button in the Views group of toolbars shown on the left side of the Ribbon.
  3. A Save As dialog will appear on the screen.
  4. Please note that the software sets the first row of the fields as the key of the table when we save the table. We can change it later if we want.
  5. Enter “Vendors” and click on the OK

Figure 5. A Save As Dialog

Now, you see there is only one tab, Design, under the Table Tools in the Ribbon, which provides five groups of toolbars, such as (1) Views, (2) Tools, (3) Show/Hide, (4) Field, Record & Table events, and (5) Relationships (see Figure 5).

Figure 6. The Screen Display Under the Design View of the Table Object

In the Backstage View, there are two sections of input; the upper one is for the basic characteristics of each individual data field, i.e., Field Name, Data Type, and Description; and the bottom one is for the Field Properties of the selected data field from the upper section. We will be using the Field Size for the most part of the assignment.

Note that the Primary Key (a key symbol shown on the left side of the Filed Name in the first row) is automatically set to the first row of the fields when we create a table.

6. Now, let’s enter data fields, data types, and field properties shown in Table I in the table Vendors.
7. Enter “VendorID” to the first Field Name box, click on the first Data Type box and select “AutoNumber”, and enter “Vendor ID Number” to the first Description
8. Select “Long Integer” in the Field Size box at the bottom in Fields Properties. Do not change values of other field properties.
9. Click the second row of the fields and enter the information shown in the Vendors table in Table I.
10. Once you finish entering the information, Save the table by clicking on the Disk button above the File tab in the Ribbon.
11. Close the table by clicking on the Close (i.e., a X symbol) button on the right side of the table in the Backstage View (see Figure 7).

Figure 7. The Design View of the Table Object, “Vendors”

12. Now you are ready to create a second table, “Purchases” (see Table I).
13. Click on the Table button shown under the Create tab in the Ribbon.
14. Follow previous steps to create the table and all of the tables shown in Table I.

Note: To establish a composite primary key (involving more than one attribute/field), click on one field/row when you see your cursor becoming a right-arrow symbol after you move your cursor to the left side of the field and then click on the second field/row while holding down the Control (Ctrl) key. Once you have the composite primary key fields selected, you can then click on the Key button shown under the Design command of the Ribbon. Save the table before you close it and then work on the next table. You need to apply this composite primary key setup when you create PurProd and SalProd tables.

To Establish The Relationships Between The Tables

• Create a Relationship:

1. Before you begin to set up relationships for tables, MAKE SURE that you have set up both InvoiceNo and ProdID as the (composite) primary key in the SalProd table, and PurID and ProdID in the PurProd table. You may have to redo all of the work if you don’t check on this!
2. Click on Database Tools tab in the Ribbon.
3. Click Relationships button in the Relationships group of commands in the Ribbon. A Show Table dialog will then appear on the screen. Select all of the tables by holding on the Shift key and clicking on the last table (i.e., Vendors) in the dialog. Click on the Add Then, click on the Close button.
4. Re-arrange the layout of the tables by clicking on a table and holding on the left button of your mouse. Drag the tables to the position and then release the left button of your mouse so that these tables display exactly like those depicted in the partial REAL diagram (see Figure 8).

Figure 8. Lining Up Tables

5. Be sure you follow the order of creating the tables listed as shown in Table I. Establish the relationship between two tables by clicking on a primary (foreign) key field in one table and dragging and dropping it to a foreign (primary) key field in the related table. For example, the first relationship you establish will be to drag from the VendorID field in the Vendors table to the VendorID field in the Purchase table. An Edit Relationships dialog will appear when you release the mouse button from dropping. Verify the table relationships in the Edit Relationships dialog box and the Relationship Type. The Table/Query and Related Table/Query should both display VendorID in the boxes and Relationship Type should display One-To-Many (see Figure 9).
6. Click on the Enforce Referential Integrity box and then click on the Create Referential integrity prevents you from entering inconsistent data into these two tables.  For example, you would not be allowed to enter a vendor ID number into table Purchases since that vendor ID number has not yet been entered into the table Vendors.

Figure 9. Edit Relationships Dialog

7. Continue setting up the relationships until all of the relationships are established (see Figure 10) and then save and close the Relationships window by clicking on the Disk button and then the Close The order of setting up the relationships does not matter. However, make sure the relationships established are consistent with those depicted in the REAL diagram.

Figure 10. Relationships Established

1. MAKING A BACKUP COPY

• There is nothing worse than having to redo the work because you didn’t make a backup/duplicate copy. You can’t do “Save As” to back up/duplicate Access files as you do in other Microsoft applications. To get a duplicate, you can make a copy of the file itself. If you are working off the hard drive, close out Access and use Windows Explorer/My Computer to locate your Access file on the hard drive and copy it to a USB flash disk or the hard drive. You can also email the file to yourself. You need to rename the backup/duplicate file so that you can save both files in the same directory.

1. TURN-IN
   • Review the objective for this assignment again making sure that you have acquired the knowledge and skills.
   • Double-check with the REAL diagram to see if you have the relationships setup correctly (i.e., EXACTLY the same) as well as the setup for all tables (its data type and field properties. Failure to check will cost you many hours of work later on.
   • You do not need to turn in anything for this assignment. This assignment serves as the input to Assignment 2.

Note: You will need a storage device to transport your file from/to the Lab and home/work (e.g., a USB flash drive). DO NOT work directly off the storage device since the storage space maybe limited. To avoid transporting the files physically, you can email the file to yourself so that you can access it from the lab and at home/work. Remember the location of the file where you created and/or saved the file.

Table I. Tables

Vendors

NOTE: The underline indicates the table’s primary key and the brackets, [ ], designate a foreign key.  Do not include the [ ] or underline symbol in the field name when creating the tables in Access. The primary key is the unique identifier for each item in the table. The foreign keys are derived from the relationships between the entities. The rule followed is the primary key of the “one” side of the table is placed in the table of the “many” as the foreign key.

Purchases

NOTE:  Do not include the [ ] or underline symbol in the field name when creating the tables in Access.

Make sure you remove the “0” from the Default Value of the CashID field (Required should be “No” as well).

PurProd

NOTE: Both PurID and ProdID are the composite key in this table. To setup them both as the key, first unselect the key if it has been selected in the first row (by clicking on the first row and click on the Primary Key button in the Tools), select the second row while the first row is selected if they are the composite key here, and then click on the Primary Key button. Use the Shift key to select continues and Ctrl key for discontinues rows.

Products

SalProd

Sales

NOTE: Make sure you remove the “0” from the Default Value of the CashID field (Required should be “No” as well).

Customers

Cash

**Make sure that its “DEFAULT VALUE” property has no value (i.e., remove the zero)