Transcription

Microsoft Access Quick ReferenceFree Cheat SheetsVisit ref.customguide.comBasic SkillsKeyboard ShortcutsThe Access 2019 Program ScreenGeneralQuick Access ToolbarTitle BarClose ButtonCreate a new database. Ctrl NOpen a database. Ctrl OClose a database . Ctrl WClose Access . Alt F4RibbonPrint current view . Ctrl PHelp . F1Delete record . Ctrl Cancel changes . EscOpenDatabaseObjectNavigationPaneInsert date. Ctrl ;Insert time . Shift Ctrl ;Insert value from samefield in previous position . Ctrl ‘Spell check . F7Switch applications . Alt TabNavigationStatus BarRecord NavigationBarScroll BarView ButtonsMove between query ortable columns. , Access BasicsNext field. TabOpen a Database: Click the File tab and selectOpen, or press Ctrl O.Get Help: Press F1 to open the Help pane. Typeyour question in the Search field and press Enter.Save a Database: Click the Save button onthe Quick Access Toolbar, or press Ctrl S.Choose a location where you want to save thefile. Give the file a name, then click Save.Close a Database Object: Click the object’sClose button in the upper-right corner of thewindow.Use the Navigation Pane: Click the All AccessObjects button at the top of the NavigationPane and select the type of object(s) you want todisplay.Open a Database Object: Double-click an objectin the Navigation Pane.Modify a Database Object in Design View: Clickthe View button list arrow and select DesignView.Switch Object Tabs: If you have multiple objectsopen, click the tab for the object you want todisplay.Rename a Database Object: In the NavigationPane, right-click the object you want to renameand select Rename. Type a new name, andpress Enter.Delete a Database Object: Select the object youwant to delete in the Navigation Pane, press theDelete key, and click Yes.Click the topic links for free lessons!Move between query ortable rows . , Close a Database: Click the File tab and selectClose.Database ObjectsTables store a database’s data in rows(records) and columns (fields).Queries ask a question of data stored ina table.Forms are custom screens that providean easy way to enter and view data in atable or query.Previous field . Shift TabNext screen . Page DownPrevious screen . Page UpFirst record . Ctrl Last record . Ctrl Go to a specific record . F5Toggle Navigation Pane . F11EditingCut . Ctrl XCopy. Ctrl CPaste . Ctrl VUndo . Ctrl ZRedo . Ctrl YFind . Ctrl FReplace . Ctrl HReports present data from a table orquery in a printed format.Design ViewMacros automate several tasks into asingle command.View properties . Alt EnterModules automate commands usingVisual Basic. Modules are similar tomacros, but are more complex.Save object . Ctrl SOpen object in Designview . Ctrl EnterSwitch to Form view . F5 2020 CustomGuide, Inc.Contact Us: [email protected]

