School of Computer Sciences Western Illinois University

IS 524, Assign#2 Amaravadi

Note that this assignment requires you to know database concepts prior to doing it. Download ‘insurance.accdb’ from the course web site and open it in Access, make sure to ‘enable’ the content. The database already has the product table. You will be creating the Creating the client and payments tables, entering data, doing queries and creating a report.

DATA DEFINITION

1. We will define the tables in the following steps. Go into “Create” tab and select “table design”. You are presented with a blank table as below.

2. This has id as primary key. Close this table and then select ‘Create’ and ‘Table Design’ again from the main menu. You are presented with another form that looks like the previous one. Using the attributes below, define the data for the Client table by typing in the attributes and their data types. Define the primary key by highlighting the key and selecting the primary key icon from the “design’ menu. Close the table and save as ‘Client’. Repeat the same process for the payments table. You need to hold down the shift key when defining multiple primary keys. The product table is already available.

Client

Product

Payments

You should see all three tables on the left side of the screen.

CHECK RELATIONSHIPS

3. You need to check if the tables are linked together. Go into “Database tools” and “relationships” from the main menu. It should show the tables linked by cross-reference keys. If they are not linked, “add” all three tables and link them by dragging and dropping primary keys (see the diagram below). Close and save.

DATA ENTRY FORMS

4. We will create data entry forms for the ‘client’ and ‘payment’ tables and enter data through the forms. For the ‘product’ table data will be entered directly into the table. Select ‘client’ table first and go into create” and “forms design” from the menu on top. You are presented with a blank form on which ‘controls’ will be placed. See the menu in the figure below. You will create the title first and then add fields:

a. Select the label “Aa” from the form menu and create a label with the mouse. Give it a title (see below).

b. Then add fields using “Add existing fields” tab as shown in the diagram below. You need to drag fields in.

c. When you are done adding fields, select rectangle icon from menu (shown in diagram above) and draw a rectangle around the fields (this is not illustrated).

d. Select rectangle that you drew and right click mouse button.

e. Fill the rectangle with suitable color. The fields are not seen now.

f. Go to “Arrange” and select “send to back”. Fields should be seen now.

g. Save the form as “Client data entry.”

5. We will create combo boxes for ‘gender’ and ‘state of residence’ to speed up data entry. Here the steps for gender are spelled out, but you have to create a combo box for ‘state_of_residence’ on your own.

a. First you need to select the gender label and field and delete these from the form.

b. Next, select ‘combo box’ option from the menu above and draw a box underneath the ‘cname’ field.

c. Then you are asked if you want to type values or should it look up values (see first figure below). You will select the former.

d. Next you can type in the combo box values -- see second figure below.

e. Next it will ask you the name of the attribute to store the selected value – it has to be the ‘gender’—select it.

f. Next give a name for the label – this will also be ‘gender’.

6. Using similar steps create a combo box for the state_of_residence. Type in the values seen below:

When you are done, your client data entry form should look like the form below:

7. Create a data entry form for ‘payments’ table, following similar steps. This is left as a challenge for you but note that both client# and product# are combo boxes. When you define these you need to select ‘I want to look up values’ rather than typing them in. For the client#, you will be looking it up from the client table. The product# is looked up from the product table. Also when you are selecting the fields select both ‘client#’ and ‘cname’ fields so that the cname is displayed when entering data (but the data being entered is client# and goes into the client# field). It will ask whether to hide the primary key but do select the primary key to be displayed. Similarly when you are selecting fields, you need to select both product# and product type. You are left on your own for doing this. When you are done, the payment data entry form should look like this:

Note that you do not have to do anything for the product table since the table already has the data.

DATA ENTRY

8. Now you can enter data. From list of objects on left, open up the “client data entry” form by double clicking. Using this form and information below, add records. The first client record is added in your name with the rest of the data in the record same except for gender. When you are done, open up the “payments data entry” form and enter the data from the ‘payments’ table below;

CLIENT TABLE

PRODUCT TABLE

PAYMENTS TABLE

QUERYING

4. We will do three queries. To access the query interface, Go into “create” and “query design” from the main menu. You can select the table and fields as shown below. Note that you must not use the QBE interface.

“Add” appropriate table(s) table (depending upon the query it could be one or all of “Client,” “Payments,” or “Product”). While in the query menu, select “view” and “sql view.” If you have more than one table, Microsoft adds a “Join” in the “From” part which you should delete. You should type queries only as discussed in class/notes.

Now you can start typing your queries, but remember:

