Download
1 / 57
570 likes | 588 Views
Introduction to Data Entry, Queries and Reports. Recall that our database had four tables: Customer, Item, Order and ItemInOrder. Lookup Wizard.
E N D
Recall that our database had four tables: Customer, Item, Order and ItemInOrder
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 CustomerID. • 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.
Data Entry Dependence Customer Item Customer must precede Order. Order and Item must precede ItemInOrder. Order ItemInOrder
Double click on the Customer table to open it up in DataSheet view.
What one can do in DataSheet view • In DataSheet 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.
It’s not all or nothing • In order to start associating items with an order in the ItemInOrder 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 OrderDateShipped.
When one clicks in the CustomerID 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.
Strictly speaking it is looking up CustomerID, 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.
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.)
Open the ItemInOrder table in DataSheet view and enter some data.
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.
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.
Saving occurs more often than in other Office applications and without the user explicitly selecting Save.
Select the Queries object and double click on Create query in Design view
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.
Select the Customer table and click Add to make it part of the query.
Next drag the fields involved in the query into the grid below.
Click on the drop-down list in the View button and select DataSheet View.
DataSheet view shows the results of the query. This query might be called a projection – it “projects” out the fields we want to see.
Click on the drop-down list in the View button and select SQL View.
SQL View shows a SQL statement that would yield the results of the Query-by-Example.
One can use the drop-down box in the Sort field under the CustomerLastName to choose Ascending to place the names in alphabetical order by last name.
When one closes the query, one is prompted to save it, give it a name, it then appears in one’s list of queries.
Next design a query that shows only customers from PA. Add the CustomerState 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.
Uncheck the show checkbox under state so that it is involved in the query but not displayed in the results.
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.
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.”
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.
Add Customer and Order to the query, then add the desired fields to the grid, choose to sort by Ascending on the OrderID.
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 ItemInOrder table.