DatabasesWork with DataTablesDetermine the Purpose of a Database:Planning a database is an important step in thecreation process. Consider:Find Data: Select the column header or click inany cell in the field you want to search, andclick the Find button on the Home tab. Typethe text you want to find in the Find What field,and click the Find Next button.Create a New Blank Table: Click the Createtab on the ribbon and click the Tablebutton. Click the Click to Add field heading,select a field type, type a name for the field,and enter the data for the new field. Click thenext Click to Add field heading to add anotherfield. Fields you need Tables you need The primary key Relationships between fieldsCreate a Database from a Template: Click theFile tab and select New. Type a word/phrasethat describes the database you want to createin the Search for online templates field andclick Search . Select the template you wantto use, name the new database, andclick Create .Create a New Blank Database: Click the Filetab, select New, and click the Blankdatabase button. Type a name for the newdatabase, and click Create .Add a Record: In Datasheet view, click theNew Recordbutton on the recordnavigation bar. Or, begin adding data in theblank bottom row of the table.Record selectorReplace Data: Select the column header orclick in any cell in the field where you want toreplace text, and click the Replace buttonon the Home tab. Enter the word you want tofind in the Find What field, then enter the textthat will replace it in the Replace With field.Click Replace or Replace All.Sort Records: Click anywhere in the columnyou want to sort and click either theAscending or Descending button onthe Home tab of the ribbon.Remove a Sort: Click the Remove Sortbutton on the Home tab.Apply a Filter: Click anywhere in the columnyou want to filter and click the Filter buttonon the Home tab. Uncheck the boxes for anydata you want to hide and click OK.Filter by Selection: Click in the column youwant to filter, click the Selection button onthe Home tab, and select an option from themenu.Select a Record: Click the record selector(gray box to the left of the record) for therecord you want to select.Filter by Form: Click the Advanced FilterOptionsbutton on the Home tab and selectFilter by Form . Click the empty cell belowthe field you want to filter, then click the listarrow and select the value you want to use tofilter the records. Repeat this to filter by anyadditional fields, and click the Apply Filterbutton on the Home tab to filter the records.Edit a Record: Click the field you want to editand make the necessary change. Press Enterto commit the change.Clear Filters: Click the Advanced FilterOptionsbutton on the Home tab of theribbon and select Clear All Filters .Delete a Record: Click the record selectornext to the record you want to delete, click theDelete button on the ribbon, and click Yes.Adjust Column Width: Click and drag thecolumn header’s right border to the left or right.Or, double-click a header’s right border toauto-size the column.Start adding a new record hereSelect Multiple Records: Click the recordselector for the first record you want toselect and drag to the last record you want toselect.Select a Field: Click the field (column) headerfor the field you want to select.Copy and Paste: Select the data you want tocopy, click the Copy button on the Hometab, then click where you want to paste thedata, and click the Paste button.Check Spelling: Click the Home tab and clickthe Spelling button, or press F7.Print Preview a Database Object: Display theobject you want to preview. Click the File tab,select Print, and click Print Preview .Print a Database Object: Display the objectyou want to print. Click the File tab, selectPrint, and click Print .Click the topic links for free lessons!Create a Table in Design View: Click theCreate tab on the ribbon and click the TableDesign button. Enter a name in the FieldName column and click in the first Data Typefield. Click the Data Type list arrow, andselect a data type for the field.Insert a Field: In Design view, click the recordselector for the field that will be below thenew field, and click the Insert Rows buttonon the Table Tools Design tab. Enter a fieldname for the new field, press Tab, click theData Type list arrow, and select a datatype.Reorder a Field: In Design view, click therecord selector for the field you want tomove, then click and drag the selected row upor down to the desired location.Delete a Field: In Design view, click the recordselector for the field you want to delete, andclick the Delete Rowsbutton on the TableTools Design tab.Change a Field Type: Open the table whosefield(s) you want to modify in Design view, clickin the field’s Data Type box, click the DataType list arrow, and select a data type.Add a Primary Key: In Design view, select thefield you want to use as your primary key, andclick the Primary Key button on the TableTools Design tab.Change a Field’s Properties: In Design view,select the field that you want to change theproperties for, click in the property field youwant to change, and enter the new settings.Adjust Row Height: Click and drag the rowheader’s bottom border up or down.Change the Field Size: In Design view, selectthe field whose size you want to change. Clickin the Field Size field and type or select thefield size.Freeze a Column: Click the header for thecolumn you want to freeze, click the Morebutton on the Home tab, and select FreezeFields .Index a Field: In Design view, select the fieldyou want to index, click in the Indexedproperty field, click its list arrow, and select anindexing option.Unfreeze a Column: Click the header for thecolumn you want to unfreeze, click the Morebutton on the Home tab, and select UnfreezeAll Fields.Add a Description to a Field: In Design view,click in the field’s Description box and typethe description.Hide a Column: Click the header for the columnyou want to hide, click the More button onthe Home tab, and select Hide Fields.Unhide a Column: Click any column header,click the More button on the Home tab ofthe ribbon, and select Unhide Fields.Add a Caption to a Field: In Design view, selectthe field you want to add a caption to, click inthe Caption property field, and type a caption.Add a Total Row: In Datasheet view, click theTotals button on the Home tab. Click thecell in the Total row for the column you want todisplay the total for, and select the function youwant to perform on the field. 2020 CustomGuide, Inc.Contact Us: [email protected]