Use square brackets for attributes that have the “#” sign e.g. [cust#]
In order to get data from multiple tables you need to link the values of the common attribute or common field in the Where clause as in table1.linkattr1 = table2.linkattr2, where linkattr is the common attribute you are linking. In such cases, you need to precede all attributes by the table name as in Select ord.[ord#].
Multiple conditions in the where clause are connected by an “AND” or “OR” as necessary.
You don’t need to use any special operators for dates. However, use “#” to delimit dates as in “….Where Ord.[Ord_dt] = #2/20/15#…”
ACCESS expects “;” as the delimiter for the query (i.e. the end-of-query marker).

6. Queries need to follow format as discussed in class/notes.

a) create an SQL query to list selected client details. The query is

Select [client#], cname, state_of_residence, phone

From client;

The answer you should get is shown below. Copy your query and query result into a word document. To copy the result table use the selector to the left of the query title. Copy and paste the result into the word document. Once in the Word document, select the table using the table-selector icon and format the left margins by moving the ruler guides at the top of your word document so that the table is properly indented as shown here.

b) Type an SQL query list client#, client names, payment amount and date paid. Copy and paste both the query and your answer into the word document. This is left for you to figure out.

c) List client#, client name, product type, amount paid and date paid. Copy and paste the query and result into your word document.

REPORTING

5. We will create a report involving all three tables. Go into “Create” and “Report Design”. You will be presented with a blank screen with PH, Detail and PF. Use the right mouse button to insert Report Header and Footer. We will first create title, then PH, then detail.

a) Select the “Aa” icon and using the mouse draw a label. Type the report heading. (Note that the icons are under the “design” tab).

b) Using the line icon, draw a line above and below the heading (see below).

c) For the page header, create a label or labels that contain the page headers (see second row in the report spec below) and draw a line. You can double click on the line and change its property so as to have dashes.

d) For the detail line select the “add existing fields” icon from report menu and add the fields. Delete the label for each field. You may have to modify properties for certain fields. Simply double click on field to open up properties.

e) Also the fields in the detail line have borders. You need to select each field, right click to show properties and change the “border style” property to “transparent”.

f) For the report footer, copy and paste the line from the RH. Beneath the footer, there is a text box for the date and a label box for your name. The textbox has the value “=Date()” as shown below. The label box simply uses “Aa” icon to create the label. Use your name where it says “Created by ….” Create these and save the report. Your report should be more or less identical to the one shown below except for your name.

g) You need to select and italicize the name and date

h) With the report in view, you can go to the Windows icon in the top left corner and select “save as” and save it to a PDF file – if this is not possible, you can always print out separate sheets and staple them together.

i) Insert the PDF file into your word document so that it has queries in the first part and report in the later part. Print and submit the file.

The report should be more or less identical to that shown below, except for your name and date and shading in between created automatically by ACCESS.

SUBMISSION & GRADING

You should submit a document that contain(s):

1) Three SQL queries (that you typed in) -- SQL statements should be typed in by you. You should not be using the queries given by ACCESS.

2) Results of running the queries copied and pasted from Access. Each result should follow each SQL statement.

3) The listing of the report as shown above (the name and date will be different).

Above is order in which they should be stapled. Please do not submit the Access database file. Queries 1 & 2 are worth 2.5 points each, query 3 is worth 5 points, the report is worth 10 points. Grading will be based on conformance to requirements. For e.g. if the words are cut off, -1 point. There are also penalties for not following submission requirements – if there is a problem for you please let me know.

COMMON QUERY PROBLEMS

· Is there a semi-colon after the end of query? There should be one.

· Is there a space between client and #? No space.

· Is client# enclosed in square brackets? [client#]

· Are the names of attributes used in the query matching with those that are defined?

· Are you using attributes in the ‘select’ part that are listed in the tables, in the ‘From’ part?

· Are primary keys identified correctly?

· Is your data typed accurately?

IF YOUR REPORT DOES NOT WORK

1) go to "home" tab and "database tools" and check and make sure your relationships are fine. Also check if you defined pkeys correctly. Correct any problems and run the report again.
2) If there are still problems, you need to delete all the fields in your report (attributes).

3) Go into report properties and erase the record source.
3) save and exit the report shell
4) next go into queries and create a query using QBE -- use the report in the assignment to identify which attributes you need
5) save it as clients_pmts_products
6) next open your report in design view and find the report properties button (double click black box top left corner or right click and open report properties)
7) select clients_pmts_products as your record source.
8) proceed to build the rest of the report as usual.