how to extract information from a relational database using Queries object in MS Access

1. OBJECTIVE

The objective of this assignment is to help you learn how to extract information from a relational database using Queries object in MS Access. By completing this assignment, you will be able to gain DBMS skills such as (1) selecting and displaying information from a single table, (2) performing appropriate joining operations and then selecting and displaying information from multiple tables, and (3) using a number of arithmetic comparison relations and logical operators for selecting information in a query.

II.  BACKGROUND

This is a critical time in uSPiN’s growth. The ability to access information about vendors, customers, purchases, sales, and inventory will help both Jim and Linda to operate the company more effectively and efficiently. As a result, it is important to build certain queries in the database to allow Jim and Linda to extract the information they are looking for (i.e., vendor information, sales invoice information, customer information, purchase information, and inventory information).[unique_solution]

To complete this assignment you will need to use the “uSPiN-withMoreData.accdb” database file. It includes eight tables with data in them for the first couple months of uSPiN’s operations. These tables are the same as the ones you developed in Access Assignments #1 through #3, except more data has been entered to better illustrate query development.

• PARTIAL REAL DIAGRAM

Below you will find the entities and relationships, i.e., data model, for uSPiN. A complete database would consist of customer payment records, inventory purchases, etc. and would involve complete interactions between all aspects of the database. However, these few tables shown below will provide a representative exposure to relational databases and provide you with the understanding of their relationships.

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

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

Vendors (VendorID, Name, Street, City, State, Zip, Tel, Contact)

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

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

Products (ProdID, Description, Cost, SellPrice)

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

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

Customers (CustID, Name, Street, City, State, Zip, Tel, Affiliation)

Cash (CashID, DateCash, TotalAmt)

The underline indicates the table’s primary key and the brackets, [ ], designate a foreign key.  Do not include the [ ] or underline symbol in the field name when creating the tables in Access.

The primary key is the unique identifier for each item in the table. The foreign keys are derived from the relationships between the entities. The rule followed is the primary key of the “one” side of the table is placed in the table of the “many” as the foreign key.

IV. GENERAL INSTRUCTIONS FOR DEVELOPING SIMPLE AND LINKED QUERIES

Read “General Guidelines for Constructing Queries” on Page 9-11 and “To Develop Simple Queries” on Page 7 below before beginning the construction of these Simple Queries.  DO YOU KNOW WHERE YOU STORED YOUR FILE?

Simple Queries

1. List all customer names, their affiliation names, telephone number, city, and state. Sort the query alphabetically by customer name. A solution to this query is included in your database as Qry_Simple1. Below shows the instructions in constructing this query.

After you launch the database file, i.e., uSPiN-withMoreData.accdb, click on Enable Content button if you see the Security Warning message. The switchboard will be opened automatically.

1. Click on the Create tab in the Ribbon.
2. Click on the Query Design button from the Queries group of buttons.
3. A Show Table dialog displays on the screen. Select Customers table and click on the Add Click on the Close button then.

Following “General Guidelines for Constructing Queries” on Page 9-11, we will understand that we are constructing a database query to extract certain customer’s information here. Specifically, we need to list customer’s name, their agent’s name, telephone number, city and state (from Step 1—Understand the Request of Information Customers).

By looking at the tables in the database, we will see that Customers table is the table where it stores ALL of the information we are looking for (from Step 2—Identify the Sources of Information Items)

Since we don’t need information from other tables, we don’t need to worry about joining effects of tables. As a result, we can skip Step 3—Be aware of the Effects of Joining When Involving Two or More Tables.

1. Double-click on CustName (or select/click once, drag the attribute, and release it down in the Field area of the QBE grid) from the Customers You will see that MS Access places the attribute into the first column of the QBE grid displayed at the bottom of the query. Simply click on the View button in the Ribbon. You will see a query output listing all of customer names from the table following the sequence when it was entered.
2. Click on the Design View from the View button to continue the work on the query.
3. Now, add Affiliation, Tel, City, and State to the query (by double-click on them).

Following Step 4—Specify the Criteria and Apply Appropriate Aggregate Operations, we just need to list customer names alphabetically. As a result, no aggregation is needed.

1. Click in the Sort box under CustName. When the down arrow appears, click on it and select Ascending. Notice that the Show boxes for all attributes are all checked, meaning that MS Access will display their data in the output screen.

Following Step 5— Specify the Output Formats and Layout, we will make sure that we have correct output formats and layout that we want from the query outcome.

1. Click on the Save button and type Qry_Simple1.

