Second monthly course

1. Second monthly course MS ACCESS Office Management Tools II Ms Saima Gul

2. DisplayingSelected Datawith Queries MS ACCESS Office Management Tools II Ms Saima Gul

3. What is a query? • A Microsoft Access query is a question that you ask about the information stored in your Access tables. • Figure on slide 7 is a typical Query Design window. • After you create and run a query, Microsoft Access will retrieve and display the set of records you asked for in a datasheet. This set of records is called a dynaset, which is the set of records selected by a query.

4. How dynasets work • Access takes the records that result from a query and displays them in a datasheet, in which the actual records are called a dynaset. • Physically, a dynaset looks like a table; in fact, it is not a table. The dynaset is a dynamic (or virtual) set of records. This dynamic set of records is not stored in the database. • When you run a query, Access places the resultant records in the dynaset. When you save the query, the information is not saved; only the structure of the query is saved—the tables, fields, sort order, record limitations, query type, and so forth. • Consider these benefits of not saving the dynaset to a physical table: • A smaller amount of space on a storage device (usually a hard disk) is needed. • The query uses updated versions of any records changed since the query was last run. • Every time the query is executed, it reads the underlying tables and re-creates the dynaset.

5. Creating a query • After you create your tables and place data in them, you are ready to work with queries. To begin a query, follow these steps: • From the Database window, click the Queries Objects button. • Double click the ‘Create Query in Design View’ option. The Show Table dialog box in the Query Design window

6. Selecting a table • The Show Table dialog box displays all tables and queries in your database. • You can add the Student table to the query design with these steps: • Select the Student table from the Show Table dialog box. • Click the Add button to add the Student table to the Query Design window. Or you can double-click the table name instead of pressing the Add button. • Click the Close button. • While in Query Design mode, you can activate the Show Table dialog box to add more tables at any time; select Query➪Show Table or click the Show Table button (picture of table with plus sign). • When you want to delete a table from the Table/Query pane (top pane of the Query Design window), click the table name in the query/table entry pane and either click Delete or select Query➪Remove Table.

7. The Query Design window with the student table in the upper pane and the bottom pane currently empty.

8. Using the Query window • The Query window has two main views, the Design View and the Datasheet View. • The Design View is where you create the query, and the Datasheet View is where you display the query’s dynaset. • The Query Design window is currently in the Design View; it consists of two panes: • The table entry pane • The Query by Example (QBE) design pane (also called the QBE grid) • The table/query entry pane, the upper pane, is where tables and/or queries and their design structures are displayed. • The visual representation of the table is a small window inside the table/query entry pane. It shows the table name in the title bar of this small window and displays all the fields in the list box of the window. • The Query by Example (QBE) pane, the lower pane, is used for holding the field names that will be displayed and any criteria that will be used by the query. • Each column in the QBE design pane contains information about a single field from a table or query in the upper pane.

9. Using the QBE pane of the Query Design window • The Query Design pane (QBE grid), which has six named rows: • Field.This row is where field names are entered or added. • Table.This row shows the table the field is from (useful in queries with multiple tables). • Sort.This row enables you to enter sort directives for the query. • Show.This check box determines whether to display the field in the resulting dynaset. • Criteria.This row is where you enter the first line of criteria to limit the record selection. • Or.This row is the first of a number of rows to which you can add multiple values to be used in criteria selection.

10. Adding a single field • You can add a single field in several ways. • One method is to double-click the field name in the field list (also called a table window); the field name will immediately appear in the first available column in the QEB pane. • You can also add a field graphically to the QEB pane. Highlight the field name in the table window in the table entry pane, click it, drag it, and drop it in the desired column of the QBE Design pane. • Another method is to select the field you want from the drop-down list that appears when you click the down arrow button in the Field: cell of the QBE pane. • To run the query, click the Datasheet button on the toolbar (the first icon from the left). When you are finished, click the Design button on the toolbar (the first one on the left) to return to design mode. • You can also run the query by clicking the Run icon on your toolbar with the exclamation point on it, or by selecting Query➪Run.

