190 likes | 229 Views
DB Implementation: MS Access Queries & Reports. Outline. Access Queries Query Creation Sorting & Filtering Query Types Dynamic Query Access Reports Report Sections Grouping & Filtering. MS Access File for Lecture. MS Access Queries. Database Queries Core DBA skill
E N D
DB Implementation:MS Access Queries & Reports L546 Session 7, IU-SLIS
Outline • Access Queries • Query Creation • Sorting & Filtering • Query Types • Dynamic Query • Access Reports • Report Sections • Grouping & Filtering MS Access File for Lecture L546 Session 7, IU-SLIS
MS Access Queries • Database Queries • Core DBA skill • From SQL to Query by Example (QBE) • What does it do? • Find target information • Retrieve, Filter, Sort, Aggregate/Summarize • Manipulate data • Perform calculations • Add, Change, Delete, Combine data in tables • Assemble/Supply data for forms and reports • How does it work? • Access translates QBE to SQL • SQL performs data manipulations based on Relational Algebra • Access queries create a dynaset (“live” view of table) • changes made in data by query is reflected in underlying tables L546 Session 7, IU-SLIS
Access Queries: Views • Datasheet view • For displaying the result of the query • Useful for reviewing/validating the query • Design View • For creating/modifying a query using drag & drop GUI (i.e., QBE) • Consists of Diagram Pane & Grid Pane • Add tables/queries to the Diagram Pane • Add fields to the Grid Pane (Field row) • Can sort/filter/compute by fields • Sort row: set to Ascending/Descending • Criteria row: use Expression to apply data filter • Total row: compute (sum, min, max, count, etc.) of each field • Automatically generates SQL statements • SQL View • For creating/modifying a query by manually writing SQL statements • Only way to create SQL-specific queries • Union/Data-definition/Pass-through query L546 Session 7, IU-SLIS
Access Queries: Basic Types • Simple Query • Uses one table/query • To generate a subset (row/column) of a table • Multi-table Query • Joins multiple tables/queries • To merge small chunks of data in normalized tables • Linked tables are automatically linked in in the Query Design Grid • Creating a link in the Query Design Grid does not permanently link tables • Select Query • Selects records that meet given criteria • Does not change the data • Parameter Query • Prompts for query criteria values (parameters) to run a dynamic query L546 Session 7, IU-SLIS
Access Queries: Sort & Filter • Datasheet View • Sorting • Click column and right-click • Filtering • Filter Tool (Home tab) • Filter by Selection • Filter by Form • Advanced Filter/Sort • Design View • Sort using the Sort Row • Sort priority is from left to right for multiple sort • Filter using the Criteria Row • Criteria in multiple rows make OR query • Criteria in single row make AND query • Criteria in a single cell • AND/OR • Wildcards (*) in Like and Between • e.g. Like “A*”, Like “[A-C]*”, Like “*av*”, Like “ave?”, Not Like “A*” • e.g. Between 1950 and 1960, >1960 L546 Session 7, IU-SLIS
Access Queries: Query Criteria • Query Criteria Expressions L546 Session 7, IU-SLIS
Access Queries: Expressions • Using expressions to create a calculated column • Enter expressions in blank column of query design view • NAME: [Field1] operator [Field2] • Format the display in Format property of the field • Examples • Simple math • DiscountPrice: [Discount] * [StandardPrice] • String Concatenation • Name: [FirstName] & “ ” & [Lastname] • Date & Time math • DateDiff(Interval, BeginDate, EndDate) • HireAge: DateDiff(“yyyy”,[BirthDate],[HireDate]) • DateAdd(Interval, Number, Date) • RetireDate: DateAdd(“yyyy”,25,[HireDate]) • Customized Sorting • Switch(expr1, value1, expr2, value2, etc.) • Switch([City]=“Seatle”, 1, [City]=“Redmond”, 2, etc.) L546 Session 7, IU-SLIS
Access Queries: Dynamic Criteria • Parameterized Queries • Dynamic query based on varying criteria value • e.g. retrieve books written by a given author • Enter the parameter name in square brackets in criteria • Do not use existing field names • Can use expressions • e.g. Like "*" & [Last Name] & "*“ • Query Criteria from Form Entries • Use a form to enter query parameter values • good for multiple parameter entries • Create a parameter query • Create a form with unbound input control for each of query parameters • Add a command button that will run the parameter query • Run Query Action of Miscellaneous Category • Reference the form controls that hold parameter values from the query • i.e. change the criteria to form controls • Parameter name = fully qualified name of form control L546 Session 7, IU-SLIS
Access Queries: Joins • Inner Join • Default join in Access (i.e. Natural Join) • Returns only the records where joined fields are equal in both tables • Left Outer join • Returns all records from the left table • Right Outer join • Returns all records from the right table L546 Session 7, IU-SLIS
Access Queries: Advanced Types • Action Queries • Append Query • Appends table rows to an existing table • Delete Query • Deletes table rows • Update Query • Modifies the values of particular fields for particular records • Make Table Query • Creates a new table from rows of other tables/queries • will overwrite existing table of the same name • Crosstab Query • Performs mathematical operations on intersection of two fields • SQL-Specific Queries • Data-definition Query • Defines/Changes the definition of a database object (e.g., create/modify a table) • Union Query • Combines multiple SELECT queries • Pass-through Query • Send command directly to ODBC database server to run server-side SQL L546 Session 7, IU-SLIS
Access Queries: Action Queries • Append Query Appends table rows to an existing table • Create a select query. • Convert the select query to an Append query • Append Tool in Query Type group of Design tab • Choose the destination fields for each column in the append query. • Run the query to append rows/records. • Delete Query Deletes table rows • Create a select query. • Convert the select query to a Delete query. • Delete Tool in Query Type group of Design tab • Run the query to delete rows/records. • Update Query Modifies the values of particular fields for particular records • Create a select query. • Convert the select query to an Update query. • Update Tool in Query Type group of Design tab • Set Update To: (and Criteria when appropriate) rows • Run the query to update fields/columns. • Make Table Query Creates a new table from rows of other tables/queries • Create a select query. • Convert the select query to a Make Table query. • Make Table Tool in Query Type group of Design tab • Run the query to update fields/columns. L546 Session 7, IU-SLIS
Access Queries: Crosstab Query • Crosstab Query Performs mathematical operations on intersection of two fields • Using the Crosstab Query Wizard • Create a select query to join tables (if needed). • Click Query Wizard in the Other group of the Create tab. • Select Crosstab Query Wizard in the New Query dialog box & click OK. • Choose table/query with which to create a crosstab query & click Next. • Choose the field(s) whose values will be used as row headings & click Next. • Choose the field whose values will be used as column headings & click Next. • Choose a field and a function to use to calculate summary values. • The data type of the field that you select determines which functions are available. • Using the Crosstab Tool • Create a select query. • Convert the select query to a Crosstab query • Crosstab Tool in Query Type group of Design tab • Set Row Headings • Crosstab = Row Heading, Total = Group By, Sum, Avg, etc. • Set Column Headings • Crosstab = Column Heading, Total = Group By • Set the calculation field and function • Crosstab = Value, Total = Sum, Avg, etc. • Set Criteria to filter data (if needed). • Total = Where, Criteria = expression L546 Session 7, IU-SLIS
Access Queries: SQL Queries • Data-definition Query Defines/Changes the definition of a database object • Create a table • CREATE TABLEtable_name( field1 type(size), field2 type(size), … , PRIMARY KEY(field)) • Modify a table • ALTER TABLEtable_nameADD/ALTER COLUMNfield type(size)DROP COLUMNfield • Union Query Combines multiple SELECT queries • Select queries must have the same fields (i.e., Union compatible) • SELECT field1, field2, …. FROM table1UNIONSELECT fieldA, fieldB, …. FROM table2 • Create the select queries in Design view • Copy & paste SQL statements into a union query • Pass-through Query Send command directly to ODBC database server to run server-side SQL • Configure the server DBMS as an ODBC data source • Create a Pass-through query L546 Session 7, IU-SLIS
MS Access Reports • Access Reports • Allows presentation of table and query data in a customized layout • Good for data summarization • Not a tool for data manipulation • Optimized for printing rather than screen display • Report Views • Design View • Provides a detailed view of report structure • e.g., headers/footers for the report, page, groups • Layout View • Useful for modifying appearance and readability of the report • e.g., setting column widths, adding grouping levels • Print Preview • How the report will look when printed • Displays page breaks, report columns, etc. • Report View • For basic viewing the report • Can copy data L546 Session 7, IU-SLIS
Access Reports: Creating Reports • Using the Report Tool • Select the source (table/query) of the report in the Navigation pane. • Click the Report tool in the Reports group of the Create tab. • Using the Report Wizard • Click the ReportWizard in the Reports group of the Create tab. • Follow directions on the Report Wizard pages. • Using the BlankReport Tool • Click the Blank Report tool in the Reports group of the Create tab. • Drag fields from the Field List pane to the report L546 Session 7, IU-SLIS
Access Reports: Sections • Report Header • Appears once at the beginning of the report (before the page header) • Use for “Cover Page” information • e.g., report author, company logo, date • Page Header • Appears at the top of every report page • e.g., report title • Group Header • Appears at the beginning of each group • Use for group name • e.g., company name when grouped by company • Detail • Main body of the report (table/query data area) • Group Footer • Appears at the end of each group • Use for summary information for a group • Page Footer • Appears at the bottom of every page • e.g. page numbers, dates • Report Footer • Appears once at the end of the report • e.g. report totals L546 Session 7, IU-SLIS
Access Reports: Grouping & Filtering • Creating a grouping Level • Access adds a header & footer corresponding to grouping field’s name • e.g. grouped by Category Category Header, Category Footer • Option in Report Wizard • Manual group creation • Click Group & Sort tool in Grouping & Totals group of Format/Design tab (Layout/Design view) • Click Add a group in the Group, Sort, and Total pane. • Select a field to group by • Click More to set additional grouping options • Filtering Records • Using Select Query • Set filtering conditions in query criteria • Using Filter Properties • set Filter and Filter On properties (Data tab) of a report • Dynamic Criteria • Use Parameter Queries L546 Session 7, IU-SLIS
Access Reports: Counting & Summing • Using the Layout View • Open a report in Layout View • Select the field you want to count • Click Totals icon in Groupings & Totals group of the Format tab • Select an appropriate summary function • Right-click the summary fields and click Set Caption • Using the Design View • Open a report in Design View • Place a Text Box in appropriate section of the report • Set the Control Source property of the text box to be an expression using a summary function • Summary counts: =COUNT([Field_NAME]) • Sums: =SUM([Field_NAME]) • Set the Running Sum property if running total is desired L546 Session 7, IU-SLIS