You can take a look at the Design View of Qry_Simple1 and see how this query was setup (i.e., open the query in the Design View).

Now, you are ready to create the following queries.

2. List name, address, city, state, zip, and telephone number of all vendors (save the query as Qry_Simple2XX). XX is your initial. Sort the information by state alphabetically.

Queries submitted without your initials will NOT receive credit for the assignment.

3. List product ID, description, purchase cost, and selling price of all products. Sort alphabetically by the description. Name this query Qry_Simple3XX. XX is your initial.

4. List “club” customer names, their affiliation names, telephone number, city, and state. Sort the query alphabetically by customer name. Name this query Qry_Simple4XX. XX is your initial. There are two types of customers: schools and clubs. All clubs have a “TTC” in their customer’s name at the end. As a result, you will enter Like “*TTC” in the Criteria under the CustName column to screen for the customer’s names with anything ahead of TTC  in their names (follow Step 4—Specify the Criteria and Apply Appropriate Aggregate Operations).

Like operator is used for screening text type of data. Quotation marks are needed (i.e., “xxxxx“) to indicate about the text that you are searching for. An asterisk, i.e., *, serves as a wildcard representing any number of characters/letters.

5. List all IL Display customer names, their affiliation names, telephone number, city, and state. Sort the query alphabetically by customer name. Name this query Qry_Simple5XX (follow Step 4—Specify the Criteria and Apply Appropriate Aggregate Operations).

6. List all Butterfly Display product ID, description, cost and selling price. Sort ascending by product ID. Name this query Qry_Simple6XX.

7. List all unpaid purchases. Display purchase ID, date of purchase, vendor ID, and total purchase amount. Sort ascending by purchase ID. Use CashID for screening for unpaid purchases. Since all paid purchases will have an indication about its payment transaction in CashID. Use Is Null in the Criteria to extract records having a null value in the CashID. Do NOT show CashID. Name this query Qry_Simple7XX.

8. List all receivable from the sales. Display invoice number, date of sales, customer ID, and total invoice amount. Sort ascending by invoice number. Name this query Qry_Simple8XX.

9. List all purchases recorded in the month of January, 2012. Display purchase ID, date of purchase, vendor ID, and total purchase amount. Sort ascending by purchase ID. Use DatePur for screening for January purchases. Use >=, AND, and <= operators in the criteria. For example, enter >=#12/1/11# AND <=#12/31/11# in the criteria to extract data recorded in the month of December, 2011. Use # around a date. You can also use Between AND operators for this task. Name this query Qry_Simple9XX.

10. List sales over $4,000 recorded in the month of February, 2012. Display invoice number, date of sales, customer ID, and total invoice amount. Sort ascending by total invoice amount. Name this query qry_Simple10XX.

Read “General Guidelines for Constructing Queries” on Page 8-11 and “To Develop Linked Queries” on Page 7-8 below before beginning the construction of these Linked Table Queries.  DO YOU KNOW WHERE YOU STORED YOUR FILE?

Linked Table Queries

1. List detailed purchase items. Display purchase ID, date of purchase, vendor name, product ID, description, quantity purchased, cost, and subtotal. Sort ascending by purchase ID. Save this query as Qry_Linked1.

Looking at the data model of this database, you can find that the data items that we are looking for are stored in four tables, i.e., Vendors (VendorName), Purchases (PurID, DatePur), PurProd (QtyPur), and Products (ProdID, Description, Cost), from Step 2—Identify the Sources of Information Items.

These four tables are linked via foreign keys: Purchases → Vendors via (VendorID), Purchases → PurProd via (PurID), and PurProd → Products via (ProdID). All of them will follow the nature/inner join, i.e., a fault join in the query, from Step 3—Be aware of the Effects of Joining When Involving Two or More Tables.

1. Click on the Create tab in the Ribbon.
2. Click on the Query Design button from the Queries group of buttons.

A Show Table dialog displays on the screen. Double click on Purchases, Vendors, PurProd, and Products tables. Click on the Close button then. The query automatically links the tables according to their relationships that we have established before, from Step 3— Be aware of the Effects of Joining When Involving Two or More Tables.

1. Now, double click on the data items such as purchase ID (from Purchases table), date of purchase (from Purchases table), vendor name (from Vendors table), product ID (from Products table), description (from Products table), quantity purchased (from PurProd table), and cost (from Products table).
2. You can take a look at the outcome by clicking on the Datasheet View. Go back to the Design View when you are done viewing.
3. You need to create a calculate field, i.e., Subtotal. The subtotal is equal to the product between quantity purchased and cost, or “Subtotal: PurProd.[QtyPur]*Products.[Cost]” in the Field cell in the QBE grid.
4. Select Ascending in the Sort under the PurID
5. Click on the Save button and type

