Introduction to Data Entry Queries and Reports CSC
- Slides: 57
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 CSC 240 (Blum) 2
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. In. Order CSC 240 (Blum) 4
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
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 (Blum) 8
Open up the Order table CSC 240 (Blum) 9
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
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 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 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 – 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
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 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 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 selecting Save. CSC 240 (Blum) 20
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 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. CSC 240 (Blum) 23
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. CSC 240 (Blum) 25
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 240 (Blum) 27
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. 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
SQL View of Alphabetized results CSC 240 (Blum) 31
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 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
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
SQL View of state selection query. CSC 240 (Blum) 37
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 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
SQL View of Parameterized state query. CSC 240 (Blum) 41
Save the parameterized query CSC 240 (Blum) 42
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 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
SQL View of Customer-Order join CSC 240 (Blum) 46
Save the Customer-Order query CSC 240 (Blum) 47
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) 49
Customer-Item query in Data. Sheet View CSC 240 (Blum) 50
Customer-Item query in SQL View CSC 240 (Blum) 51
Save Customer. Items query. CSC 240 (Blum) 52
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
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
Save the parameterized query. CSC 240 (Blum) 57
- Csc data entry
- How do informational reports and analytical reports differ
- Definition of single entry system
- Difference between single and double entry system
- Data entry protocol
- Sql insert update delete query
- Suggestions and queries
- Generation of a new entry opportunity
- Mde data reports and analytics
- Using subqueries to solve queries
- Standing queries
- Action queries in access
- Degenerate dimensions
- Multirelation queries
- Wildcard queries in information retrieval
- Any queries
- Complex sql join queries
- Basic retrieval queries in sql
- Hotel database sql queries
- Answering my queries
- Wideworldimporters
- Sql queries for banking database
- Conjunctive queries
- Stefano grazioli
- Ingres algorithm
- Teradata ordbms
- For any queries
- Symbiosis centre for distance learning (scdl)
- Dml sql
- Any queries images
- J query
- Any queries
- Rrc completions query
- Texas railroad commission online queries
- Ssms intellisense not working
- Basic ir
- Csc102
- Reports of accenture delivery data cubes
- Introduction to data mining and data warehousing
- Single entry system introduction
- Trade payables control account
- How to write a diary entry
- How to write diary entry
- Maintenance of reports and records
- Writing and completing reports and proposals
- Input/output design
- Prefatory elements in proposal exclude
- Touch system data entry
- Udise data entry module
- Nrhm rch portal
- Pengolahan data editing coding entry cleaning
- Data entry quality control methods
- Ippe2 survey base form
- Www.idsp.nic.in data entry
- Mouse input or output
- Awaassoft data entry
- Abhwc nhp.gov.in
- Hmis clearance