Introduction to Data Entry Queries and Reports CSC

  • Slides: 57
Download presentation
Introduction to Data Entry, Queries and Reports CSC 240 (Blum) 1

Introduction to Data Entry, Queries and Reports CSC 240 (Blum) 1

Recall that our database had four tables: Customer, Item, Order and Item. In. Order

Recall that our database had four tables: Customer, Item, Order and Item. In. Order CSC 240 (Blum) 2

Lookup Wizard • Recall that we used the Lookup Wizard to establish relationships between

Lookup Wizard • Recall that we used the Lookup Wizard to establish relationships between tables, for example the Order table has a field corresponding to the Customer. ID. • When entering an order, a person will be presented with a drop-down list of customers. • There is an implication here that the customer data precedes the order data. Similarly the order data and item data precede the item-in-order data. • The logic/structure of our database design implies a logic/order to data entry. CSC 240 (Blum) 3

Data Entry Dependence Customer Item Customer must precede Order and Item must precede Item.

Data Entry Dependence Customer Item Customer must precede Order and Item must precede Item. In. Order CSC 240 (Blum) 4

Double click on the Customer table to open it up in Data. Sheet view.

Double click on the Customer table to open it up in Data. Sheet view. CSC 240 (Blum) 5

Enter some customer data CSC 240 (Blum) 6

Enter some customer data CSC 240 (Blum) 6

What one can do in Data. Sheet view • In Data. Sheet view, one

What one can do in Data. Sheet view • In Data. Sheet view, one can look at the data, add new data, remove any of the data and change any of the existing fields. • With more sophisticated database, one will separate out these possibilities and assign different users different permissions. One will specify whether a user can SELECT, INSERT, DELETE and UPDATE particular data. CSC 240 (Blum) 7

Open the Item table in Data. Sheet view and enter some data. CSC 240

Open the Item table in Data. Sheet view and enter some data. CSC 240 (Blum) 8

Open up the Order table CSC 240 (Blum) 9

Open up the Order table CSC 240 (Blum) 9

It’s not all or nothing • In order to start associating items with an

It’s not all or nothing • In order to start associating items with an order in the Item. In. Order table we must first have the corresponding Order record. • But that does not mean we must know the final value for every field in the Order record. – Clearly we will not immediately know the Order. Date. Shipped. CSC 240 (Blum) 10

Go to the View drop-down selector and choose Design View CSC 240 (Blum) 11

Go to the View drop-down selector and choose Design View CSC 240 (Blum) 11

Make sure the Order. Date. Shipped is not required. CSC 240 (Blum) 12

Make sure the Order. Date. Shipped is not required. CSC 240 (Blum) 12

When one clicks in the Customer. ID field a drop-down arrow appears on the

When one clicks in the Customer. ID field a drop-down arrow appears on the right. Use it to lookup the Customer. Recall we designed it to show customer names even though we are really looking up customer IDs. CSC 240 (Blum) 13

Strictly speaking it is looking up Customer. ID, but the user is expected to

Strictly speaking it is looking up Customer. ID, but the user is expected to supply customer names. If one types something that is not a valid customer last name and hit Enter, an error message results. CSC 240 (Blum) 14

Even if one enters a valid customer ID, it does not accept it –

Even if one enters a valid customer ID, it does not accept it – since we told Access the user would be using customer names here, that is what it expects. (If one types a valid last name, it accepts it. ) CSC 240 (Blum) 15

Enter some Order records. CSC 240 (Blum) 16

Enter some Order records. CSC 240 (Blum) 16

Open the Item. In. Order table in Data. Sheet view and enter some data.

Open the Item. In. Order table in Data. Sheet view and enter some data. CSC 240 (Blum) 17

If one tries to enter a duplicate item within the same order, an error

If one tries to enter a duplicate item within the same order, an error message arises because the combination of Order and Item serves as a primary key and thus must be unique for each record. CSC 240 (Blum) 18

No escape • After entering an invalid record – If there are other records

No escape • After entering an invalid record – If there are other records to enter, there is no problem. – If there are not, one will have started a record that is invalid and trying to leave it will produce error messages and give the data entry person a sense that the cannot escape from this problem. – Closing the table will produce some error messages but will produce the desired result – all of the previously entered valid records will be saved and the invalid record will not. CSC 240 (Blum) 19

Saving occurs more often than in other Office applications and without the user explicitly

Saving occurs more often than in other Office applications and without the user explicitly selecting Save. CSC 240 (Blum) 20

Select the Queries object and double click on Create query in Design view CSC

Select the Queries object and double click on Create query in Design view CSC 240 (Blum) 21

Introduction to Querying • The approach we will be taking here is known as

Introduction to Querying • The approach we will be taking here is known as Query-by-Example. The language for querying is SQL, so we could also write a SQL statement. • The first step is to settle on a question to ask. – Let us start with something simple such as what are our customer’s names. • The next step is to add the tables necessary to answer the question. – This query involves only the customer table. CSC 240 (Blum) 22

Select the Customer table and click Add to make it part of the query.

Select the Customer table and click Add to make it part of the query. CSC 240 (Blum) 23

Next drag the fields involved in the query into the grid below. CSC 240

Next drag the fields involved in the query into the grid below. CSC 240 (Blum) 24

Click on the drop-down list in the View button and select Data. Sheet View.

Click on the drop-down list in the View button and select Data. Sheet View. CSC 240 (Blum) 25

Data. Sheet view shows the results of the query. This query might be called