2. List detailed sales items. Display invoice number, date of sale, customer name, product ID, description, quantity sold, price, and subtotal. Sort ascending by invoice number. Name this query Qry_Linked2XX.

3. Double check on the calculations of the total purchase amount by listing purchase ID, date of purchase, vendor name, subtotal, and total amount purchased. Sort ascending by purchase ID. Name this query Qry_Linked3XX where XX represents your initials. Use Totals button to aggregate the values in subtotal (follow Step 4—Specify the Criteria and Apply Appropriate Aggregate Operations). A Total row will appear in the QBE grid. Select “Sum” for the Subtotal and any numerical-value columns and “Group By” for any text-value columns.

4. Double check on the calculations of the total sales amount by listing invoice number, date of sales, customer name, subtotal, and total amount sold. Sort ascending by invoice number. Name this query Qry_Linked4XX.

5. List total amount of purchases per vendor. Display vendor ID, vendor name, total amount of purchases in January, 2012, and February, 2012, separately. You may need two queries to accomplish this. Sort alphabetically by vendor name. Name this query Qry_Linked5aXX and Qry_Linked5bXX. Use “Where” in the Total row under the date to properly screening the aggregated data. Do Not show the date.

6. List total quantity purchased by product items. Display product ID, description, and total quantity purchased. Sort descending by total quantity purchased. Name this query Qry_Linked6XX.

7. List all product items sold. Display product ID, description, and total quantity sold. Sort descending by total quantity sold. Name this query Qry_Linked7XX.

