Business Finance - Accounting Access Assignment

FIRST DELIVERABLE INSTRUCTIONS

Organization of Assignment:

This Access database assignment is organized as follows:

Part 1: Introduction to the database program Access

Part 2: Business Processes, Information Needs and Beginning Database Structure

Part 3: Establishing Relationships between Tables in a Database

Part 4: Creating an Entry Form

Part 5: Adding Calculated Controls and Formatting Controls

Part 6: Creating Queries of Information from Data in the Database

· Parts 1 through 3 are contained within this Word document and comprise the first deliverable instructions for this assignment. On average, the first deliverable takes two to three hours to complete.

Learning Objectives:

Students will:

1) Gain a basic understanding (e.g., factual knowledge, methods, principles, generalizations, theories) about:

· Basic database terminology and the general requirements for efficiently designing a relational database (tables, attributes, attribute types, and relationship types)

· Types of attributes (primary, foreign and nonkey/secondary fields)

· Types of relationships between related tables in a database (“one to many” and “many to many” relationship types)

· The role of a primary key for establishing the principle of “entity integrity”

· The role of a foreign key when a “one to many” relationship exists between related tables for establishing the principle of “referential integrity”

· The role of a dual primary key (“concatenated” primary key) when a “many to many” relationship exists between related tables for establishing “referential integrity”

2) Learn to apply course material ( to improve thinking, problem solving and decisions) by:

· Designing a relational database for recording invoices in a revenue system

· Creating an entry form for efficiently populating tables and generating invoices for sales transactions

· Implementing some basic internal controls in the relational database for improving data collection

· Creating queries and reports from data in the relational database created

· Identifying information characteristics being met by internal controls and database features

3) Develop specific skills, competencies and points of view needed by professionals, including:

· Gaining experience with using Access to create a relational database for recording invoices in a revenue system

· Appreciating the need for systematically planning, designing and controlling a relational database for efficiently and effectively gathering and storing data and producing information output

Besides emphasizing material in Chapter 4 – Relational Databases of the course textbook, this assignment also provides direct insight into certain material from other chapters, including:

· Chapter 1 – Accounting Information Systems: An Overview

· Chapter 2 – Overview of Transaction Processing and Enterprise Resource Planning Systems

· Chapter 14 – The Revenue Cycle: Sales to Cash Collections

This assignment will also be useful for gaining insight into certain material from Chapter 15 – The Expenditure Cycle: Purchasing to Cash Disbursements , because the revenue cycle and expenditure cycle mirror each other in many ways. We will also refer back to certain concepts learned from this assignment when covering SAP in the latter part of the semester.

Table and Figure References in Each Set of Deliverable Instructions:

Various tables and figures are referred to throughout the instructions for both deliverables.

· Tables and figures for Parts 1 through 3 are in a separate Word document that is posted along with this Word document at Blackboard.

· Tables and figures for Parts 4 through 6 are likewise in a separate Word document that will be posted along with the second deliverable instructions at Blackboard.

When instructions refer to a specific table or figure, please refer to it in the separate related document for assisting you with successfully completing the assignment.

Download, save, print each Word document and staple EACH one SEPARATELY from the others . Keeping each set of documents separately stapled will make it easier to successfully complete each deliverable. Keeping the tables and figures separate from instructions will make it easier to move back and forth between instructions and related tables and figures. Keeping each one stapled will help reduce the risk of skipping steps or working steps out of order.

Typographical Conventions Used in Each Set of Deliverable Instructions:

Please note the following typographical conventions, which are used in each set of deliverable instructions:

· Small capital letters are used for keyboard or mouse keys such as ctrl, enter, click and right click.

· Bold type is used for words/icons on a screen in Access that you work with, like click File and Save.

· Bold underlined type is for text that you are to enter via the keyboard.

· Italic type is used for emphasis throughout the instructions. It is also used for new terms or phrases when initially defined in the instructions.

· Keystroke combinations are represented with a plus sign. For example, if required to press and hold the ctrl key while pressing the S key, the text will read as follows: ctrl + S.

· An arrow (→) is used when a sequence of commands needs to be performed. For example, assume that you are instructed to perform the following in Word:

File → Options → Save → Save auto recover information every → 10 .

This series of commands in Word means that you would click File, select Options, then Save options, then check the Save auto recover information every checkbox and after selecting that checkbox, type in the digit 10 .

