Lab 4 - Database 2.pdf

UNIVERSITI KUALA LUMPUR KAMPUS KOTA MALAYSIAN INSTITUTE OF INFORMATION TECHNOLOGY Name of Course PRINCIPLE OF IT Course Code ITD 12103 Lecturers Semester / Year Date Assessment Lab Activity – Microsoft Access Bas...

UNIVERSITI KUALA LUMPUR KAMPUS KOTA MALAYSIAN INSTITUTE OF INFORMATION TECHNOLOGY Name of Course PRINCIPLE OF IT Course Code ITD 12103 Lecturers Semester / Year Date Assessment Lab Activity – Microsoft Access Basic Database Tutorial Instruction: Open Microsoft Access and follow the basic step database using Microsoft Access Tutorial that taken from http://www.quackit.com/microsoft_access/microsoft_access_2013/tutorial/. The details below are the steps to create basic database using Microsoft Access 2013. 1. About Microsoft Access 2. Create a Database 3. Create a Table 4. Adding Data 5. Create a Form 6. Modify a Form 7. Create a Query 8. Modify a Query 9. SQL View 10. Create a Macro 11. Export Access Database to Excel 1. Create the Database The first step to creating a database is to, well, create the database! We will create a blank database first. Then throughout the rest of the tutorial, we'll make additions to it so that it suits the needs of our favorite space travel company. Creating a database in Microsoft Access is as easy as creating a Word document! This lesson demonstrates how to create a database in MS Access. 1. You may notice that, when you first start Microsoft Access 2013, you see the following screen. In this case, you can simply selectBlank desktop database (then skip to Step 3). If you already have Access open, you can go to the File menu: 2. Choose Blank desktop database. (Skip this step if you already chose Blank desktop database at step one). You also have other options, but we'll just use a blank database here:  3. Choose a name and click Create. Let's call it SpaceTrips. You can either use the default location or click the folder icon to change the location: Your New Database Once you've completed the above steps, you should see a blank database, like this: We know this database is blank because it only contains one table (called Table1) and that table only has one column (called ID). So we need to add some more columns to the table and give the table a name. We can do that next when we Create a Table. 2. Create a Table With database management systems, you need to create your tables before you can enter data. Microsoft Access makes creating tables extremely easy. In fact, when you create a database, Access creates your first table for you (and calls it Table1). Normally when you need to create a new table, you'll select CREATE > Table from the Ribbon toolbar. But we can do that later. For now Access has already created our first table, so all we need to do is modify that table so that it suits our needs. Using our blank database, we are going to rename Table1 to Customers. This table will have 4 columns: CustomerId, FirstName,LastName, and DateCreated. 1. Rename the ID field to CustomerId. To do this, Right-click on the ID column and select Rename Field. Enter CustomerId when prompted:  2. On the next field, click on Click to Add (unless Access has already opened the menu for you) and select Short Text: 3. At this point, Access will conveniently highlight/select the field name (currently Field1) so that you can name the field. Call itFirstName: 4. Do the same again for the next field (LastName) and select data type Short Text. 5. In the next field, select the Date & Time data type and name the field DateCreated What we just did was create the column names and specify the type of data that can be entered into them. Restricting the data type for each column is very important and helps maintain data integrity. It can ensure that the user enters the data in the intended format. It can also guard against accidents like for example, inadvertently entering an email address into a field for storing the current date. Your database table should now look like this: You might notice that now when you click on the field names, you can't see or change their data types. Instead, if you click on the inverted triangle within a field, you see a different menu of options (eg, Sort Oldest to Newest etc) depending on the field's data type. This shows that the field is ready for data - once you have data, you can sort it using this menu. Don't worry if you accidentally entered the wrong data type. You can change the dataype in the Ribbon toolbar by adjusting the data type under the Data Type dropdown (top right area of the toolbar). If you can't see this option in the toolbar be sure to select theFIELDS tab first: We have just created a database table. We did this in Datasheet view. Next we will see how it appears in Design view. 3. Design View What Is Design View? Design view enables you to design and set up your database (kind of a "behind the scenes" view of your database). This is where you set up and configure your tables, forms, reports, etc. As you continue working with MS Access, you will find yourself switching between Design view and Datasheet view (or in the case of forms, Layout view) often. So it's good to familiarize yourself with this concept early on. Switch to Design View The following steps demonstrate how to switch to Design view. 1. Click on the View icon at the top left (just under the File menu). The View icon currently looks like this: 2. At this point, Access will ask you to name and save the table you just created. Type Customers and click OK: 3. You are now looking at your Customers table in Design view. Click on the DateCreated field, then in the bottom pane, enter=Now() in the Default Value field. Also  click in the Format field and select General Date from the contextual menu: We have just set a default value for the DateCreated field. This means that, whenever a new record is entered, this field will automatically be populated with the current date and time. We have also specified a format for all dates that are stored in this field. You can see that we have configured just two of many options in this pane. These options can be used to further specify exactly what type of data can be entered into the database as well as any constraints or defaults that you'd like to set against each field. Some options include restricting the length of data (under Field Size), setting a default value (which we've just done), specifying whether it is a required field, and more. In practice, you will probably want to tighten some of these rules down - like specifying whether a field is a required field or not (i.e. can't be left blank). But for the purposes of this tutorial, we won't get too fussy. You'll also see that the Ribbon now has a DESIGN tab with a new set of design-related options. Primary Key You might have noticed the little "key" icon to the left of the CustomerId field. This indicates that the field is a primary key. A primary key ensures that the data in this column is unique - no two values can be the same. This is important for when you want to perform queries or run reports against the table, and it is vital for establishing relationships with other tables. Expression Builder You can also click on the little... button that appears whenever you click in the Default Value field (and other fields too). Clicking on this button opens the Expression Builder, which enables you to browse for a suitable function, constant, or operator to assist you in building an expression for the field. We could just have easily used the Expression Builder to build our =Now() expression: Switch Back to Datasheet View Now that we're in Design view, you might have noticed that the View icon looks different - it has changed from a Design view icon into a Datasheet view icon. It now looks like this: Click the View icon to return to Datasheet view, and we'll look at the options for adding data to our table. 4. Design View In Microsoft Access, there are many ways to add data to your database. Which method you choose will depend largely on how much data you need to add and whether the data already exists outside of Access. Here's an explanation of the main methods of adding data to a database. Datasheet View You can type directly into the table while it's in Datasheet view. While this is fine for smaller tables with a small number of records that are rarely updated, it's not suitable if you plan to maintain a lot of records. Maintaining even a small database can become a time-consuming task if records change frequently - especially if you need to update data that's spread across multiple tables. It's also not really suitable if there will be non-technical users maintaining the database. For non-technical users, it's better to create a nice user-friendly form. Form You can set up a form, so that non-technical users can enter data into the form. Once they submit the form, the data is automatically inserted into the database. One of the great things about forms is that they can insert into many tables - saving you from having to open up each table to manually insert the data.Access provides a form wizard, which steps you through the process to building a form. We'll cover this in the next lesson. SQL View You can use SQL view to insert data into your database. You can also use SQL view to do many other things with your database (including creating tables). SQL (which stands for Structured Query Language) is the standard programming language for working with databases. To select SQL View from the Ribbon, select CREATE > Query Design, (close the popup table - called Show Table), then select SQL View. External Data There may be times when you need to load your database with data that already exists. For example, you might have data in an Excel spreadsheet or even a.csv file that you want to transfer to an Access database. This can be done! Access 2013 allows you to import data from the following file types:  Microsoft Excel  Microsoft Access   ODBC Databases, such as SQL Server  Text or comma-separated value (CSV) files  SharePoint List  XML  Data Services  HTML Document  Outlook folder To import data from any of these file types, select EXTERNAL DATA from the Ribbon, select the file type, then follow the prompts: You can also use this menu to link to various file types (as opposed to import them). Doing this will enable you to provide up to date data from external databases or files. This option does carry some restrictions depending on the file type. Access 2013 can link to the following file types:  Microsoft Excel (read-only)  Microsoft Access  ODBC Databases, such as SQL Server  Text or comma-separated value (CSV) files (add new records only)  SharePoint List  Data Services (read-only)  HTML Document  Outlook folder For now, we will create a form to enter data into our Customers table. 5. Create a Form As mentioned, Access forms provide non-technical users the ability to add data to your database. Forms also make it easier to insert data across multiple tables. There are several methods to create a form (as you can see by the following screenshot): Options include using the Form Wizard, Design view, and even starting with a blank form. The option you choose will depend on your own preference and perhaps the type of form that you want to create. The Navigation option is for creating a form that allows the user to navigate database objects such as other forms. The Form Wizard option steps you through the process of creating a form. If you use the Form Wizard, you can always modify the form later using Design view. For the purposes of this tutorial, we'll create our form as quickly as possible. Then we'll go back and modify it later. Here's the quickest way to create a form in Access. 1. Ensuring that the Customers table is selected (in the left navigation pane), from the Ribbon, select CREATE > Form 2. Access has just created a brand new form in Layout view, based on the fields in the Customers table. Click on the inverted triangle under the View icon and select Form View: 3. This is how the user will see the form. Go ahead and enter data into the FirstName and LastName fields. Be sure to hit Enterafter you've entered the last name. Then hit enter again to add another record.  4. Now let's check our table. Click Customers in the left navigation, then click Refresh All on the Ribbon. You should now see your newly created record in the table: You might notice that the DateCreated field is full of hashes symbols ######. This is because the column is currently too narrow to display the data. You can resize the column to fit the data by double clicking at the side of the column header. First, move the cursor to the edge of the column header until it changes appearance into a resize tool, then double click. The column should now be wide enough to display the data: Now let's switch back to the form and save it. 1. Click on the second tab called Customers (the first one is for the Customers table), then click the Save icon in the top left (above the Ribbon):  2. Rename your form Customers Form when prompted: The form will now appear in the left navigation column under the heading Forms. Next, we will switch to Design view and modify our form. 6. Modify a Form So we've just created a form. While the form works as expected (i.e. it enters data into the database table), it could certainly be improved upon. Here, we'll give the form a fresh new look and we'll also prevent users from modifying the DateCreated field. Although we've been using the Views icon on the Ribbon to switch views, you can also switch views by using the icons at the bottom right of Access (screenshot below). Design View 1. Click the Design view icon at the bottom right corner of Access (you can also use the icon at the top left of the Ribbon if you prefer): 2. Click on the DateCreated field, then click Property Sheet. The Property Sheet will appear to the right of the form: 3. On the Property Sheet, scroll down until you see the Locked option. Change its value to Yes using the drop down control. We have just "locked" the DateCreated field so that users can't change it. This field is intended to store the date that each record was first entered into the system. Therefore its  value should never change. By locking the field users can't inadvertently (or even maliciously) change it. We can still display the field, for the users' information, but we don't want them changing it. 4. Next, select the CustomerId label (the label is the one on the left - don't touch the ones on the right), and add a space so that it becomes Customer Id. Do the same for the other fields. This just makes the form a bit more readable and more presentable, as the labels are what the user sees. It should look like this: 5. Now close the Property Sheet and switch to Layout view. Layout View It's time to give our form a new look. 1. In Layout view, click in a blank part of the form (i.e. away from the fields). This should select the form detail area. 2. From the Ribbon, select a background color for the form: 3. Now feel free to format some of the other elements on the page. For example, background colors on each field, change the border color of each field, bold the labels, etc. If you want to select multiple fields, hold the Shift key down while you select them. You can even move fields around by clicking and dragging them. And you can resize the fields by clicking and dragging their edges (you can also do it via the Property Sheet). Don't forget to save the  form once you're happy with it. Here's an example of how a form can look by doing just that: Form View Now switch back to Form view and see how the form looks and works. Enter in a few records. And a few more. You can also slide the Navigation Pane back to make more room for the form (you can do this in any view). Done. Next we'll create a relationship. 7. Create a Relationship A primary feature of relational databases is that they contain multiple tables, each of which can have a relationship with any of the other tables. So far we've only created one table and it's a bit difficult trying to establish a relationship with another table when you're the only table in the database! Our database needs more than one table anyway, because we need to be able to track not only customers, but also products, as well as the products the customers actually purchase. So let's create two more tables and establish a relationship between them. One table will hold product information, the other will holdorder information. Create the Related Tables Go ahead and create two tables as follows. Be sure to set a format and default value for the DateCreated fields on both tables (the user shouldn't need to enter this field - the system can do that). Primary Key Remember that the key icon indicates that the field is a primary key. A primary key ensures that the data in this field is unique - no two values can be the same. A table must have a primary key before a relationship can be established with another table. Access automatically sets the ID field as a primary key, so as long as you simply rename ID to ProductId (orOrderId as the case may be) it will already be a primary key. If for some reason your table doesn't have a primary key set, or if you want to change the primary key field, you can simply right click on the field header (in Design view), and selectPrimary Key from the contextual menu. Establish the Relationship Now that you've created two extra tables, our database has three tables. Now it's time to create the relationship between all three tables. 1. While viewing a table in Design view, and ensuring that the DESIGN tab is selected, click Relationships from the Ribbon: 2. A Show Table dialog box will pop up, displaying all three tables. Select all of them and click Add: 3. Click Close to close the dialog box 4. You will now see three boxes which represent your three tables. Click and drag the CustomerId from the Customers table across to the corresponding CustomerId field on the Orders table: 5. The Edit Relationships dialog will pop up. Click Enforce Referential Integrity so that it is checked. Check that the values are the same as the following screenshot and click Create: You will now see a line established between the CustomerId field on the Customers table and the CustomerId on the Orderstable. 6. Now do the same for the Products table. That is, click and drag the ProductId from the Products table across to the corresponding ProductId field on the Orders table. Your table relationships should look like the ones in the following screenshot: Relationship Types We just established a many-to-many relationship. There are three types of relationships that you can establish between tables. These are as follows: Many-To-Many Relationship This is what our example above uses. A row in table A can have many matching rows in table B, and vice versa. In our case, a single customer can order many products, and a single product could have many customers. You create a many-to-many relationship by using a third table, called a junction table (more on that below). One-To-Many Relationship This is the most common relationship type. You don't need a third (junction) table for this type of relationship. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A. For example, a row in a Gender table (which contains the records Male and Female) can have many matching rows in a Customers table, but a row in the Customers table can only have one matching row in the Gender table. That is unless there was a business rule that allowed customers to be male and female at the same time. In this case, a many-to-many relationship would need to be established. One-To-One Relationship A row in table A can have only one matching row in table B, and vice versa. This is not a common relationship type, as the data in table B could just have easily been in table A. This relationship type is generally only used for security purposes, or to divide a large table, and perhaps a few other reasons. The type of relationship that you use depends on the table structure and how the fields are defined. Junction Table In the above relationship, the Orders table is known as a junction table. A junction table is one that contains common fields from two or more other tables within the same database. It is used as a reference table in a many-to-many relationship (such as we are doing in our example). Junction tables are known under many different names. Here are some: cross- reference table, bridge table, join table, map table,intersection table, linking table, many-to-many resolver, link table, pairing table, pivot table, transition table, or association table. So if you ever hear someone mention one of those, you should have some idea what they are referring to. Primary Key vs Foreign Key We already know that a primary key ensures that the data in the field is unique. This is important because our Orders table needs a unique value in order to reference any record from the other tables. For example, it can't use the FirstName field because there could be more than one person with a given first name. And it can't use the DateCreated value because it's possible that two records could be created at exactly the same time (eg, if two operators are entering data or if many records are imported from an external source). This is why we need at least one field that we know will only ever contain a unique value. A foreign key is simply the primary key's corresponding field in the related table. So in our example, the CustomerId field in the Orderstable is a foreign key, while the CustomerId field in the Customers table is a primary key. Likewise, the ProductId field in the Orderstable is a foreign key, while the ProductId field in the Products table is a primary key. The fields don't necessarily need to have the same names but it's a good practice to get used to. It will definitely make the database structure easier to understand - especially when you start adding more tables that also need to have relationships established. Relationships and Queries Creating relationships like the one above can assist in building queries against the database. Next, we're going to use our newly created relationship to help us create a query against the tables in our database. 8. Create a Query A query refers to the action of instructing the database to return some (or all) of the data in your database. In other words, you are "querying" the database for some data that matches a given criteria. The queries are run against one or more tables to return only the data that you're interested in. For example, you might like to see a list of all individuals whose last name is "Griffin". Or you might like to see a list of all users who have registered with your database over a given time period. You might also want to see which customers have ordered a particular product. To do all this, you need to perform a query. Sample Data Before we start querying the database, we should probably make sure we've got enough data in there first. Otherwise it doesn't matter how good our queries are - they will all return zero results if there's zero data. So let's add some data. Remember, the ID columns and DateCreated columns are automatically generated, so you only need to enter data into two columns in each table. Customers Table Feel free to make up your own data. Here's mine: Products Table Feel free to make up your own data. Here's mine: Orders Table Again, feel free to make up your own but you will need to make sure that the value in the CustomerId and ProductId fields match an actual ID in the respective tables. For example, if your Orders table contains a CustomerId of 5, you will need to make sure there's an actual customer in the Customer table with a CustomerId of 5. Here's some sample data for the Orders table: Referential Integrity When you create a relationship, you also have the option to Enforce Referential Integrity (which we selected when we created our relationship). This prevents foreign key values from having no corresponding primary key value in the referenced table. Using our tables as an example, let's say you have 10 records in the Customers table (with a CustomerId of 1 to 10), but your Orders table contains a record using a CustomerId of 11. In this case, referential integrity has not been maintained. If you had enforced referential integrity on the relationship (as we have), Access would prevent this from happening. Below is a screenshot of what will happen if you attempt to enter a value that would breach referential integrity. In this case I tried to enter a CustomerId of 50 and a ProductId of 50, but my Customers and Products tables don't have a CustomerId of 50 or a ProductId of 50: Note that you will only get this message if you have enabled Enforce Referential Integrity. If you hadn't enforced referenctial integrity, Access would have happily let you enter the meaningless values and the referential integrity of your database would have flown out the window. Create the Query Now let's create a query that returns the names of all customers who have ordered a product. 1. Ensuring you have the CREATE tab open on the Ribbon, click Query Design 2. The Show Table dialog box will appear with all of our tables listed. Select all three tables and click Add, then click Close: 3. The three tables are now represented in the top pane (beneath the Ribbon). Choose the fields you'd like to be presented in the results of your query. You can either double click on the field name or click and drag it down to a column in the bottom pane. Select the fields as  follows: 4. Click the Run button at the top-left part of the Ribbon:  5. You should now see the result of the query. Here's mine: 6. Save the query by right-clicking on the Query1 tab and giving it a name. Call it Customer Orders: So What Does Our Query Actually Do? Our query successfully returns a all customers who ordered at least one product and it includes the product and its price next to their name. You can also see that some customers have ordered more than one product. The query has automatically joined the three tables using the relationship that we created and returned the matching data. This query will only return those customers who have ordered at least one product. If a customer is in the Customers table but does not order a product, that customer's record will not be displayed in the results of this query. If you look back at the Customers table (near the top of this page), you'll see that Marge Simpson is a customer but she hasn't ordered any products yet (i.e. she doesn't have a corresponding record in the Orders table). Well actually, Marge is only a potential customer. She simply expressed interest in buying a space ship. But that didn't stop the pesky sales person from adding her name into the database! In any case, the query has done its job. We didn't want to see a big list of customers that haven't ordered anything yet. Next we're going to modify our query so we can look at some of the other things we can do with queries. 9. Modify a Query OK so we just created a query to return all customers in our database who have ordered a product. Now we'll see if we can tweak the query a little bit here and there to see what other results we can return. Product Name The marketing department wants to see who, if anyone, ordered their most expensive space ship. So, let's return a list of all customers who purchased the most expensive space ship - the "Venus Carrera ET". 1. From Design view, in the bottom pane, enter "Venus Carrera ET" (double quotes included) into the Criteria field of theProductName field: 2. Click the Run button in the Ribbon to view the results of the query. Alternatively, you can just click the Datasheet View icon. And here's the result: 3. Once you're satisfied with the results, return to Design view and remove your modification (i.e. remove "Venus Carrera ET"from the Criteria field). Price You could also modify your query so that it returns customers who purchased a product worth over a certain price. So let's construct a query that returns all customers who purchased any product with a price over $80,000.  1. From Design view, in the bottom pane, add (>80000) into the Criteria field of the Price field: 2. Click the Run button in the Ribbon to view the results of the query. Alternatively, you can just click the Datasheet View icon. And here are the results: Price Using Totals The previous query is all good and well, but there's one (potential) problem with it. While it certainly allows us to see all customers purchased a product over $80,000, it doesn't really paint the full picture. It doesn't display those customers who may have purchased more than one product, each of which is less than $80,000, but the total of which is more than $80,000. Here's how we can capture that one. 1. From Design view, click the Totals button in the Ribbon: 2. You will now see a Total row in the bottom pane. Each field will have Group By. In the Price field, click Group By and selectSum. 3. Click in the next row down (i.e. the Sort field) and select Descending. Keep the >80000 criteria in the Price field.  4. You will also need to delete the ProductName column by selecting it and clicking Delete Columns (next to the Totals button on the Ribbon). It should now look like this: 5. Run the query. And here are our big spenders: You could spend a lot of time tweaking this one simple query. For example, here's some ideas:  You could replace >80000 with >=80000  You could remove the >80000 altogether  You could sort by CustomerId instead  You could use >50000 And