Data. Sheet view shows the results of the query. This query might be called a projection – it “projects” out the fields we want to see. CSC 240 (Blum) 26

Click on the drop-down list in the View button and select SQL View. CSC

Click on the drop-down list in the View button and select SQL View. CSC 240 (Blum) 27

SQL View shows a SQL statement that would yield the results of the Query-by-Example.

SQL View shows a SQL statement that would yield the results of the Query-by-Example. CSC 240 (Blum) 28

One can use the drop-down box in the Sort field under the Customer. Last.

One can use the drop-down box in the Sort field under the Customer. Last. Name to choose Ascending to place the names in alphabetical order by last name. CSC 240 (Blum) 29

Data. Sheet View of Alphabetized results CSC 240 (Blum) 30

Data. Sheet View of Alphabetized results CSC 240 (Blum) 30

SQL View of Alphabetized results CSC 240 (Blum) 31

SQL View of Alphabetized results CSC 240 (Blum) 31

When one closes the query, one is prompted to save it, give it a

When one closes the query, one is prompted to save it, give it a name, it then appears in one’s list of queries. CSC 240 (Blum) 32

Next design a query that shows only customers from PA. Add the Customer. State

Next design a query that shows only customers from PA. Add the Customer. State field to the query and add a condition to the corresponding Criteria field. Such a query can be called a “selection” – as it selects out records that satisfy some condition. CSC 240 (Blum) 33

Results of PA Selection CSC 240 (Blum) 34

Results of PA Selection CSC 240 (Blum) 34

Uncheck the show checkbox under state so that it is involved in the query

Uncheck the show checkbox under state so that it is involved in the query but not displayed in the results. CSC 240 (Blum) 35

Results of PA Selection without state showing. CSC 240 (Blum) 36

Results of PA Selection without state showing. CSC 240 (Blum) 36

SQL View of state selection query. CSC 240 (Blum) 37

SQL View of state selection query. CSC 240 (Blum) 37

Change the criteria field to a phrase in square brackets to allow the user

Change the criteria field to a phrase in square brackets to allow the user of the query to enter a state during the query’s execution. CSC 240 (Blum) 38

Executing the query now gives rise to an input box for the user to

Executing the query now gives rise to an input box for the user to enter a state. Such a query is said to be “parametrized” or “parameterized. ” CSC 240 (Blum) 39

Results of parameterized state query. CSC 240 (Blum) 40

Results of parameterized state query. CSC 240 (Blum) 40

SQL View of Parameterized state query. CSC 240 (Blum) 41

SQL View of Parameterized state query. CSC 240 (Blum) 41

Save the parameterized query CSC 240 (Blum) 42

Save the parameterized query CSC 240 (Blum) 42

The Join • Next create a query that lists the orders and the names

The Join • Next create a query that lists the orders and the names and phone numbers of those who have placed those orders. • This query requires information from two tables: Customer and Order. • The tables are said to be “joined. ” – More on what that actually means another time, let us just see the results on it here. CSC 240 (Blum) 43

Add Customer and Order to the query, then add the desired fields to the

Add Customer and Order to the query, then add the desired fields to the grid, choose to sort by Ascending on the Order. ID. CSC 240 (Blum) 44

Customer-Order join results in Data. Sheet View CSC 240 (Blum) 45

Customer-Order join results in Data. Sheet View CSC 240 (Blum) 45

SQL View of Customer-Order join CSC 240 (Blum) 46

SQL View of Customer-Order join CSC 240 (Blum) 46

Save the Customer-Order query CSC 240 (Blum) 47

Save the Customer-Order query CSC 240 (Blum) 47

Building a Customer-Items query • Next we want a list of the customers, their

Building a Customer-Items query • Next we want a list of the customers, their phone numbers and the items they have ordered. • This involves all four tables – since the relationship of customer is to order, and the relationship of order to item is realized through the Item. In. Order table. CSC 240 (Blum) 48

Add the tables, drag the desired fields to the grid, etc. CSC 240 (Blum)

Add the tables, drag the desired fields to the grid, etc. CSC 240 (Blum) 49

Customer-Item query in Data. Sheet View CSC 240 (Blum) 50

Customer-Item query in Data. Sheet View CSC 240 (Blum) 50

Customer-Item query in SQL View CSC 240 (Blum) 51

Customer-Item query in SQL View CSC 240 (Blum) 51

Save Customer. Items query. CSC 240 (Blum) 52

Save Customer. Items query. CSC 240 (Blum) 52

Recall query • Suppose we had to recall an item. We would want a

Recall query • Suppose we had to recall an item. We would want a list of customers who had bought a particular item. • One decision in such a query is whether we select on the item’s name or item’s ID. – Let us choose here to go with ID. One might want to make accompanying queries that will either provide the user with a list of item names and Ids or a parameterized query allowing the user to supply the name and obtain the corresponding ID. CSC 240 (Blum) 53

Design of parameterized recall query. CSC 240 (Blum) 54

Design of parameterized recall query. CSC 240 (Blum) 54

Data. Sheet View of parameterized recall query. Recall that we told Access to show

Data. Sheet View of parameterized recall query. Recall that we told Access to show us the Item name instead of the item. ID in the Item. In. Order table. CSC 240 (Blum) 55

SQL View of parameterized recall query. CSC 240 (Blum) 56

SQL View of parameterized recall query. CSC 240 (Blum) 56

Save the parameterized query. CSC 240 (Blum) 57

Save the parameterized query. CSC 240 (Blum) 57