(As an aside, the example above is a good internal control for making sure that data entered is not lost. Consider controls such as this when creating files in Access and other programs).

· For field names in the Access tables (see Table 2, for example), ALLUPPERCASE type is used for primary keys and Mixed Case for nonkey/secondary keys. This helps with identifying more easily each type in tables.

· Special notes on Access techniques and database concepts are included in boxes such as the one encompassing this bullet item.

ITEMS TO BE TURNED IN FOR GRADING

There are two deliverables for this assignment:

1) The first deliverable is worth a base of 35 points. See schedule for due date and time .

· A backup of your database in progress

· Word document containing, in the following order , cover sheet of statement of academic honesty and rubric, screenshots taken, and formally typed answers to questions contained in the instructions.

2) The second deliverable is worth a base of 65 points. See schedule for due date and time .

Students will complete the instructions in the second deliverable Word document and upload BOTH of the following at Blackboard at the link provided with that document:

· Your completed database

· Word document containing, in the following order , cover sheet of statement of academic honesty and rubric, screenshots taken, and formally typed answers to questions contained in the instructions.

Instructions for Uploading Required Items at Blackboard:

· Go to the first deliverable folder

· Click Upload Both the Backup of First Deliverable Access Database and Word Document HERE

· Attach BOTH required files, one at a time, using Browse My Computer

· Select Submit AFTER attaching BOTH files. If submitting the wrong files or failing to submit both files, you can submit both files again by going back through this process.

You will follow a similar process for uploading required items for the second deliverable.

Page | 26

PART 1

INTRODUCTION TO ACCESS

Part 1 provides an overview of how Access operates. This section addresses the Access ribbon; how to create a new database file; how to save, backup and close a database file; and how to open an existing database file. This section is valuable for completing both sets of instructions. Please read this section very carefully.

1.1 MS Office Version of Access

The screen captures in this assignment are based on the Office 365 version of Access. Access in Office 365 has some features that are slightly different than recent previous versions of Access, but not so much that they prevent using to complete this assignment. In fact, a feature in recent versions of Access is they allow using another version to work on the same database, with the ability to go back and forth between versions. This means a student can complete the assignment using one version on different parts of the database. Students are therefore allowed to use one or more versions to complete this assignment.

If you prefer to use Office 365 exclusively to complete the entire assignment but do not have it on your personal computer, remote login to the university (see highlighted comments posted with these instructions at Blackboard) allows the use of Access in Office 365 located on the university network. Included in the highlighted comments are also instructions on how to obtain a free copy of Office 365 through the university.

1.2 MS Office 365 Access Ribbon

These instructions use the following terminology regarding the Office 365 ribbon structure (see Figure 1 – as previously noted, all figures and tables for a deliverable are in a separate document posted with the respective deliverable instructions):

· File is used to perform operations on the database (open, save, close, print and set global preferences).

· The Office Ribbon is used to perform operations within the database file.

· A ribbon Group is a logical organization of operations within the ribbon.

· The Quick Access Toolbar has useful features such as Undo and Save.

1.3 Creating a New Database

· To open Access using Windows XP, use Start → Programs, locate and open Access; for Windows Vista, use Start → All Programs, locate and open Access. Access is a Microsoft Office Suites program, so it is sometimes found within a Microsoft Office program group.

· Select Blank database from the opening screen (see Figure 2).

· You are given on the resulting pop-up screen (see Figure 3) a choice of where to save and what to name your database. Use the Open File button to locate the location of your choice (such as a USB drive, hard drive on your personal computer, or your school network account) to save your new database.

· Name the database file LastNameFirstInitial_AccessAssignment , e.g., SmithR_AccessAssignment .

· Select Create.

· Remember your file location so you can find your database later.

· Access opens the new database and an empty table named “Table1.” If any changes are made to the table design, or if you add data to the table, Access will prompt you if you want to save the revised table when you exit the table. That saved table will then appear the next time your database is opened. If no changes are made after initially creating the table and you close the table or close Access, Table1 will not appear the next time your database is opened. If you close Table1 and it disappears, you can recreate it as described in Section 2.2 of these instructions.

1.4 Saving, Making Backups and Closing a Database

To close a database file, select File and then Close, or click the X button in the top right corner.

