1Tutorial 3Maintaining and Querying a Database2Finding Data in a TableMust navigate to a record before view/delete/change itFind is a great way to navigate to a recordBut... Find can’t:show matching records as a subset (it jumps from record to record)find records based on multiple field values3Updating a DatabaseA database is useful only when it’s contents are accurate and up-to-dateMaintaining a database involvesinserting new recordsupdating existing recordsfirst navigate to the record, then edit its field valuesNavigation mode vs. Editing mode[F2] to toggle between modesdeleting recordsnavigate to the record, click Record Selector | Delete button | Yesconfirmation dialog box…generally can’t Undo a record deletionissue: if referential integrity is set, can’t delete a record if it has matching child rows in a related table… unless the Cascade Delete option is setmanually deleted child rows using the subdatasheet, then deleted parent row2Practice TimeOpen the Writer table and enter one of your friends as a new writer.Use Find to navigate to your own record, then change your Last Contact to today’s date and your Reprint Payment Amount to $222.22.Delete the newly entered record for your friend.Try to delete Valerie Hall’s record. Do you understand why you receive an error message?45Displaying Related Records in a SubdatasheetSubdatasheetwhen tables have a defined One-to-Many relationship, the primary (one) table can display sub-datasheets showing records from the child (many) tableview/insert/update/delete related records stored in another tableFigure 3-5 page AC 123Access automatically creates a subdatasheet for the One table in a One-to-One or One-to-Many relationshipsteps to change what the subdatasheet displays1.Design View2.Open Table’s Properties sheet3.Subdatasheet Name property6Introduction to QueriesSelect Query (other types of queries in Tutorials 5 & 9)used to view, analyze and edit data from 1 or more tablesAccess selects and displays the records meeting your criteriaWith Select queries, you can:choose which fields to see − place desired fields onto gridchoose which records to see − specify criteriasort records more flexiblyperform calculationspull data from multiple related tablesmake changes to data in tablesuse a query as source of data for forms, reports or another query37Query Views(another in Tutorial 10)Design Viewcreate/modify query’s structurespecify which fields to display, which records to display, how to sort, perform calculationsDatasheet Viewview the set of records that answers the question you posedgenerally also able to add/edit/delete records 8The Query Design WindowQuery By Exampleto specify fields, record selection criteria, sorting, table joinsField Listmethods of placing fields onto the grid:drag each field separately (or double-click)when you want a different order than fields appear in the tabledouble-click Field List title bar, then click and drag one onto gridplaces every field in a column, in order they appear in the tabledouble-click the * in the Field Listplaces * in a single column, representing all fieldsis fast… but can’t use sort or record selection criteriaShow check boxpurpose?vs. deleting the field from the grid9Creating and Running a QueryCan create via Query Wizard or Design Viewbuttonexecutes the query, then displays query’s results in Datasheet ViewUpdating Data Using a QuerySelect Queries generally produce a dynaseta dynamic view of a set of recordscan insert/update/delete rows in a dynaset and the changes are stored in the underlying tablePractice TimeCreate a query that shows each article’s title, type, issue date, and length. Save as qryArticles when working correctly.410Creating a Multi-Table QueryThe resulting query results can have fields from any of the joined tablesPractice Timecreate a query that shows each article’s title, type, and the author’s first name, last name and phone. Save asqryArticlesAndWriters when working correctly.Cautions with multi-table querieswhen a row in one table doesn’t match any rows in the other, it isn’t shown in the query results (Inner Join, lib)don’t include unnecessary tablesdon’t include tables that lack a join lineCartesian Product (nib)11Sorting Data in a QueryBy default records are displayed in primary key sequenceQuery results may be easier to interpret if sorted by another field(s)Sorting does not physically rearrange the order the records stored on disk, it only affects how the records are displayed in the query resultsIndexes can speed up sort operations(Tutorial 9)12Sorting Data in a QuerySorting Data in the Query Results DatasheetA-Z Z-A sort options available via AutoFilter list at top of columnthis technique can sort based on only a single columnSorting in Query Design Viewclick in Sort row, then select Ascending or DescendingAccess allows up to 10 different sort fieldseach can be either Ascending or Descendingunique vs. non-unique sort fieldsleftmost sort field is primary sort, sort field to right is secondary…may need to drag fields to new locations on the gridDemo:Modify qryArticlesAndWriters to sort by type.Modify to sort primarily by type and secondarily by length, with longest articles on top.513Practice Time1.Modify qryArticlesAndWriters to sort by only last name.2.Modify qryArticlesAndWriters to sort primarily by type, and secondarily by title (i.e., sorted by title within type). Save as qryArticlesAndWriters when working correctly.3.Create a query that shows each writer’s last name, first name, and freelancer status (not necessarily in that order), sorted primarily by freelancer status, secondarily by last name, then by first name. Save as qryWritersByFreelancer when working correctly.14Filtering DataFiltershows a subset of records in an open datasheet or formtemporarily shows a subset of recordsFilter By Selectionfilters records based on a selected field value (or portion thereof)steps:1) open the datasheet (or form)2) navigate to a record containing desired data3) select the field that serves as the value to match4) click Selection button list arrowin Navigation ButtonsToggle Filter buttonclick to revert to
View Full Document