MIS - TWO ACCESS ASSIGNMENT PROJECT

Saving Your Database

When you are working on a PSU computer, make sure you save your work to your personal network share (P: drive) on the PSU network. Make sure you write down the path to the database so you can find it again.

The database is a file that contains all the related information. To the operating system, the database looks like one file with the file extension of .accdb. All of the tables, queries, reports, etc. you create in the database, are all stored inside this one database file.

You should periodically save your database as you are working on it. The easiest way to do that is to click on the save icon at the top of the window next to the Office button. Once you have created your database and given it a name Access will save your database automatically when you close it.

Working with Tables

A table is a logical organization of related information in your database. You can and will have multiple tables in your database. While there is no specific number of tables a database should have, you should utilize your superior judgment skills when you design a database. It generally makes sense to group a set of related fields into one table. The rule of thumb is that you never want to have the same information in more than one table, except for common fields.

If you have more than one table, you have to join them together in order to use information from each table in one query or report. In order to join one or more tables together, you need a common field in each table so Access can match them together based on their value.

We have 3 tables in our project. The artist table contains information related to individual artist such as name, phone number, and web site. There is one record (row) for each artist in the artist table. The product table contains information about each of the products (CD’s) we sell. There is one record (row) for each product. The reason we have two tables is that we don’t want to repeat information, such as the artist name in each product record. What we do instead is enter the artist code in each product record, and then find a matching artist record by matching the value of the artist code in each product record, and obtaining the artist name from the appropriate artist record.

Create a Table

· Click on the Create tab

· Click on the Table icon in the upper left hand corner

· Click on the down arrow below the View icon

· Select Design View

· When the Save As window appears, enter the name of your table in the Table Name field

Access will display a window for you to enter the fields in the table.

Once you create a table in Access, it will be listed in the All Tables column on the left hand side of the screen. You can switch from table to table by clicking on the table name in this column.

Add/Change Fields in the Table

Fields (columns) are the smallest unit of information in the table and database. Even though fields are made up of a varying number of bytes, it is impossible to directly access the individual bytes within Access, only the fields.

You need to define a separate field for each piece of information you want to store and individually access. For example, in our database, we have separate fields for artist name and web site. This is because we might want to sort on the artist name, or we might only want to print the web site.

There are basically two data types, text and numeric. Text data includes both alpha and numeric characters. Whenever you work with text data, you have to enclose it in quotes. For example, if you are looking for all the artists with a music style of jazz, you type it as “Jazz”. Numeric data contains only numbers, and a few selected special characters. You don’t enclose numeric values in quotes. If you are looking for all the product records with unit costs greater than $7, you type it as >7. You can only perform arithmetic computations of fields defined as a numeric data type. Numeric data types include number and currency. If you try to use a text field in a numeric computation, it won’t work.

To create a new field in your table:

· Make sure you are in design mode.

· Go to the bottom of the fields displayed to add a new field on the next line (first non-blank).

· Type the field name and select the data type.

· If you select a data type of text, the field size will appear as an option in field properties section with a default size of 255. Highlight the field size and type in the correct field size.

· If you select a numeric data type, such as number or currency, the field size will default. You can change the field size in the field properties window.

· Press Enter.

· Repeat this step for each field you have to add.

To change an existing field in your table:

· Click on Tables in the Objects column in the left hand side of the window.

· Click on the icon of the table you want to change.

· Click on Design.

· Highlight the field you want to change and make your changes.

Primary Key

The primary key is data that Access uses to find specific records in your table. For example, in each record of our user table, we have a field called user ID. The user ID must be unique (different) for each record. When you want Access to find that specific record, you must provide the user ID.

To make a field the primary key:

· Right click in the column to the left of user ID.

· Select Primary Key.

In this assignment, we don’t want to define a primary key for the Sales table, because whenever we read the file, we read the entire file, not just one record. Additionally, if we sell the same CD multiple times on the same date (this is what we want to happen) we will have multiple Sales records with the same product code, sales date and sales price. If we have multiple records with the same primary key, Access will not let us store them. Therefore, we don’t want to define a primary key for the Sales table.

Field Properties When you are working with fields in the design view Access will display the Field Properties window directly below the field definition window. The exact content of this window will vary with the different field types. You will use the fields in this window to define the specific attributes of the field such as size, whether or not it is required, etc.

Field Size

Enter the length of the field.

Input Mask

Enter an input mask to define a pattern for the data to be entered into this field. For example, a telephone number would be defined as 000\-000\-0000, a social security number 000\-00\-0000. You can skip this if you don’t need it.

Caption

Enter a label to be used for the field when it is used on a form. If you skip this, the field name will be used.