11. Selecting fields

12. Adding multiple fields • You can add more than one field at a time by selecting the fields you want to place in the query and then dragging and dropping the selection in the QBE pane. The selected fields do not have to be contiguous (one after the other). • To add multiple fields, follow these steps: • Remove any existing fields in the QBE pane by selecting Edit➪Clear Grid from the menu. • Highlight in the table entry pane the first field name that you want to add. • Hold the Shift/Control key down and click the other fields that you want to select. • Click the selected fields and drag the Multiple Field icon, which appears as you move the mouse. The icon appears as a group of three field icons. • Drop the Multiple Field icon in the desired column of the QBE Design pane.

13. Dragging all fields as a group • To select all the fields of a table, perform these steps: • Remove any existing fields in the QBE pane by selecting Edit➪Clear Grid from the menu. • Double-click the title bar of the table to select all the fields. • Point to any of the selected fields with the mouse. • Drag the Multiple Field icon to the QBE pane. • This method fills in each column of the QBE pane automatically. All the fields are added to the QBE pane from left to right, based on their field order in the Student table. • You can also select all the fields by clicking on the * in the table window in the table entry pane. The QBE shows ‘table name.*’ which means that all fields have been selected.

14. Selecting all fields

15. Removing a field • You can remove a field from the QBE Design pane. • Select the field or fields to be deleted in the QBE Design pane, and then press Delete or select Edit➪Delete. Changing the field display name • To make the query datasheet easier to read, you can rename the fields in your query. The new names become the tag headings in the datasheet of the query. • To rename pId to Program ID, follow these steps: • Click to the left of the ‘p’ of pId in the Field: row of the QBE Design pane. • Type Program ID and a colon (:) between the new name and the old field name.

16. Changing the field display name Table entry pane QBE pane    

17. Displaying Only Selected Records Understanding record criteria • Record criteria are simply some rule or rules that you supply for Access to follow. • These criteria tell Access which records you want to look at in the dynaset. • A typical criterion could be “all Students,” or “only those students not living in Peshawar,” or “students enrolled in program having ID 4.” • In other words, with record criteria, you create limiting filters to tell Access which records to find and which to leave out of the dynaset. • You specify criteria starting in the Criteria: property row of the QBE pane. Here you designate criteria with an expression. The expression can be simple example data or can take the form of complex expressions using predefined functions.

18. Understanding record criteria (contd.) • As an example of a simple data criterion using the Student table, you could type “Peshawar” in the Criteria: cell of Address and the datasheet displays only records for Peshawar. Result of query

19. Entering other simple criteria • You can also specify criteria for Numeric, Date, and Yes/No fields. Simply enter the example data in the criteria field. • It is also possible to add more than one criteria to a query. For example, suppose that you want to look only at records from the Student table for students who are enrolled both in Pid 3 and 4, and where these students have DoB in year 1990 (where the value of DoB is greater or equal to January 1, 1990). • Create a new query starting with the Student table. • Add the fields name, address, Pid, and DoB to the QBE grid. • Click the Criteria: cell in the Pid column in the QBE Design pane. Type 3 here. • Click the Or: cell in the Pid column in the QBE Design pane. Type 4 here. • Click the Criteria: cell in the DoB column in the QBE Design pane. Type >= 01/01/1990 in the cell. (also in or cell) • Click the Datasheet button.

20. Entering other simple criteria    

21. Result of the query

22. Adding More than One Table to a Query • After you create the tables for your database and decide how the tables are related to one another, you are ready to begin creating multiple-table queries to obtain information from several tables at the same time. • In the query design view, click on the ‘show table’ button on the tool bar to add more tables to the table/query entry pane. • Next slide shows the top pane of the Query Design window with additional tables added. Because the relationships were set at table level, the join lines are automatically added to the query.

23. Adding More than One Table to a Query