The save operation in Access works somewhat differently than it does in Word or Excel. You do not save the entire database, but rather save the component objects (e.g., tables, forms, queries, reports) as you work on objects individually. This is what is being described in the last bullet item in Section 1.3 of these instructions. The Save command under File (or the Save button above File) should be used frequently after work is performed on an object so your work is not lost if there is a software/hardware/power malfunction. Access will always prompt you to save any unsaved changes when exiting any object. Note that there is no save command to initiate when exiting the Access program. Upon exiting the program, Access performs a global save that captures the status of all the component objects in the database.

Backups are important for ensuring the accessibility of information. Make a backup of your database. Exit Access and find the location at which you saved your database. Make a copy of your database file by right clicking on it, selecting Copy, then right clicking again in a blank area within the same folder or another location you desire to place the backup, and selecting Paste. right click on the new file and select Rename, giving it a name that describes at what point the database is constructed. For this assignment, incorporate both the last step completed and the date of backup when naming backup files, e.g., SmithR_AccessAssignment_1.4_2022-8-16 (Part 1.4 on August 16, 2022).

Backing up data is a very important internal control within any organization, as well as in one’s personal life. It is best to place backups in a location separate from the original file. Organizations have lost both original and backup data because both were stored in the same location (including the same physical location) and then suffered some unfortunate event that caused both original and backup data to be lost.

1.5 Opening an existing database

· From the opening screen (see Figure 2), select Open. Then select the Browse icon to find the directory and file to be opened.

-Or-

If the file you want has been opened recently, it will be listed as a choice in the right pane after selecting Open. click the file.

-Or-

Select Start on the computer, locate the file, and double click it to open.

· If you see a security warning after opening your database, select File → Enable Content → Enable All Content to eliminate this warning from showing in the future.

Please proceed to the next page for Part 2 of the deliverable.

PART 2

BUSINESS PROCESSES, INFORMATION NEEDS and BEGINNING DATABASE STRUCTURE

When starting a new company, one of the first things you would do is hire employees, so you would need to record information about these employees. You would also purchase inventory items to sell. You would also obtain customers and make sales to them using invoices to document the sales transactions. Your employees are responsible for helping the organization be profitable, so you may give them an incentive of earning commissions on sales to customers.

In an AIS database, these types of data can be recorded in the five tables shown in Table 1 (see separate Word document containing tables and figures). In the first part of this assignment, you will create these five tables.

The framework of a relational database is built around tables containing data and designing the tables in such a way data redundancy (i.e., repetitious data) is minimized. Tables are joined, or related, to other tables through common pieces of data contained in each table. Table 2 contains the table data that will be used to populate the database tables shown in Table 1. This table data will NOT BE ENTERED as part of this deliverable – it will be entered as part of the second deliverable. Entering data as part of this deliverable will result in a reduction of the first deliverable grade.

Each column in a table describes an attribute, or characteristic, of a single topic, e.g., EmpName is an attribute of Employee in Table 2 (specifically, it is the employee’s name). Each row describes all the attributes for a specific record, e.g., each row in the employee table describes the attributes of a specific employee. In database terminology, the cells in columns in which data are entered are called fields and the rows are records. Your Chapter 4 of the course textbook also refers to rows as tuples (rhymes with the word “couples”).

In the Access Datasheet View (see Figure 4), the bottom right panel contains the table data. When column headings and rows are added in the Design view, this section of the Datasheet View will resemble the appearance of a spreadsheet.

The data shown in Table 2 will be explained more fully in these instructions. The next step in this assignment is to create the five tables necessary for ultimately creating both an invoice entry form and queries of information located in the database.

2.1 Access Objects

As mentioned previously, the framework of a relational database is built around tables. In Access jargon, the table is an object (also commonly referred to as an entity or relation). Objects store data and provide tools for manipulating and displaying the data. Access objects include the following:

· Tables that contain the underlying database data.

· Queries that allow the user to manipulate, search and sort data.

· Forms that provide a user-friendly interface for entering or displaying data.

· Reports that convert data into useful information for decision making. For example, an income statement that is generated in a database is a report that uses a query to sort for revenue and expense transactions to display the resulting information in an income statement format.

Identifying Access Objects

Access provides visible icons that appear to the left of all objects in the Navigation pane.

Used for tables.

The overlapping tables icon is used for queries, as queries represent the joining of linked tables for analysis and reporting.

Used for forms.

Used for reports.