Microsoft Access Quick ReferenceFree Cheat SheetsVisit ref.customguide.comIntermediate SkillsOverview of QueriesQuery ExamplesExpressionThe queried tables appear here.You can also link tables.To add a field tothe query, clickand drag it fromthe table down tothe design grid.Sort orderShow resultsCriteria rowsDesign GridQueriesCreate a Query: Click the Create tab on theribbon and click the Query Design button.Select the table(s) you want to add to thequery, click the Add button, and close thedialog box. Double-click each field you want toinclude in the query. Click the Save buttonon the Quick Access Toolbar, type a name forthe query, and click OK.Run a Query: Double-click a query in theNavigation Pane.Hide Fields from Queries: Clear the Showcheck box in the design grid for the field(s) youwant to hide.Find Unmatched Records: Click the Create tabon the ribbon and click the Query Wizardbutton. Select Find Unmatched Query Wizardand click OK. Use the wizard to complete the findunmatched query, enter a name for the query inthe final step of the wizard, and click Finish.Crosstab Queries: Click the Create tab on theribbon and click the Query Wizard button.Select Crosstab Query Wizard and click OK.Use the wizard to complete the crosstab query,enter a name for the query in the final step of thewizard, and click Finish.Create a Multiple Table Query: Click theCreate tab on the ribbon, click the QueryDesign button, double-click the tables youwant to add to the query, and click Close.Double-click each field you want to include inthe query and save the query.Create a Delete Query: Click the Create tab onthe ribbon and click the Query Design button.Select the tables and queries you want to add,click Add, and close the dialog box. Connect anyunrelated tables and click the Delete button onthe Design tab. Double-click the asterisk (*) inthe table field list for the table containinginformation you want to delete. Drag the field youwant to use as the limiting criteria onto the designgrid. Then, click in the field’s Criteria row andtype the specific data you want to delete.Work with the Expressions Builder: In Designview, click in the Field row of a blank column inthe design grid, and click the Builder buttonon the Design tab. Select a field to use in thecalculation, select an operation for thecalculation, click or type any other fields orvalues you want to use, and click OK.Create an Append Query: Click the Create tab onthe ribbon and click the Query Design button.Select the tables and queries you want to add,click Add, and close the dialog box. Click theAppend button on the Design tab, specify theappend settings, and click OK. Double-click anyof the fields you want to append.Sort Fields: Click in the Sort field for the fieldyou want to sort, click the list arrow for thefield, and select a sort order.Click the topic links for free lessons!Result“London”Displays recordswhere the fieldequals “London.”“London” Or“New York”Displays recordswhere the fieldequals “London” or“New York.”Between 1/1/00And 12/31/00Displays recordswhere the date isbetween 1/1/00 and12/31/00.Year([OrderDate]) 2018Displays recordswhere theOrderDate fieldequals 2018.Is NullDisplays recordswhere the field isnull.Is Not NullDisplays recordswhere the field is notnull.Not "USA"Or ""Displays recordswhere the field doesnot contain the text"USA" and is notblank.Like “S*”Displays recordswhere the field textstarts with an “S.”Not Like “S*”Displays recordswhere the field textdoes not start withan “S.” “S”Displays recordswhere the field textstarts with letters “S”through “Z.” 100Displays recordswhose field value isgreater than 100. Sum([Sales])Displays the sum ofthe values in theSales field. Avg([Sales])Displays the averageof the values in theSales field. Count([Sales])Displays the numberof records in theSales field. 2020 CustomGuide, Inc.Contact Us: [email protected]

