Requirements 1. Using Access or a similar relational database, create the tables needed to set up a database for contributors, contributions, and animals. 2. What data field did you use for the primary record key of the FFF contributor table? Why did you use it? 3. Using Access or similar software as required by your instructor, add yourself as a contributor. 4. Create relationships for the tables. 5. Document your work by printing hard copies of each table in datasheet view and the relationships report that shows how they are related. Access 2007 is the ability to use predefined templates. Some of them are Assets, Contacts, Issues, and Events (Figure 4-11). If you click on one of these, you can name your database and download the template from the web. These templates are ready to use and allow you to add fields, delete fields, or use pre-made queries or reports. Figure 4-12 shows the starting screen for Access 2007. If you click on the ??Blank Database?? icon in the top portion of the screen in Figure 4-11, you will launch the option to open a new blank database (Figure 4-12). A panel will appear on the right side of your screen asking you to name your database. The default name is ??Database1.accdb.?? The ??accdb?? suffix stands for ??Access database?? and Access will attach it automatically if you rename your database (which you should!)?you don?t need to add it. Creating Database Tables As you already know, database tables store data about specific table entities?e.g., customers, vendors, or employees. To illustrate how to create tables in Access, let?s create a table of payroll records similar to the one in Figure 4-1. Getting Started. Your first task is to rename your database something more meaningful?for example, ??My First Database.?? (Blanks are permitted in Access database names.) Type your new name in the filename box. Your next task is to decide where to store it. To do this, note the file folder icon with an arrow to the right of the filename textbox. Clicking on this icon will display a Microsoft ??Save As?? dialog box (not shown) that enables you to select where to store your database. After you have done this, click on the ??Create?? button in the lower-right portion of Figure 4-12. A larger version of the screen shown in Figure 4-13 will appear. Defining a Record Format. The Ribbon across the top of the screen in Figure 4-13 shows five tabs: Home, Create, External Data, Database Tools, and Datasheet. The figure also shows two important components. First, Access assumes that your next job is to create a table of records, and accordingly supplies the default name ??Table1?? in the left portion of the screen in Figure 4-13. Second, the system assumes that each record will have at least one data field with default name ??ID?? as shown in the right side of the screen. Before you enter data in your new database, you must first define the record structure for your table. It is much easier to spend time developing this format prior to entering data than to spend hours changing it later. Figure 4-14 displays the form for developing your database. To get to this screen, right click on the table name ??Table1: Table?? and select ??Design View?? from the set of choices in the drop-down menu that subsequently appears. The screen in Figure 4-14 is a template for creating the record format (i.e., the data fields) of your records. To define a record format, begin typing the name of the first data field you wish to create?e.g., the term ??SocSecNum???in the upper right portion of the screen in Figure 4-14. When you do, the following three columns will appear in that area of the screen: (1) Field Name (which is required), (2) Data Type (also required), and (3) Description (optional). Let?s look at each of these items separately. Field Name. Field names are the names you assign to the data fields in your record. As illustrated in Figure 4-14, you can embed blanks in field names and capitalize selected letters in names as desired. Two general rules to follow when naming data fields are (1) use mnemonic names (that help you remember their use such as ??Zip code??), and (2) do not use long names (which are cumbersome to use). Although it isn?t obvious from Figure 4-14, you can use the same field name in each of two tables?the field names in tables are completely independent of one another. In fact, using the same field names for the same data?for example, ??VendorNumber???in both a Vendor table and a Vendor Invoices table often makes sense because this makes it easy to identify the data field (foreign key) that can link the tables together. We?ll look at this shortly. Data Type. For each data field you create in a table, you must also specify a data type. This tells Access how to store the data?for example, as text, a number, or a date. Several examples of such data are, ??text?? data types for an employee?s First Name and Last Name, a ??currency?? data type for the employee?s pay rate, a ??date?? data type for the employee?s date of hire, a ??Yes/No?? data type for the employee?s qualifications to earn overtime pay, and a ??Memo?? data type (that stores variable-length text) for the Remarks data field. Each data field you specify in a table also includes a set of field properties, whose values show in the lower portion of the screen in Figure 4-14. These include such settings as ??field size?? (e.g., a length of 9 bytes), ??format?? (e.g., a number with a percent sign), and ??input mask?? (e.g., a template for entering a phone number). Figure 4-14 shows the field properties for the SocSecNum field in our table. Note the Input Mask entry, which you can select from a drop down set of items if you click on this property. You might also be curious why we defined this as a ??text?? field rather than a ??number?? field. The reason is because this data value is not really a number that we will mathematically manipulate, but rather a code. Thus, we create it as a text field and limit its Field Size property to 9 characters Finally, if you use a ??number?? data type, you must also select the type of # you wish to use?for example, Integer, Long Integer, Single (a small decimal value), or Double (a large decimal value). These choices are important when using numeric data fields to link tables together?the field types must match exactly for the join to work. Description. The last item that you can create for each data field in a table is its description. This is an optional field that you can ignore when defining record structures. However, as you can see from the figure, data field descriptors help document the table itself, and can also describe exception conditions or contain special notes. Identifying a Primary Key. Recall that a primary key is the data field in each record that uniquely identifies the record. After you have defined the data fields in your table, you can also designate a primary key. This is optional but usually a good idea. For our payroll file example, we will use the employee?s Social Security number (SocSecNum) for the primary key. One way to designate this field as the primary key is to click on the name of this field and then select ??Primary Key?? icon ( ) from banner at the top of the screen. An alternate way is to right click on the field with your mouse and select ??primary key?? from the set of choices in the drop down list. The end result in either case will be the same?a little key icon appearing in the first column opposite the data field you selected, as illustrated in Figure 4-14. Finally, some tables such as join tables do not have an obvious primary key. In such instances, you can ask Access to assign an artificial one by creating a Transaction Number or similar data field name and use an AutoNumber data type for it (see the first column in Figure 4-9). In so doing, Access will automatically assign sequential numbers to each record you create, which can also act as a primary key. Saving a Table. Because you named your database when setting it up, it already has a name. However, if you look at the left of your screen (Figure 4-13) you will see that the table is still named Table1: Table. If you attempt to close your table at this point, Access will prompt you for a name. You can of course use the default name ??Table1,?? but it is better to create your own name for it?for example, ??Payroll Master File.?? You should also include the conventional tbl prefix in any name you create for a table. Thus, for example, we used the name ??tblPayroll Master File?? for our table name in Figure 4-14. Creating Records After specifying the names, data types, sizes, descriptions, and perhaps primary key for the data fields in your table, you can create individual records for it. To do so, you must switch to ??datasheet?? (or run) view. An easy way to do this is to close the design view of this table and then select the ??Datasheet?? view from the View menu in the upper-left portion of the Access screen in Figure 4-14. After making these choices, you should see a screen similar to Figure 4-15. This is a table in datasheet view, and you are now free to input the data for individual records. Begin by entering data in the row with the asterisk (?) and use the tab key to transition from data field to data field. Every time you complete the data entry for a new record, Access will save the record in the appropriate table automatically. If you make a mistake while entering data, you can use your backspace key or delete key to correct it just as you would when correcting text in a word processor. Also, if you wish, you can delete an entire record by clicking on the first column to select an entire row (record) and then hitting the delete key. Creating Database Relationships Lastly, it is important to know how to create relationships between database tables. As you?ve seen from earlier discussions, these relationships link tables together. They also enable users to create multi-table reports, such as the one in Figure 4-3. To illustrate how to create relationships in Access, assume that you have created a department table with records similar to the one in Figure 4-2. Figure 4-16 illustrates the record structure for this table, which you name ??tblDepartments.?? The department code is the primary key for this table. You now have two tables???tblDepartments?? and ??tblPayroll Master File.?? They are related in a one-to-many relationship because each department has many employees, but each employee belongs to only one department. The department code is common to both sets of records, although its name differs slightly from one table to another. (We purposely used different names to demonstrate the fact that the names do not have to match exactly to link tables.) This field will act as the foreign key in the Payroll Master File table. REFERENCE PAGE!!!!!!!!!!!!!!!!!!!!!!