2.2 Creating a Database and Naming Tables

· If not already having done so, follow the steps in 1.3 to create and name a new database file.

· You should see a screen like Figure 4, the Datasheet View, which is used for creating and populating database tables. When you create a new database file, Access will create the database and usually open an empty table named “Table1.” If you see “Table1” in the right pane, then skip the next bullet item.

· If you do not see “Table 1” in the right pane, as shown in Figure 4, then use the Create ribbon and select Table as shown below:

· From the Home or Table Fields ribbon (above the navigation pane), select View and then Design View:

· Enter Employee in the pop-up box and then select OK.

In this assignment, you will create five tables with the following names:

1. Employee (which you have just created, and will continue to work on in section 2.4.1)

1. Inventory (do not create yet – you will create this table later in section 2.4.2)

1. Customer (do not create yet – you will create this table later in section 2.4.3)

1. Invoice (do not create yet – you will create this table later in section 2.4.4)

1. InvoiceLine (do not create yet – you will create this table later in section 2.4.5)

As a reference to Chapter 2 of the course textbook, the first three tables are master files (employee and customer are “agents” and inventory is a “resource”) while the last two are transaction files (both are “events”).

2.3 Renaming Tables

Section 2.2 describes how to name empty, unstructured tables upon their initial creation, e.g., “Table1” and then naming “Employee.” This section describes how to rename tables that have already been named, structured and even populated. This can be useful if erroneously naming a new table.

From the Data Sheet View or Design View: You must first close the table to rename it. Follow these instructions:

1. Open your database file and you will see a screen that is like Figure 4.

2. Right click on the table, e.g., Employee, in the right-hand panel and Close. If closing an empty table, e.g., “Table1,” it will disappear. See section 2.2 for recreating and naming an empty table.

3. In the left-hand pane (the navigation pane), right click on the table, select Rename, then enter the new table name.

2.4 Creating the Database Structure using Access Tables

The steps below will be used to design and populate database tables in this assignment:

1. Create and design the tables where data is stored.

2. Create the structural relationship between tables.

3. Create forms to enter data into the tables.

It is possible to enter data into tables without fully designing the table and its structure, e.g., to enter data from the Datasheet view shown in Figure 4. Eventually, the characteristics of each field must be described, e.g., data is required/not required to be entered in the field. The instructions below describe how to design the table attributes prior to entering data, which is the most efficient way to design a database.

If not already done so, follow the instructions in section 1.5 to open your database file and section 2.2 to name your employee table. You should be looking at a screen like Figure 5, the Table Design View. If you do not see the Design View, but instead see the Datasheet view (Figure 4), then use the View button in the bottom right corner to switch to Design View.

Use Design View to create the table structure for the data in Table 2. You need to use the exact names and abbreviations shown in the following section to avoid confusion and to fit the information in the space available in the invoice form that you will create later in the assignment.

You are required to specify the field properties for each data element in each table. These properties relate to the data field’s characteristics, or attributes. Examples of field properties to be specified include:

· The size of the field

· The field’s format

· Whether data is required to be entered in the field

· The caption that will appear in Datasheet View for data entry and on reports that include the field

· Any default values, or whether the field can contain a blank or zeroes

· Validation rules, such as those governing a limit on sales commissions, for example

· The text for the error message to appear if the validation rule is violated

· Whether the field will be used for an index file

For purposes of this assignment, the field properties for each data element will be provided to you in these instructions. The properties will vary depending on the field type (e.g., text, number, currency, and data).

2.4.1 Employee Table

This section describes the creation of the structure of the employee table that was created in Section 2.2. The fields and attributes for the employee table are described below.

Field1

Field Name: EMPCODE

By Default, Access should make the first field the primary key, designated with the key icon in the column just left of the field name (see Chapter 4 of the course textbook for discussion of the concept of the primary key, which is THE attribute that uniquely identifies a specific record from all other records).

As you can see in Figure 5, Access uses the default name “ID” for this field. Change the field name by highlighting “ID” and entering EMPCODE. Note that we are using all UPPERCASE letters for the field name of the primary key. The uppercase letter convention will help us quickly identify primary key fields when we review the entire database structure.

Before moving on to the next field, be sure that the key icon appears to the left of EMPCODE in the fieldname location. If there is no key icon, then right click on EMPCODE and select Primary Key from the pop-up menu.

Data Type: Short Text