24. Understanding Types of Table Joins • To view data in two tables, they must be joined through a link that is established via a common field (or group of fields) between the two tables. • The method of linking the tables is known as joining. • In a query, tables with established relationships are shown already joined. • Within a query, you can create new joins or change existing joins; just as there are different types of relationships, there are different types of joins. • The different types of joins are: • Equi-joins (inner joins) • Outer joins • Self-joins

25. Inner joins (Equi-joins) • The default join in Access is known as an inner join or equi-join. • It tells Access to select all records from both tables that have the same value in the fields that are joined. • Thus, an inner join between tables is simply a join where records are selected when matching values exist in the joined field of both tables. • Unlike inner joins (equi-joins), outer joins are used for showing all records in one table while showing common records in the other. • The table or query that does not have a matching record will simply display empty cells in the datasheet when the dynaset is displayed. Creating outer join

26. Student Table data Program Table data

27. Double clicking the join line

28. query

29. Inner join result

30. left outer join result right outer join result

31. Calculated fieldoriginal table

32. Calculated field     

33. Calculated fieldresult

34. Comparison search condition • In the following slides, only the QBE section is shown for the ‘basic info’ table added in the table entry pane.    

35. Set membership • It also has a negated version in which we write ‘Not In(‘Peshawar’, ‘Kohat’), which works the opposite.    

36. Pattern matching (like/not like) • There are three special pattern matching symbols: • ? – A single character (0-9, Aa-Zz) • * - Any number of characters (0 to n) • # - Any single digit (0 – 9) • For example: • Address LIKE ‘P*’ means the first character must be P, but the rest of the string can be anything. • Address LIKE ‘S???’ means that there must be exactly 4 characters in the string, the first of which must be an S. • Address LIKE ‘*r’ means any sequence of characters, of length at least 1, with the last character an r. • Address LIKE ‘*sh*’ means a sequence of characters of any length containing sh. • Address NOT LIKE ‘P*’ means the first character cannot be a P.

37. Pattern matching • It also has a negated version in which we write ‘Not Like P*’ which works the opposite.    

38. SELECT Statement - Aggregates • There are five aggregate functions: COUNT returns number of values in specified column. SUM returns sum of values in specified column. AVG returns average of values in specified column. MIN returns smallest value in specified column. MAX returns largest value in specified column. • Each operates on a single column of a table and returns a single value. • COUNT, MIN, and MAX apply to numeric and non-numeric fields, but SUM and AVG may be used on numeric fields only. • Examples: You just need to write following in the first column of QBE pane. • Count Result: count([pId]) • Sum Result: sum([Salary]) • Avg Result: avg([Salary]) • Min Result: min([Obtained Marks]) • Max result: max([Obtained Marks])

39. Exploring Microsoft project MS project Office Management Tools II Ms Saima Gul

40. What is a Project? • A Project is a job that has a beginning and an end (time), a specified outcome ( scope) at a stated level of quality (performance), and a budget (cost). • A project is a temporary effort to create a unique product or service. Projects usually include constraints and risks regarding cost, schedule or performance outcome. Project Management • Project management is a set of principles, practices, and techniques applied to lead project teams and control project schedule, cost, and performance risks to result in delighted customers. • Efficient use of resources to complete a project as designed, on time, at the desired level of performance, and within budget. • These project parameters are also called constraints. • Examples of different kinds of projects are: • Design and construction of new building • Working on Final Year Project • Developing a new product or service etc.

41. Starting MS Project • When you open Microsoft Project from the Programs folder of the Windows Start menu, Project initially displays the main screen for Project 2007, as shown on next slide. • On the left, you see the Project Guide pane. The Project Guide pane helps users set up and work with a project. • You also can open Project by double-clicking any Project file. Project files are saved with the extension .mpp.

42. Starting Project (Contd.)