Due September 15th Complete Case Analysis 4-21 in Ch. 4 of Core Concepts of Accounting Information Systems. Refer to the Center for Math Excellence as a resource for completing this week's problem sets. CASE ANALYSES 4-21. Furry Friends Foundation I (Creating a New Database from Scratch) The Furry Friends Foundation is a non-profit organization that finds homes for abandoned animals that are suitable for adoption. FFF began operations with a bequest from a wealthy gentleman who lived his life taking care of stray animals and wanted to be sure that such animals were looked after once he was gone. Although the amount the foundation started with was sufficient to set up an office and begin operations, it depends upon continuing donations to run daily operations. FFF has been keeping its records on 4 6 cards. Over the years, the foundation has had requests from contributors for year-end statements that document their donations to the Foundation for tax purposes. (Usually, donations are given with a particular type of animal in mindfor example, ''for dogs.'') Now that the number of contributors exceeds 500, the president has decided to develop a database to handle the foundation's accounting and reporting needs. The following is a sample of some of the records at FFF. Requirements 1. Using Access or a similar relational database, create the tables needed to set up a database for contributors, contributions, and animals. 2. What data field did you use for the primary record key of the FFF contributor table? Why did you use it? 3. Using Access or similar software as required by your instructor, add yourself as a contributor. 4. Create relationships for the tables. 5. Document your work by printing hard copies of each table in datasheet view and the relationships report that shows how they are related. Access 2007 is the ability to use predefined templates. Some of them are Assets, Contacts, Issues, and Events (Figure 4-11). If you click on one of these, you can name your database and download the template from the web. These templates are ready to use and allow you to add fields, delete fields, or use pre-made queries or reports. Figure 4-12 shows the starting screen for Access 2007. If you click on the ''Blank Database'' icon in the top portion of the screen in Figure 4-11, you will launch the option to open a new blank database (Figure 4-12). A panel will appear on the right side of your screen asking you to name your database. The default name is ''Database1.accdb.'' The ''accdb'' suffix stands for ''Access database'' and Access will attach it automatically if you rename your database (which you should!)you don't need to add it. Creating Database Tables As you already know, database tables store data about specific table entitiese.g., customers, vendors, or employees. To illustrate how to create tables in Access, let's create a table of payroll records similar to the one in Figure 4-1. Getting Started. Your first task is to rename your database something more meaningfulfor example, ''My First Database.'' (Blanks are permitted in Access database names.) Type your new name in the filename box. Your next task is to decide where to store it. To do this, note the file folder icon with an arrow to the right of the filename textbox. Clicking on this icon will display a Microsoft ''Save As'' dialog box (not shown) that enables you to select where to store your database. After you have done this, click on the ''Create'' button in the lower-right portion of Figure 4-12. A larger version of the screen shown in Figure 4-13 will appear. Defining a Record Format. The Ribbon across the top of the screen in Figure 4-13 shows five tabs: Home, Create, External Data, Database Tools, and Datasheet. The figure also shows two important components. First, Access assumes that your next job is to create a table of records, and accordingly supplies the default name ''Table1'' in the left portion of the screen in Figure 4-13. Second, the system assumes that each record will have at least one data field with default name ''ID'' as shown in the right side of the screen. Before you enter data in your new database, you must first define the record structure for your table. It is much easier to spend time developing this format prior to entering data than to spend hours changing it later. Figure 4-14 displays the form for developing your database. To get to this screen, right click on the table name ''Table1: Table'' and select ''Design View'' from the set of choices in the drop-down menu that subsequently appears. The screen in Figure 4-14 is a template for creating the record format (i.e., the data fields) of your records. To define a record format, begin typing the name of the first data field you wish to createe.g., the term ''SocSecNum''in the upper right portion of the screen in Figure 4-14. When you do, the following three columns will appear in that area of the screen: (1) Field Name (which is required), (2) Data Type (also required), and (3) Description (optional). Let's look at each of these items separately. Field Name. Field names are the names you assign to the data fields in your record. As illustrated in Figure 4-14, you can embed blanks in field names and capitalize selected letters in names as desired. Two general rules to follow when naming data fields are (1) use mnemonic names (that help you remember their use such as ''Zip code''), and (2) do not use long names (which are cumbersome to use). Although it isn't obvious from Figure 4-14, you can use the same field name in each of two tablesthe field names in tables are completely independent of one another. In fact, using the same field names for the same datafor example, ''VendorNumber''in both a Vendor table and a Vendor Invoices table often makes sense because this makes it easy to identify the data field (foreign key) that can link the tables together. We'll look at this shortly. Data Type. For each data field you create in a table, you must also specify a data type. This tells Access how to store the datafor example, as text, a number, or a date. Several examples of such data are, ''text'' data types for an employee's First Name and Last Name, a ''currency'' data type for the employee's pay rate, a ''date'' data type for the employee's date of hire, a ''Yes/No'' data type for the employee's qualifications to earn overtime pay, and a ''Memo'' data type (that stores variable-length text) for the Remarks data field. Each data field you specify in a table also includes a set of field properties, whose values show in the lower portion of the screen in Figure 4-14. These include such settings as ''field size'' (e.g., a length of 9 bytes), ''format'' (e.g., a number with a percent sign), and ''input mask'' (e.g., a template for entering a phone number). Figure 4-14 shows the field properties for the SocSecNum field in our table. Note the Input Mask entry, which you can select from a drop down set of items if you click on this property. You might also be curious why we defined this as a ''text'' field rather than a ''number'' field. The reason is because this data value is not really a number that we will mathematically manipulate, but rather a code. Thus, we create it as a text field and limit its Field Size property to 9 characters Finally, if you use a ''number'' data type, you must also select the type of # you wish to usefor example, Integer, Long Integer, Single (a small decimal value), or Double (a large decimal value). These choices are important when using numeric data fields to link tables togetherthe field types must match exactly for the join to work. Description. The last item that you can create for each data field in a table is its description. This is an optional field that you can ignore when defining record structures. However, as you can see from the figure, data field descriptors help document the table itself, and can also describe exception conditions or contain special notes. Identifying a Primary Key. Recall that a primary key is the data field in each record that uniquely identifies the record. After you have defined the data fields in your table, you can also designate a primary key. This is optional but usually a good idea. For our payroll file example, we will use the employee's Social Security number (SocSecNum) for the primary key. One way to designate this field as the primary key is to click on the name of this field and then select ''Primary Key'' icon ( ) from banner at the top of the screen. An alternate way is to right click on the field with your mouse and select ''primary key'' from the set of choices in the drop down list. The end result in either case will be the samea little key icon appearing in the first column opposite the data field you selected, as illustrated in Figure 4-14. Finally, some tables such as join tables do not have an obvious primary key. In such instances, you can ask Access to assign an artificial one by creating a Transaction Number or similar data field name and use an AutoNumber data type for it (see the first column in Figure 4-9). In so doing, Access will automatically assign sequential numbers to each record you create, which can also act as a primary key. Saving a Table. Because you named your database when setting it up, it already has a name. However, if you look at the left of your screen (Figure 4-13) you will see that the table is still named Table1: Table. If you attempt to close your table at this point, Access will prompt you for a name. You can of course use the default name ''Table1,'' but it is better to create your own name for itfor example, ''Payroll Master File.'' You should also include the conventional tbl prefix in any name you create for a table. Thus, for example, we used the name ''tblPayroll Master File'' for our table name in Figure 4-14. Creating Records After specifying the names, data types, sizes, descriptions, and perhaps primary key for the data fields in your table, you can create individual records for it. To do so, you must switch to ''datasheet'' (or run) view. An easy way to do this is to close the design view of this table and then select the ''Datasheet'' view from the View menu in the upper-left portion of the Access screen in Figure 4-14. After making these choices, you should see a screen similar to Figure 4-15. This is a table in datasheet view, and you are now free to input the data for individual records. Begin by entering data in the row with the asterisk () and use the tab key to transition from data field to data field. Every time you complete the data entry for a new record, Access will save the record in the appropriate table automatically. If you make a mistake while entering data, you can use your backspace key or delete key to correct it just as you would when correcting text in a word processor. Also, if you wish, you can delete an entire record by clicking on the first column to select an entire row (record) and then hitting the delete key. Creating Database Relationships Lastly, it is important to know how to create relationships between database tables. As you've seen from earlier discussions, these relationships link tables together. They also enable users to create multi-table reports, such as the one in Figure 4-3. To illustrate how to create relationships in Access, assume that you have created a department table with records similar to the one in Figure 4-2. Figure 4-16 illustrates the record structure for this table, which you name ''tblDepartments.'' The department code is the primary key for this table. You now have two tables''tblDepartments'' and ''tblPayroll Master File.'' They are related in a oneto-many relationship because each department has many employees, but each employee belongs to only one department. The department code is common to both sets of records, although its name differs slightly from one table to another. (We purposely used different names to demonstrate the fact that the names do not have to match exactly to link tables.) This field will act as the foreign key in the Payroll Master File table. REFERENCE PAGE