You are instructed to use Short Text here but could have also used the Number format for this field. Regardless of the format used, you would have to be sure to format EmpCode the exact same way in all other tables where this field appears to the database works properly.

Design View and the Data Dictionary

When you define the Data Type, Field Size and other characteristics of the EMPCODE field, you are creating the database’s data structure or internal level schema. The data structure can be viewed as a component of the data dictionary of the database. See Chapter 4 of the course textbook for more information about data structure, schemas and data dictionaries.

For more information on data types, select the Help ribbon and and search for format property then review the results such as that for date and time fields in Access and the introduction to data types and field properties in Access. In this assignment, you will be required to use these four field types: Text, General Number, Currency and Date/Time.

Remaining attributes of EMPCODE are entered in the Field Properties section of the Design View (see Figure 5).

Field Size: 4 .

Read the following news article and recognize the importance of formally defining the maximum number of digits of data that can be entered in a specific field. Reasonably defined field sizes would have prevented these errors from occurring.

2.4.4 Design Invoice Table

Create another table using Create → Table → View → Design View → Invoice. This table will contain invoice data. Enter the following fields and related attribute settings.

Field1

Field Name: INVNO

Data Type: Short Text

Field Size: 6

Caption: Invoice No.

Required: Yes

Allow Zero Length: No

Indexed: Yes (NO duplicates)

(Leave all other settings as is)

Before moving on to the next field, be sure that the key icon appears to the left of INVNO in the fieldname location.

Field2

Field Name: InvDate

Data Type: Date/Time

Caption: Invoice Date

Required : Yes

Indexed : No

(Leave all other settings as is)

Note: When initially choosing “Date/Time” as the Data Type, Access will default to a format of “month/day/year”. Other formats are available but this is the one desired in the assignment. Therefore, no further format choices need to be made.

Field3

Field Name: EmpCode

Date Type: Short Text

Field Size: 4

Caption: Employee Code

Required: Yes

Allow Zero Length : No

Indexed : No

(Leave all other settings as is)

Field4

Field Name: CustCode

Data Type: Short Text

Field Size: 4

Caption: Customer Code

Required: Yes

Allow Zero Length: No

Indexed: No

(Leave all other settings as is)

Save the table.

2.4.5 Design InvoiceLine Table

Create another table using Create → Table → View → Design View → InvoiceLine . Enter the following fields and related attribute settings.

Field1

Field Name→ INVNO ( Do NOT make this the Primary Key field yet. If noted as the Primary Key, right click on and Click on Primary Key to remove.)

Data Type: Short Text

Field Size: 6

Caption: Invoice No.

Required: Yes

Allow Zero Length: No

Indexed: Yes (Duplicates OK)

(Leave all other settings as is)

Duplicates are OK in this case because a single invoice can have many invoice lines, with each invoice line representing a product sold to a specific customer at that time (see Figure 12-15 in the course textbook for an example of an invoice with multiple inventory products sold). Therefore, we can have duplicates of the INVNO in this table if INVNO is concatenated (combined with) with PRODNO as the primary key of the table. This concept of the ability of having two or more attributes combine as a primary key in a table is noted in Chapter 4 of the course textbook. We will discuss this concept when covering Chapter 4 and refer to the concept as the dual or concatenated primary key.

If Access will not let you select “Yes (Duplicated OK),” and instead gives you a message stating that “Removing or changing the index for this field would require removal of the primary key. If you want to delete the primary key, select that field and click the Primary Key button.”, select OK to the message. You will need to remove the key icon from the INVNO field name. To remove, point to the INVNO field name, right click on and click on Primary Key.

Field2

Field Name: PRODNO

Data Type: Short Text

Field Size : 5

Caption: Product No.

Required: Yes

Allow Zero Length: No

Indexed: Yes (Duplicates OK)

(Leave all other settings as is)

Like INVNO in this table, Duplicates are also OK in this case because a specific product type can be sold many times (i.e., be on invoice lines of many invoices). We can have duplicates of the PRODNO in this table if it is concatenated (combined with) with INVNO.

IMPORTANT: Read the next lines very carefully about how to concatenate (combine) two attributes as a dual or concatenated primary key in a table.

Make INVNO and PRODNO the dual primary key as follows:

Hold ctrl and then click on the row selector for each field. With both rows selected and continuing to hold ctrl, move your pointer and click the Primary Key button in the Tools group. Both rows should now be selected as the dual primary key of the table.