43. Working with the Project Guide • The Project Guide is a goal-based user interface that helps you build projects. • In addition to the Project Guide pane on the left side of the screen, you also can display the Project Guide toolbar, which appears just above the Project Guide pane. • To display the Project Guide toolbar, right-click anywhere in the toolbar area at the top of the screen and choose Project Guide (or Tools -> Options -> Interface Tab) (see next slide). • Using the buttons on the Project Guide toolbar, you can limit the choices that appear in the Project Guide pane. • At this point, you can use the Project Guide toolbar and the Project Guide pane to begin building your project. • Click a button on the Project Guide toolbar to start working in the associated area. • The choices listed in the Project Guide pane change, based on the Project Guide toolbar button that you click.

44. The buttons on the Project Guide toolbar

45. Working with the Project Guide (Contd.) • When you click a link in the Project Guide, a wizard starts and walks you through the process that’s suggested by the link. • For example, if you click the Tasks button on the Project Guide toolbar and then click the Define the project link, a three-step wizard walks you through starting a project. • The first step helps you to establish the starting date for your project. After setting the date, click the right arrow at the top of the pane or click Save and go to Step 2 at the bottom of the Project Guide pane to continue. • In Step 2 of the Define the Project Wizard, you identify whether you intend to use Project Server. • In Step 3, you return to the Project Guide.

46. Examining the Gantt Chart view • By default, Project opens a new project in the Gantt Chart view. • The Gantt Chart view has two main sections: the Gantt table and the Gantt Chart. • After you enter task information, the Gantt table (in the left pane) holds columns of information about your project, such as the task name, duration, start date, and more. • The Gantt Chart (in the right pane) is a graphic representation that helps you see the timing and relationships among tasks. • The timescale along the top of the Gantt Chart acts like a horizontal calendar. Project enables you to display up to three timescales along the top of the Gantt Chart—a top, middle, and bottom timescale (Format->time scale). • On the next slide, you see two timescales. The top timescale shows months; the bottom timescale shows weeks. • Multiple timescales help you to see the multiple levels of timing simultaneously, such as the day and hour or the month, week, and day.

47. Entering Information • Several views or portions of views in Project, such as the Gantt table, use a familiar spreadsheet style interface. Information appears in columns and rows. The intersection of a column and a row is a cell, just as in Excel. • Project assigns each task in your project an ID number, which corresponds to the task’s row number running along the left of the spreadsheet. • You can enter project information either in dialog boxes or directly into cells. • When you select a cell, the Entry bar, which appears immediately above the column names of the table, displays the information in the cell.

48. Gathering Information • When you start working on a project, first, you must understand the overall goal and scope of the project so that you can clearly see the steps that lie between you and that goal. • Don’t worry about the order of the tasks at this point—just brainstorm all the major areas of activity. • Suppose that you’ve been given the project of organizing an annual meeting for your company. You may take the following steps: • Book the meeting space • Schedule speakers • Arrange for audiovisual equipment • Order food • Send out invitations • Mail out annual reports

49. Determining detail tasks • After you have prepared a list of major tasks, break them into more detailed tasks. • Take one of the items on the list—Order food, for example—and consider how you can break down this task. How detailed should you get? • The following is one possible breakdown of the Order food task: • Create a budget. • Determine a menu. • Select a caterer. • Send out requests for bids. • Receive all estimates. • Review estimates and award contract. • Give final head count to caterer. • Confirm menu one week before the meeting

50. Establishing time limits • After you have an idea of the tasks that are involved in your project, you need to have some idea of the timing of these tasks. • Should you allow two weeks for caterers to reply with bids? Not if you have only three weeks to organize the meeting. • You may want to approach determining task timing by building an initial schedule in Project, assigning time to tasks, and seeing how close you can come to your deadline. If you’re way off, you can go back and tweak the timing for individual tasks until your schedule works. Lining up your resources • Before you can build a Project schedule, you need to know what resources are available to you, as well as their costs. • You don’t necessarily need to know these resources by name, but you should know, for example, that your construction project needs three engineers at a cost of $75 per hour and one piece of earthmoving equipment at a daily rental cost of $450. • You need to identify these resources and assign them to individual tasks early in the project-planning process.