Default Value

A default value is a value for a field that always defaults into the field when the record is created. For example, you may want the word “Jazz” to default into the style field because so many of the artists perform jazz, and you won’t have to type it each time. You will only have to go back and change the word from Jazz to another valid value for style.

To set default value:

· In the default line for the field, type the default value. If the data type is text, you have to enclose it in quotes.

Validation Rule

You will want to add validation rules (commonly called edits) for certain fields in your database. This is important because it prevents users from entering information in the field that doesn’t make any sense. If you have five valid values for style, then you only want the users to enter one of the five valid values into the product table.

To add validation rules:

· Type the values that are acceptable, for example “Jazz” or “Folk” or “Pop” in the validation rule field. Be sure to enter the word or between each acceptable value. Access will add the double quotes around each field for you.

Validation Text

If you enter a validation rule, you have an obligation to enter a corresponding message that you want Access to display the data entered doesn’t match the validation rules. There is nothing more frustrating for users than to have a computer system reject what they are entering, and not tell them why. Make sure your validation text makes sense to you, and to the users of the database.

To add validation text,

· In the validation text field, type the message you want displayed, for example “Invalid Style”, when the data doesn’t meet the validation rules (pass the edits). The more detail you provide the user, the better.

Required

Enter Yes if the field is required, No if it is optional.

Allow Zero Length

Enter Yes if you want to allow zero length entries in this field, No if you don’t.

Indexed

Enter Yes if this is a key field, No it it isn’t. If you enter Yes the value of this field must be unique across the entire data base.

Unicode Compression

Enter No.

IME Mode

Select No Control.

IME Sentence Mode

Select None.

Smart Tags

Leave blank.

Alignment

Select the appropriate alignment for the field. If you’re not sure, select General.

Saving your Table

When you are done adding and changing the fields, close the table window and save your changes.

To close the window and save your changes:

· Click on the X in the upper right hand corner of the table window to close the table.

· Click on Yes to save your changes to the table.

· In the Save As field type the table name in the format specified in the assignment, for example, Artist and click on OK. This will save your table.

You can also click the Save icon in the upper left hand corner of your screen at any time to save your database.

Add/Change Data in the Table

After you have your tables designed, you need to load your data into them. It is possible to design a form for this purpose, but initially you can load the data directly into the database in the datasheet view. This permits you to quickly load a lot of data so you can get your database up and running as soon as possible.

To add or change the data in the table:

· Click on Tables in the All Tables column on the left hand side of your screen.

· Double-click on the table you want to maintain.

To add data:

· In the first row without data, type the appropriate information in each field.

· Press Tab to advance to the next field.

· When you get to the end of the row, press Enter.

· The cursor will advance to the next row after you finish entering a row of data.

· Repeat this process for each row of data you want to load.

To change data:

· Select the record you want to change.

· Tab to the field you want to change.

· Highlight the data you want to change.

· Make your changes.

· Press Enter.

When you finish adding or changing the data in the table,

· Click on the X in the upper right hand corner of the table window to close the table.

Importing Data into a table from Excel

There are many cases where it may be more convenient to import data from an Excel spreadsheet then it is to key the data into the datasheet in your database. You have to meet a couple conditions before you can import data from Excel:

· The data must be set up in Excel exactly the way it has to go into an Access datasheet.

· You must have column headings on the first row of the Excel spreadsheet that match the field names in your table.

To import data into your database:

· Close the table you want to import data into.

· Click External Data – Excel Spreadsheet

· In the Get External Data – Excel Spreadsheet window:

· Click the Browse button and navigate to the folder where the Excel file is located

· Select the file and click Open

· Select Append a copy of the records to the table

· Select the Table you want to copy the records into from the drop down box

· Click OK.

When the Import Spreadsheet Wizard opens:

· Select the worksheet (usually worksheet 1) that contains the data you want to import and click Next

· Confirm that the first row of data contains the correct column headings and click Next

· Confirm that the table you want to import into is displayed in the box and click Finish.

· If the warning window shown above appears, click Yes to proceed anyway.

· Click close. Do not check Save import steps.

· Open the table to confirm that the data has been copied into the fields correctly.

Joining Tables

Joining tables is pretty much a non-event in queries. When you add more than one table to a query, it automatically creates a relationship between the two common fields in the two records. A “join line” between the two tables graphically illustrates this relationship.

In the above illustration, you can see that the Artist and Product table are joined together because there is a line between the Artist Code in the Artist Table and the Artist Code in the Product Table. Artist Code is the common field that both tables contain. Since the line is bold and you see the 1 and infinity symbols, you can tell that there is a one to many relationship between the Artist and Product table (1 artist can have many products).