Field3

Field Name: QtySold

Data Type: Number

Field Size: Long Integer

Decimal Places: Auto

Caption: Quantity Sold

Required: Yes

Indexed: No

(Leave all other settings as is)

Save the table.

If any tables are open, close by using right click → Close on each table tab in the right-hand panel.

2.4.6 Make Backup of Database in Progress

This would be an excellent time to make a backup of the entire database. As described in Section 1.4, exit out of Access, use Windows Explorer to find your database file, make a backup copy and rename the copy with the addition of the last step completed and date of backup (for example, SmithR_AccessAssignment_2.4.6_2022-8-16). This information provides a great description to a user seeking the correct backup.

Please proceed to the next page for Part 3 of the deliverable.

PART 3

ESTABLISHING RELATIONSHIPS BETWEEN TABLES

This section of the assignment illustrates how to establish relationships between tables. This feature makes a database powerful for reducing data redundancy and improving data sharing in an organization.

3.1 Establish Relationships among Tables

Reopen your original database – not the backup copy. Only use a backup copy if a significant event occurs to the original, such as the unfortunate corruption of the original file. If having to use the backup, make a backup of the backup and then proceed using it. Do not open any tables – all tables should be closed for this part of the assignment.

The next step in the process of implementing a database is to establish relationships among the tables by pairing the primary and foreign keys (this need to establish relationships between tables is discussed on pages 88-92 of your textbook and we will discuss this in class). A foreign key is a nonkey/secondary key in one table that is a primary key in another table. The ability to make the tables relate and interact with each other is THE feature that makes databases more powerful and efficient than conventional file systems.

click on the Database Tools tab and select the Relationships button, as shown below.

If a list of your five tables is not provided, click the Add Table button from the ribbon to show the list (see Figure 8). Add all five tables (by either double clicking on each table or selecting each table and clicking Add Selected Tables) and then close the list. Move tables (click on heading and drag) into the configuration shown in Figure 9. If one or more tables is not large enough so that all attributes are visible (i.e., no scroll bars at either side or bottom), adjust so that it is.

Note in Figure 9 the organization of the tables. Inventory is to the far left and is a resource table. InvoiceLine and Invoice are next and they are event tables. Employee and customer are on the far right and are agent tables. It is this typical organization that causes this diagram to be referred to as an REA diagram, based on the first initial of each type of table (the type of data contained within).

Note that there are three master files or tables in the database – inventory, employee and customer. Also, note that there are two transaction files or tables in the database – invoice and invoice line. It should make sense that these are categorized this way, because our two activities are transactions, while resources and agents are involved with such transactions and maintained in master files or tables.

Further note in Figure 9 that relationship join lines exist between specific tables. The next task to complete is establishing these lines in your database.

To establish, begin by clicking on a primary key of a table. The primary key is identified with the key when you created the table structure. Primary key field names are also shown in UPPERCASE letters in Table 2, which should have been the format in which the field was designed. Drag the primary key to the related foreign key of the related table. For example, drag EMPCODE in the Employee table to EmpCode in the Invoice table. Refer to the individual tables (Table 2) and/or the Table Relationship Screen (Figure 9) to guide you.

After each drag, an Edit Relationships window will appear (Figure 10). Check the Enforce Referential Integrity box. Referential integrity, a concept discussed in Chapter 4 of the course textbook, guarantees that a foreign key can never point to a nonexistent primary key. After establishing referential integrity, click Create.

NOTE: If you receive an error message like that below , recheck your table structure to be sure that the two fields that you tried to join are formatted EXACTLY the same within each table. For example, if you have EMPCODE formatted as “Short Text” in the Employee table, but EmpCode is formatted as “Integer” in the Invoice Table, then you will NOT be able to join the two fields.

Continue the process of establishing relationships so that your relationships EXACTLY match those in Figure 9, including the “1” and “∞” symbols for each respective relationship established.

If the “1” and “∞” symbols for one or more relationships are NOT in agreement with Figure 9, it could be that:

· Referential integrity is not enforced on the relationship in question,

· A primary key has not been assigned in one of the tables for which you are trying to establish a relationship with another,

· An error has been made in setting up the “Indexed” properties in the design of one of the tables, or