8. Now, we are going to create an inventory report listing the ending balance of inventory. Display ProdID (from Products table), Description (from Products table), SumOfQtyPur (from Qry_Linked6XX query), SumOfQtySal (from Qry_Linked7XX query), and Balance (i.e., a calculated field, a difference between total quantity purchased and total quantity sold. Only display items with balances (i.e., do not display items with zero balance).

1. Click on the Create tab in the Ribbon.
2. Click on the Query Design button from the Queries group of buttons.
3. A Show Table dialog displays on the screen. Double click on Products Click on the Query tab and double click on Qry_Linked6XX and Qry_Linked7XX. Click on the Close button then. The query automatically links the tables through ProdID.
4. Re-arrange their position so that Qry_Linked6XX is on the left, Products in the center, and Qry_Linked7XX on the right.
5. Position your mouse on the relationship line between Qry_Linked6XX and Products.
6. While the mouse is right on top of the line, right click the mouse and click on “Join Properties”. A Join Properties dialog appears. Select Option #2 (i.e., include ALL records from ‘Products’ and only those records from ‘Qry_Linked6XX’ where the joined fields are equal)—this is an outer join, following Step 3—Be aware of the Effects of Joining When Involving Two or More Tables. Click the OK
7. Do the same for the join between Qry_Linked7XX and Products. An outer join should be setup based on the Products
8. Once the outer joins are set, we will be able to pull product item quantity purchased and sold together. Include in the QBE grid the following: ProdID (from Products table), Description (from Products table), SumOfQtyPur (from Qry_Linked6XX query), SumOfQtySal (from Qry_Linked7XX query), and Balance (type the following in a blank column next to SumOfQtySal—“Balance: [SumOfQtyPur] – [SumOfQtySal]”).
9. In the Criteria row under the Balance column, enter “ is not null”
10. Sort ascending by Product ID.
11. Name this query Qry_Linked8XX.

To Develop Simple Queries

• Start from the database window and click Create tab in the Ribbon. Click the Query Design button. Then, a Show Table dialog will appear. Identify the table(s) that you want to select data from and click Add. Close the Show Table dialog. Double click on the fields to be included in your query and click the Datasheet View from the View button or exclamation mark to run the query and display the data. The query will be saved as you exit from it. Change the name of the query and exit the query.

• When setting up criteria, there are different approaches that will work. Assume you have gender data in a table in the form of F or M. If you wanted to get all females in a query you could enter an F or “F” or “like F” in the “Criteria” row of the QBE grid. You need to be cognizant of the values that are stored in that particular data field. If instead of F or M it was originally entered in the table as female/male, you would have tried female, “female” or “like female” instead. Check out the values of the data stored before you enter a condition in the criteria.

• When setting up criteria that involve more than one attribute, putting both items on the same “Criteria” row will return all items that meet BOTH criteria. Putting one criteria on the criteria row and the next on the “or” row will return items that meet either ONE criteria.

• If you click on the “Sort” row, you will find different choices to “sort” by or “not sort”.

• You may need to change the field’s format properties (for example, to get a number to display as a percent instead of currency). To do this, put the cursor in the field name, click on the right mouse button, select Properties and then select the type of formats in the Format property of the Property Sheet.

To Develop Linked Queries

• You must make sure there is a link between the tables for your query to work. If the tables that you want to draw data from aren’t linked directly then you need to add another table that does link them. Refer to the data diagram (at the beginning of this instruction set) to determine which tables to link. Identify tables for this query until all needed tables are shown, with their relationships, and close the Show Table But DO NOT include tables that are not needed since this may adversely affect your queries.

• If you want to sum a column click on the Totals button while in the design view. This will add a “Total” row to your column in the QBE grid and then you can select Sum to add up the values in this data field (or Group By to merge/eliminate repeated values).

• New fields can be created as calculations of other fields by entering a mathematical expression in the field name such as: “Sales: Table1.[Qty]*Table2.[Price]” where the names inside the brackets [ ] represent field names and the names before the brackets and the dot the names of the tables . In this example, the query will name the column “Sales” and calculate its value by multiplying the Qty field in Table1 by the Price field in Table2. Another way to create a mathematical expression is to use the Builder. In Design View, put the cursor on the field name of a column for which you want to create an expression.  When you right click, a quick menu appears. Choose Build. Or, you can click on the Builder button in the Ribbon. An Expression Builder dialog appears. You double click on the table or query you want in the left hand column, i.e., Expression Elements, and the fields appear in the middle column, i.e., Expression Categories. Double click on the field you want and it will appear in the expression builder box. Then use the appropriate operands (*, +, >, etc.). Create the expression you want. Access will automatically call it Expr 1. You can later go back and edit the name of the field while in design view. Alternatively, you can rename Expr 1 with the desired fieldname: (e.g. Sales:) while still in expression builder. You may have to play with Expression Builder a little while to get the hang of it. SEE KNOWN ISSUES BELOW!

• Sometimes when doing calculations you will need to indicate the order the calculation should be performed in (e.g., a + b/c). To do this, use parenthesis—([a]+[b])/[c] around the part of the operation that should be done first. Otherwise, multiplication and division get a higher priority than addition and subtraction in the calculations.

Known Issues (That is what programmers call bugs!)

1. At times, expression builder inserts <<Expr>> in the box. For some unknown reasons, this usually causes the query to return an error. If this happens, simply go into the box and remove the «Expr», and the query should run normally.
2. Sometimes when you run a query, you will get back a response that says enter parameter value. That means Access doesn’t understand the attributes included in the syntax of your query and you need to go back and CAREFULLY examine them. Sometimes you can clearly see what is wrong (e.g. you had a typo of the name of the attribute), but sometimes you can’t. Be patient and search for that attribute.

General Guidelines for Constructing Queries[1]

1. Understand the Request of Information Customers

The first step involved in constructing queries is to know WHAT information is being requested. If the person who constructs the query is not the user who requests the information, he/she has to communicate clearly with the user and understand exactly the information requested. Depending on the needs of the users, different data fields may need to be included. For example, an accounts receivable (A/R) aging report would normally contain the following fields of information, in addition to the report heading: invoice date, invoice number, customer name, invoice amount, and days overdue. The report may list the information by days overdue, by invoice number, and/or by customer name depending on the preferences of the information customers (i.e., the users). The report may also cover everything to date or up to a specific date (or other criteria on different fields of information—discussed in Step 4 below). In addition, a user might request that the contact name and phone number be included on the report. Or a different user might prefer a field of information reporting the credit rating of the customer. Clearly, a wide variety of information could be provided on the report solely dependent on the information user’s preferences. As a result, the person who is constructing and executing the query, either programmer or user, must understand the exact data fields of information and criteria needed for each of the fields when constructing a query.

2. Identify the Sources of Information Items

The second step in developing queries is to identify the source tables for each of the fields recognized from the first step in the guidelines. Each field of information may be retrieved directly from one of the tables, indirectly through an operation (e.g., calculation, etc.) involving one or more tables (or generated dataset), or from the database system itself as constants (e.g., current date, a fixed rate, etc.). In the A/R aging report example, for instance, invoice date and number can be retrieved from the Invoice table and customer name from the Customer table. The invoice amount may be obtained from a calculation of quantity ordered (from the Invoice-Item table) and selling price (from the Product table). The current date used in calculating the number of days old can be provided as a system constant. Sometimes the information needed is found in a virtual (or result) table obtained from another query that becomes the source of information for the query being developed. Obviously, the construction of queries requires a detailed understanding of the table structure as well as the formulas involved in the data fields. When constructing queries, tables containing the appropriate information items needed to be identified. It is very important to make sure that unnecessary tables are not included since this may adversely affect the query results. In addition, it is important to make sure that proper relationships exist between the tables or the query results will be incorrect.

3. Be aware of the Effects of Joining When Involving Two or More Tables

From the previous steps, data fields are listed and sources of the data fields are identified. If the data fields are stored in more than one table, a join of the tables will be defined and established. A specific type of joins may be required depending on the information we want to obtain from the tables. The following are the types of joins that are commonly used.

• Natural/Inner Join

This join includes instances (records or rows) where the joining data fields from these tables are identical/equal. In our previous A/R aging report example, a natural join will be required between Invoice and Customer tables in order to retrieve the customer names from the Customer table (according to the key values from the Customer table stored in the Invoice table as a foreign key), assuming that they have Many-to-One relationship. In fact, a natural join is called upon when the joining tables have a Many-to-One relationship and the basis of information requested is from the table that has the “many” relationship in the relationship (e.g., the Invoice table in our example).

• Outer Join

This join includes all instances from one table and only those instances from the other table where the joining fields are identical. There are left and right outer joins according to the side the first table is on. Suppose that we want to obtain information about A/R subsidiary ledger account. Specifically, we want to include all customers regardless of the number of invoices (stored in the Invoice table) with which they are involved. As a result, an outer join will be performed between Invoice and Customer tables, the same tables involved in the A/R aging report example. However, in this case, the type of join required will be different since the basis of the information switches from the Invoice table to the Customer table. In other words, all of the customers in the Customer table will be included in the result regardless if they have been issued an outstanding invoice or not.

• Cartesian Product Effect

When unrelated tables (i.e., no identical/common fields existed) are included in a query, the result table will include combinations of the instances from all tables. For example, two tables, A and B, without any common fields are included in a query. A has two fields and two records (x y; 3 4 and 5 6) and B has one field with two records (z: 7 and 8). A Cartesian Product, consisting of three fields and 4 records, will be resulted (i.e., x y z: 3 4 7, 3 4 8, 5 6 7, and 5 6 8).

4. Specify the Criteria and Apply Appropriate Aggregate Operations

Based on the previous steps, all fields of information that are identified are specified in the query. However, a screening of the information based on certain values, a specific type of summarization, and/or a specific sorting method on certain fields may be required. For example, in our A/R aging report case, a screening and/or rearrangement of the information may be required on the invoice date (say, the end of last month) and only for non-zero balance invoice. It may be desirable to have an aggregation based on invoice (rather than invoice items) and customer, and to have the information sorted in a particular order (e.g., a descending sort on the days overdue). In the screening of information, there are a number of arithmetic comparison relations (i.e., =, <>, <, <=, >, >=) and logical operators (i.e., and, or, not, like, between/and) that can be used. There are also several aggregate operators that ca be used, such as Group BY, Sum, Max, Min, Where, etc. Group By is particular useful when the user wants to combine like items that are not quantities, where the Sum function would be appropriate. The information can be sorted in ascending order, descending order, or not sorted at all.

5. Specify the Output Formats and Layout

The final step involved in constructing a query is to know HOW information should be displayed on the screen and/or on the printed reports. Depending on the needs of the information customers, different formats of the reports and/or of the fields may be required. For example, the layout of the A/R aging report may vary from company to company. Formatting is also an important consideration for any new fields that have been created in the query. Decimal, percentage, currency are just a few examples of the many formats available to present the query output.

V.  TURN-IN

• To avoid points being deducted, please make sure that ALL work in the lower section of the QBE is visible before you copy and paste them. You don’t receive any points if you don’t display them completely.
• A printed copy of the queries in the DESIGN VIEW (Simple Queries 2-10 and Linked Queries 2-8). Use the “Prnt Scrn” or “prt sc” button on your keyboard and paste it onto a Word document. Please label the query number if it is not shown.
• The outputs from the above queries are shown below. Do NOT need to include the output of the query.

There are 50 records shown in this output. Above is a part of it.

There are 47 records in this output. Above shows a part of it.

[1] The guidelines are taken directly from “More on Supplemental Materials for Database Management Systems Knowledge and Skills in Accounting Information System Course” by Pillsbury, C.M.  and T.J. Wang, 2003.