Relational DatabasesFormsReportsDatabase Types: There are two basic types ofdatabases.Add a Field: Click the Add Existing Fieldsbutton on the Design tab and double-click thefields you want to add in the Field List pane atthe right.Delete a Column: Select a column or controland press the Delete key. Flat File Database: Stores all of itsinformation in the same place, such as asingle table or list. Relational Database: Stores informationin multiple tables that are related throughmatching fields. Access is a relationaldatabase.Relationship Types: There are three differenttypes of relationships used when linking tablestogether. One to One: Each record in a table relatesto one record in another table. One to Many: Each record in a tablerelates to one or more records in anothertable. Many to Many: One or more records in atable relate to one or more records inanother table.Create Relationship Between Tables: Click theDatabase Tools tab on the ribbon and clickthe Relationships button. Click the ShowTablebutton on the Design tab, doubleclick the table(s) you want to add, and closethe dialog box. Click the related field in the firsttable and drag it to the related field in thesecond table, select the Enforce ReferentialIntegrity check box (optional), and clickCreate.Print the Relationship Window: Click theDatabase Tools tab on the ribbon, click theRelationshipsbutton, and click theRelationships Reportbutton on theDesign tab. Click the Print button on thePrint Preview tab, select the desired printsettings, and click OK.FormsCreate a Form with AutoForm: In theNavigation Pane, click the table or query thatcontains the data you want the new form touse, click the Create tab on the ribbon, andclick the Formbutton.Create a Form with the Form Wizard: Click theCreate tab on the ribbon and click the FormWizard button. Click the Tables/Querieslist arrow and select the table or query youwant to use to create your form. UnderAvailable Fields, double-click the fields youwant to appear in the form. Use the wizard tocomplete the form setup, enter a name for theform in the final step of the wizard, and clickFinish.Change Form Views: Click the View listarrow on the Home tab and select a view.Move a Control: Click the control and drag it toa new location on the form.Delete a Control: Select the control you want todelete, press the Delete key or click theDeletebutton on the Home tab.Click the topic links for free lessons!Add a Control to a Form: In Design view, clickthe control button you want to add in theControls group of the Design tab. Click a spoton the form to place the control. If the controlyou added opens a wizard, navigate through itand specify the desired settings.Work with Control Properties: In Design view,select the control you want to edit and click theProperty Sheet button on the Design tab.Click the appropriate property field in theProperty Sheet pane and make the necessarychanges.Insert a Logo: In Layout view, click the Designtab on the ribbon, and click the Logobutton.Locate the file, select it, and click OK.Adjust Page Margins: In Layout view or Designview, click the Page Setup tab on the ribbon,click the Margins button, and select a marginoption.Adjust Page Orientation: In Layout view orDesign view, click the Page Setup tab on theribbon and click the Landscapeor Portraitbutton.Advanced TopicsChange a Control’s Data Source: Display theform in Design view or Layout view with theProperty Sheet displayed. Select the desiredcontrol, click the Data tab in the PropertySheet pane, click in the Control Source field,and edit the source as desired.Import Data: Click the External Data tab on theribbon and click the New Data Sourcebutton. Select the type of data you want toimport and select the file type. Browse for thefile you want to import and click OK. Completethe steps in the import dialog box.Create a Subform: In Design view, resize theform to make room for the subform. Click theControls button on the Design tab and clickthe Subform/Subreport button. Click anddrag where you want the subform to appear.Use the wizard to complete the subform setup,enter a name for the subform in the final step ofthe wizard, and click Finish.Export Access Objects: Select the databaseobject you want to export in the NavigationPane. Click the External Data tab and click thebutton for the type of file to which you want toexport in the Export group. Select the desiredexport options and click OK.ReportsCreate and Save a Report: Select the table orquery you want to base the report on in theNavigation Pane, click the Create tab on theribbon, and click the Report button. Clickthe Save button on the Quick AccessToolbar, give the report a name, and click OK.Create a Report with the Report Wizard: Clickthe Create tab on the ribbon and click theReport Wizard button. Click theTables/Queries list arrow and select the tableor query you want to use to create your report.Under Available Fields, double-click the fieldsyou want to appear in the report. Use thewizard to complete the report setup, enter aname for the report in the final step of thewizard, and click Finish.Use the Label Wizard: Open the table or querythat contains the data for your labels. Click theCreate tab on the ribbon and click the Labelsbutton. Use the wizard to complete the labelsetup, enter a name for the report in the finalstep of the wizard, and click Finish.Work with Report Layouts: Open the report inLayout view, click the Arrange tab on theribbon, and click a command in the Positiongroup.Move a Column: Click and drag a column’sheading to a new location to move a column, orsimply click and drag a control to a newlocation.Save a Database to Other Formats: Click theFile tab on the ribbon and select Save As.Select the file format to which you want to saveyour database and click the Save As button.Enter a name for the database in the File namefield and click Save.Apply Conditional Formatting: In Design view orLayout view, click the field you want to format.Click the Format tab on the ribbon and click theConditional Formatting button. Click NewRule, select a rule type, and specify the ruledetails. Click OK, then click OK again to savethe rule.Compact and Repair a Database: Click theDatabase Tools tab on the ribbon and click theCompact and Repair Database button.Password Protect a Database: Click the File tabon the ribbon and click the Encrypt withPasswordbutton in the Info section. Type apassword in the Password field, enter it again inthe Verify field, and click OK.Back Up a Database: Open the database thatyou want to back up. Click the File tab on theribbon and select Save As. Select Back UpDatabase and click the Save As button.Specify the save location and type a name forthe file, then click Save.Restore a Database: Open File Explorer andnavigate to the location of the last knownbackup of the database. Copy the databasebackup file. Navigate to the location of thedamaged or missing database and paste thedatabase backup file. 2020 CustomGuide, Inc.Contact Us: [email protected]

Get More Free Quick References!Visit ref.customguide.com to download.Office 365G SuiteOSSoft SkillsAccessClassroomMac OSBusiness WritingExcelG SuiteWindows 10Email EtiquetteOffice 365GmailOneNoteGoogle CalendarOutlookGoogle DocsDigital LiteracySecurity BasicsPowerPointGoogle DriveSalesforceSMART GoalsTeamsGoogle SheetsWordGoogle SlidesManage MeetingsProductivityPresentations more, including Spanish versionsLoved by Learners, Trusted by TrainersPlease consider our other training products!Interactive eLearningCustomizable CoursewareGet hands-on training with bite-sized tutorials thatrecreate the experience of using actual software.SCORM-compatible lessons.Why write training materials when we’ve done itfor you? Training manuals with unlimited printingrights!Over 3,000 Organizations Rely on CustomGuide“Contact Us!The toughest part [in training] is creating the material, which CustomGuide hasdone for us. Employees have found the courses easy to follow and, mostimportantly, they were able to use what they learned [email protected]

Use the Navigation Pane: Click the All Access Objects button at the top of the Navigation Pane and select the type of object(s) you want to display. Open a Database Object: Double-click an object in the Navigation Pane. Modify a Database Object in Design View: Click the View button list arrow and select Design View.