· A relationship was incorrectly established between two unrelated attributes – such as, for example, INVNO in the Invoice table and CUSTCODE in the Customer table.

Check each of the primary and foreign keys in each table to be sure that the indexed settings are correct. If index settings are incorrect, Access may have an incorrect view of the database structure. A common index error is in the InvoiceLine table, which requires concatenated primary keys.

You can edit a relationship by pointing to the join line and right clicking on the line. Go ahead and Right click on the line between the InvoiceLine table and the Invoice table to see that you can change your previous options.

Note the box for Relationship Type at the bottom of the Edit Relationship Window (see Figure 10). The Relationship Type is shown for letting the designer (in this case, you) make sure that it properly describes the underlying relationship between the two tables.

For example, the Relationship Type for the join line between INVNO in the Invoice table and INVNO in the InvoiceLine table should be “one-to-many”. Why? Because, in common business speak, ONE sale (represented by a single invoice) can consist of MANY types of products being sold (with each product represented by a separate invoice line – again, please see Figure 14-16 in the course textbook for an example of this).

Because of this relationship, INVNO is a foreign key placed in the InvoiceLine table. As we will discuss when covering Chapter 4, the proper selection and placement of the foreign key in a relationship is crucial because it reduces data redundancy in the relational database.

More on “Referential Integrity”

Without “referential integrity” (i.e., proper relationships between tables), a database would fail when pointers in one table cannot find a matching row in a related table. Referential integrity will prevent you from entering, for example, a customer name on an invoice form if that customer is NOT in the customer table. As another example, referential integrity prevents you from entering an inventory product number on an invoice when that product is NOT stored in the inventory master file.

Referential integrity also prevents you from deleting or changing records in the table in which the primary key exists because this would result in an “orphan” record in a related table in which the foreign key exists. For example, assume you entered check #101 in the check register table and charged the insurance expense account in the chart of accounts table (a debit to insurance expense). Then assume you tried to delete the insurance expense account from the chart of accounts table. Referential integrity would not allow you to delete the account because such a deletion would result in check #101 having a debit to a nonexistent expense account, i.e., an “orphan” in the check register table.

More on Relationships (Also Known as “Cardinality” in Database Terminology)

Note how Access depicts the relationship between tables.

∞ 1

Customer table

Invoice table

∞ is the symbol for infinity, meaning “many” in a database. The relationship (also commonly referred to as “cardinality”) shown above states that there is a “one-to-many” relationship between customers and invoices.

The “one-to-many” relationship between the Customer table and the Invoice table is articulated as follows in common business speak :

Many sales transactions (represented by respective invoices) can be made to a single customer, but each sales transaction (represented by a specific invoice) can only be made to one customer (i.e., a single sales invoice can only be assigned to one customer).

Because of this relationship, CustCode is a foreign key in Invoice table. If INVNO were a foreign key in Customer table, there would be a tremendous data redundancy of customer information (repetition of data in the nonkey/secondary key fields) in Customer table. This is because each time a sale would be made to a specific customer, the same secondary customer data (name, city, etc.) would be shown repeatedly unnecessarily in multiple unnecessary records in the Customer table.

Study Figure 9 – what kind of table has the “many” side of the relationship between inventory and invoice line; employee and invoice; and customer and invoice? The event table does and it is because events happen repeatedly. Resources do not occur repeatedly, nor do agents. Agents cause events to occur repeatedly and events occur repeatedly involving resources. Note that the event has the foreign key in a relationship with either a resource or agent in Figure 9. This is a good rule of thumb to keep in mind when designing a database.

Carefully read and reread the discussion in the previous two boxes. We will discuss these concepts when covering Chapter 4 in class.

3.2 Make Backup of Database in Progress

This would be another excellent time to make a backup of the entire database. Following instructions provided previously for making backups, name your backup as follows, based on having established relationships: S mithR_AccessAssignment_relationships_2022-8-16. This information provides a great description to a user seeking the correct backup.

DATABASE IN PROGRESS AND OUTPUT TO BE UPLOADED TO BLACKBOARD FOR DELIVERABLE #1: Upload both the backup just made of your database in progress and Word file of output (cover sheet, followed by two pages of screenshots and then formal answers to questions on the fourth page of the document) at Blackboard (steps for doing so are provided earlier in these instructions).

The output for this deliverable will be debriefed, followed by completion of the second deliverable.

10.4

Page | 26