You can also see that the Product Table is linked to the Sales Table because there is a line from the Product Code in the Product Table and the Product Code in the Sales Table.

If you don’t see a line between the common fields in each table, then the tables are not joined. Your queries will most likely run, but they will not yield the correct results. You can correct this problem by simply pointing to the common field in one of the tables, holding down the left mouse button and dragging to the same field in the other table. Access will add the line, and the tables will be joined together.

Referential Integrity and Table Relationships

There are times when you want to establish a permanent relationship between two tables in your database instead of simply establishing a relationship for the purpose of a query. One reason to do this is to establish a referential integrity check between two tables. Referential integrity edits the value of the fields the tables are joined to make sure they are valid. Let’s say that you have a referential integrity relationship established between the Artist and Product table based on artist code. When you create a new product record, and you enter an artist code of LC01, it checks to make sure there is an artist code of LC01 in the Artist table.

To establish a relationship:

· Database Tools – Relationships.

· When the Show Table window appears, add each of the tables you want to establish a relationship for.

· While holding your left mouse button down, drag the cursor from the field in one table to the matching field in the other table.

· The Edit Relationships table will display.

· Select Referential Integrity.

· Click Create.

To edit the relationship:

· Right click on the middle line between the two tables and select Edit Relationships.

· Make your selection from the Edit Relationships window.

· Click OK.

When you finish creating or editing the relationship,

· Click on the X in the upper right hand corner of the table window to close the table.

Working With Queries

Queries add a great deal of functionality to Access. You can use a query to view, change, and analyze data in a variety of ways.

You can use the Query Wizard to help you create a query. This is sometimes the easiest approach, but it also limits your flexibility. Additionally, you can use the design query function to design a query from scratch.

Select Queries

The most common form of query is a select query, where you use the query capability of Access to select certain records from your database. You can print the records you selected, or you can use the query as a source of records for other forms, reports, and data access pages.

To create a new select query,

· Create – Query Design

· In the Show Table window, click on the Tables you want in your query and click Add. Repeat this for every table you want to include in your query.

· After you select all the tables you want in the query, click Close.

· In the first column, click on Field Name. Use the down arrow to select the field you want.

· Repeat this for each column (field) you want to include in your query.

· In the sort row, select the fields you want to sort on, and use the right down arrow to select whether you want to sort in ascending or descending sequence.

· If you do not want to show the field in your output, unclick the show row for that field.

· If you only want to select certain records, use the criteria field to enter your selection criteria. If you are selecting text data, enter the value you are searching for.

You can also change your existing queries. After you open the query in the design view, the commands are the same.

To change a select query,

· Create – Query Design

· In the Show Table window, click on the Queries tab you want in your query and click Add.

· Make the appropriate changes.

· Click on the X in the upper right hand corner of the query window to close the window.

· Click on Yes to save your changes.

You can create queries to select certain records based on what you need to see. For example, you can select only the records where a field in the table has a certain value.

To select certain records,

· Enter a formula in the criteria row for the field. For example, >7 will limit the report to records with unit costs greater than $7.

· If you want to select records that have a certain text value, such as a certain set of codes, you can enter the code you are looking for in the criteria field. For example, if you enter Jazz, Access will return all the records that have Jazz in the field you are matching against.

A couple things to watch out for here … you don’t have to enter the quotes around the text, but Access will automatically put the quotes around the text. You do have to enter the correct operator. This can be confusing, and if you don’t think about it, it will cause you to select the wrong records. Let’s say you are looking for all the records with Jazz or Folk. You might think it is logical to type Jazz and Folk in your criteria, but it won’t work. Whenever you include the word “and”, Access assumes that both conditions must be true before it selects the record. Since each record only contains one of the codes, there will never be a time when both conditions are true. The proper selection criteria are Jazz or Folk, so Access will select the records that contain either one of these values.

Unique Values Option

The unique values option allows you to print the values of certain fields in your table only once. For example, suppose you have 20 records that contain 5 different style codes and you want to find out which style codes are used. Setting the unique values option will tell Access to only select one record for each value.

To set the unique values option,

· Right click on the Criteria field in the design area.

· Select Properties from the pop up window.

· Click on Unique Values and select Yes from the criteria line on the right hand side of the screen.

· Click the X in the upper right hand corner of the Query Properties window to close the window and save your change.

· Note: You can only print the one field from the table.

Calculating a Value with Field Formulas

You can create queries that calculate values for new fields. You can create a new field that contains the total of the two other fields.

To calculate a new field:

· Go to a new column, for a new field.

· Right click on the field row.

· Select Build … to start the Expression Builder.

· In the Expression Builder window:

· Select the table(s) that contains the fields you want to work with.

· Select the first field from the list of fields and click on paste.

· Click on the appropriate operation sign.

· Select the second field from the list of fields and click on paste.

· Click OK.

· Change the word Expr1 to whatever you want to call the total, for example Total or Profit.

Update Queries

You can use an update query to change the value of different fields in your database.

To create an Update Query:

· Create – Query Design

· Add the table you want to update to the query.

· Click on the Update button in the toolbar.

· For the field you want to change, type the value you want in the field in the Update To line.

· To execute the update, press the Run (red exclamation point) icon, 4 icons to the left of the Update icon.

Delete Queries

You can use the delete query function to delete records in your database based on the value of certain fields in your database. It is a really nice powerful feature, but it can also make a mess of things if you make a mistake and end up deleting the wrong records. Use this feature carefully.

To make a query a Delete Query:

· Create – Query Design

· Add the table you want to delete records from to the query.

· Click on the Delete button in the toolbar.

· In the column of the field you want to base your deletion on, go to the delete row and choose where or from. Where means that you want to delete any record where the value of this field is equal to the deletion criteria. From means that you want to delete any record where the value of this field is greater than or equal to the value in the criteria field.

· Type the value of the field you want to match against in the Criteria field.

· To execute the deletion, press the Run (red exclamation point) icon, 6 icons to the left of the Delete icon.

Total Queries

You can use total queries to see totals of different groups of data.

To make a query a Total Query:

· Create – Query Design

· Add the table(s) that contains the fields you want to total.

· Click on the Totals icon at the right hand side of the ribbon. The words “Group By” will appear in each row in the Total column.

· In each numeric column, use the down arrow to change the word “Group By” to some other word, such as “sum” or “average”, depending on the operation you want to perform.

· For each column you want to group by, leave the words “Group By” in place.

· In all the other columns, delete the words from the total row.

· Close the query and open it again in the datasheet view.

Working with Forms

A form permits you to add, change, or display one record at a time. It is less confusing for users to deal with one record at a time, and to only see the record they are dealing with. Access provides a form wizard to help you quickly create forms. Of course, you can bypass the forms wizard and create the form yourself.

To create a form for a table:

· Select the table you want to maintain with the form from the All Tables column.

· Create – Form

· Select the Form View to see the form.

· Select the Layout View to see the layout of the form.

· Select the Design View to maintain the design of the form.

· Click on the X in the upper right hand corner of the table window to close the form.

· When the window displays, enter the name of the form and click OK.

Although it is not required for this class, you can go into the design window and manually change the layout of your form. You have a lot of freedom when you do this because you can change almost anything, but it can also be very frustrating because you don’t always end up with things looking the way you want them to.

To change an existing form:

· Select the form you want to change from the All Tables column,

· Design – View – Design View

· Make the appropriate changes to the form.

When you are finished creating or changing your form, make sure you save it.

To save the changes to your form:

· Click on the X in the upper right hand corner of the form window to close the window

· Click on Yes to save your changes.

One of the things that is different about a form is that you only see one record at a time. You can use the arrows at the bottom of the screen to advance forward and backward through the records in the table. If you want to go to the end of the table to enter a new record, press the star key. If you want to jump to a specific record, enter the record number you are looking for.

Working with Reports

After you have your database up and running and data loaded into it, you may want to create a report. If you are designing a data base from scratch, it is a good idea to think about what you want to print on the reports so you make sure you have the data you need in the data base. Otherwise, you end up utilizing some of your Access skills to make design changes which can be cumbersome and time consuming.

To create a new report:

· Create – Reports – Report Wizard

· In the Report Wizard window,

· Click on the down arrow in the Tables/Queries field to select your table.

· Highlight the field you want on the form and click on >, or click on >> to select all fields.

· Click on Next.

· Select any report groupings that you want. Groupings are really neat. They provide a way for you to group the records in your report in a logical sequence. For example, you may want to group all the records for a specific department together.

· Click on Next.

· Select the order you want the report sorted in. In the first field, select the most important field from your database, in the second field; select the second most important field in your database.

· Click on Next.

· Select the report layout and orientation that you want.

· Click on Next.

· Select the report style that you want.

· Click on Next.

· Type the title of the report, for example, High Cost.

· Click on Finish.

· Access will display a copy of the report, at least the first page of it. You will probably need to modify the report to make it look right.

To modify the report layout:

· Right click on the Report from the All Tables column on the left hand column and choose Design view.

· Make the appropriate changes.

After you finish adding or changing your report, save your changes.

To close the window and save your reports:

· Click on the X in the upper right hand corner of the report window to close the window.

· Click on Yes to save your changes.