Report CopyRight/DMCA Form For : Microsoft Access Basics Media News Health Ufl Edu
Microsoft Access Basics amp Database Fundamentals 3 0 hours Microsoft Access is a relational database application It is the perfect tool when you begin to outgrow your data collection in Excel With Access you can obtain better collection results by creating user friendly forms with rules to protect the validity of your data You can create
Microsoft Access Basics Database Fundamentals , 3 0 hours . Microsoft Access is a relational database application It is the perfect tool when you begin to outgrow your data. collection in Excel With Access you can obtain better collection results by creating user friendly forms with. rules to protect the validity of your data You can create queries to analyze and filter your data and reports that. can be regenerated anytime you need them Topics for this workshop include database concepts planning a. database and a hands on introduction to tables queries forms and reports . , What is a Database 1 , Why use Microsoft Access 1 . Planning the Database 2 , Design Rules 2 , Organizing Data 2 . No Derived Fields 2 , Data is broken down into Smallest Logical Parts 2 . Descriptive Field Names 2 , Unique Field Names 2 . No Calculated Fields 2 , Unique Records 3 , Basic Access Objects 3 . Tables 3 , Vocabulary 4 , Queries 4 , Forms 4 , Reports 6 . Class Exercise 7 , Bonus Exercise 21 , , , , , Pandora Rose Cowart . Education Training Specialist , UF Health IT Training . , , C3 013 Communicore 352 273 5051 , PO Box 100152 prcowart ufl edu . Gainesville FL 32610 0152 http training health ufl edu . ,Updated 2 01 2017 , ,What is a Database , A variety of definitions exist for a database but essentially it s a collection of information A filing . cabinet a Rolodex a library card catalog and even the Internet are all types of databases . , Most often the word database is used to describe a collection of related data information stored . on computers An electronic database should allow you to store sort and retrieve data You can create . simple databases by creating a Word table or an Excel spreadsheet . , For example here we have simple database of our patients . MedRec First Name Last Name DOB Doctor, 123 456 Jack Nimble 06 08 72 Edwards. 987 654 Jill Pail 08 27 65 Lewis, 753 951 Mary Bluebell 12 08 51 Edwards. ,Here is a simple database of our doctors , EmpID First Name Last Name Phone . 999 999 Ken Edwards 555 1234, 888 888 Laura Lang 555 4567. 777 777 Yolanda Lewis 555 7890, ,Why use Microsoft Access . Microsoft Access is a relational database application Relational means we can link together sets of . data we can relate the data We can keep track of the patients the doctors and when the patients last . saw their doctors what happened at each visit and so on Access allows us to relate our data without . the repetition that may occur anywhere else , , In an Access database we can create both of the datasets and link them . MedRec First Last DOB Doctor EmpID First Last Phone . 123 456 Jack Nimble 06 08 72 Edwards 999 999 Ken Edwards 555 1234. 987 654 Jill Pail 08 27 65 Lewis 888 888 Laura Lang 555 4567. 753 951 Mary Bluebell 12 08 51 Edwards 777 777 Yolanda Lewis 555 7890. , In Access the data is saved in Tables As the data in the Tables change the rest of the Access database . will reflect the newest information i e the Queries Forms and Reports . , Queries show the data in a Table format A Query can pull from multiple Tables and allow you to limit . the records rows display by using criteria and showing only the fields columns you want We can find . the phone number for Jill Pail s Doctor and provide Ken Edwards with a list of his patients . , Forms can be created to provide a user friendly side to your database They are used to view and . enter your data in an interactive formatted structure Forms are also used to make menus and search . windows that turn a simple data collection tool into a more interactive user friendly application . , Reports are created to print out your data in a formatted structure They allow you to group and . organize your data They can be used to create Form letters and mailing labels Access works beautifully . with Word for mail merges but the Reports tool allows for the multi level summaries . Page 1 , ,Planning the Database , The most important part of creating a relational database is planning This can be difficult when you are . first learning to use Microsoft Access Here are some questions that may help . 1 Input What data do I already have for the database . 2 Output What information do I want to get out of the database . 3 Process What do I need to do to get there , Sometimes it helps to plan the final Reports that you want from your database For example we want . to have a chart of how many patients attended their appointments Do we track the cancellations vs . the no shows What about the late arrivals and the rescheduled If we want to differentiate we need . to make sure we are going to collect that data This is why it s so important to plan everything to try to . predict the what ifs that may occur once you have your data collected . The Tables are the core of your Access database these structures store the data Tables are essential to . using any of the other Access Tools When planning out your database try to remember the basic design . rules for your Tables ,Design Rules ,Organizing Data . Once you have an idea of the data you would like to collect you need to decide how many tables you . might want to use to organize the data efficiently In Excel we might keep several numbered columns . to keep track of things i e Medication1 Medication 2 but in Access we should create a second table . to track the numbered fields ,No Derived Fields , By using the relationships between our data sets we can derive missing data If we are creating a new . appointment for a patient we only need to put in their Medical Record Number or other unique . identifier The patient s name phone number and other information can be derived from the Patient . Table ,Data is broken down into Smallest Logical Parts . Pulling fields together in Access is often simple pulling them apart usually requires human intervention . Think of this as breaking up the data into its smallest sort able part . Descriptive Field Names , It s tempting to use abbreviations when we are creating our data tables but if the title we use is too . vague or too abbreviated we may not be able to recall why we created that field DOB Date of Birth or . Department of Babies SSN Social Security Number or Shands System Number . Unique Field Names , Be sure to differentiate between the field names in each Table We can have a First Name in our . Patient Table and a First Name in our Doctor Table but this can lead to confusion when we try to pull . both Tables into one database object ,No Calculated Fields . In Microsoft Excel we can perform our calculations on the same sheet as our data but a Table in Access . is stagnant data it does not change unless you make it change Access will let you create calculations in . Queries Forms and Reports Newer versions of Access do have a Calculated field type for the table This . embeds a calculation in the record and is not always reflected in a data entry form . Page 2 , ,Unique Records , It s important that each Table has a way to keep records unique We can do this by setting one field . column to be a Primary Key field When a field is set as a Primary Key Access will not allow any . duplication nor blanks When there is not a unique field in your data set you can use an AutoNumber . AutoNumbers are incremented or random fields that are always unique . ,Basic Access Objects , Access consists of four main database objects Tables Queries Forms and Reports Each object has at . least two views Design and Data The Design View is where we build the structure of that database . object The data view shows the output of the data and is different for each object Tables and Queries . have a Datasheet View Forms have a Form View and Reports have a Report View or a Print Preview . view Each kind of object has its own purpose ,Tables . Tables store data The Tables are the true database base of data These need to be created and . properly linked related in order to effectively use the other Access tools Tables are the core of your . database everything else in Access depends on the Tables . The Design View of a Table allows you to create and modify . Field Names the column headings , The type of data stored in a field Data Type In this workshop we use . Data Type Description , Short Text Allows any alphanumeric characters up to 255 characters . Number Limited to Numbers only, Date Time Allows Dates and or Times only . AutoNumber Creates a unique number for each record . Yes No This is a binary field only two answers Yes No True False . Lookup Wizard The lookup wizard allows you to link the field to another Table . or to type in a list of your own creation , Descriptions which will be displayed in the status bar in the Data view of Forms . And the Properties of each field such as how many characters can be entered text field size or . how the data is formatted 05 05 15 or May 5 2015 . , , Page 3 , , The Datasheet View of a Table allows you to create and modify the data within a grid structure based . on the settings in the Design View , Field ,Record . Table , , Vocabulary , A collection of fields make up a record A collection of records make up a Table A collection of . Tables make up a database , Field One column of a Table common to all the records . Record One row of a Table containing all data about a particular entry . Table One set of related data , Database Structured collection of related Tables . , Queries , Queries show a selection of data based on criteria limitations you provide Queries can pull from one . or more related Tables and or other Queries , The Datasheet View of a Query looks like a Table All data added or modified in a Query will be saved . in the Table The Design View is where the structure of the Query is created This is where we choose . the record sources and fields and set the sort order and criteria . , Record Sources Tables and or , Queries containing the data . Fields Field names from the , above record source and . expressions to build new fields , Sort Order Order of the result in . order of position , Criteria Limitations applied to the . final result , Page 4 , ,Forms , Most Forms display one record at a time in a formatted user friendly environment You can build your . Form so it will display multiple records As you develop Forms you can create navigation buttons insert . graphics and change the colors to display everything consistently Forms have three basic views Design . View Layout View and Form View , , Your record source can be a Table or Query If we want to all the patients use the Table if we only . want to see Dr Edward s Patients use a Query , , , . The data entered or modified in a Form is automatically saved to the Table The Table is the true . location of the data the Form is a pretty way to view modify create the data . , For the Basic Workshop we will use the AutoCreate and Wizard buttons to make our Forms . , We modify our Forms by using the Layout View to change the placement and size of the fields and the . Design View to add objects like command buttons to move between records and open other database . objects like other Forms and Reports , , Page 5 , . Reports , Reports are designed to create an organized output of data from your database With a Report you can . group and summarize information You can t edit the data in a Report but if you make the . modifications in the Table Query or Form you will see the results when you open the Report again . Reports have four basic views Report View Print Preview Layout View and Design View . , For the Basic Workshop we will use the wizard and AutoCreate buttons to make our Reports . , The Print Preview and Report View allow you to view how the data falls into the Report . The Print Preview will show you how the data falls on the page and how it will appear when . printed , The Report view lets you see a continuous flow of the data without page breaks . , The Design View and Layout View allow you to resize and move the fields . The Design View allows you to add objects like text boxes that contain formulas . The Layouview allows you to resize the field and see the data at the same time . , Page 6 , Access 2016 Basics Class Exercise ,Class Exercise . ,Create the Database , 1 Open Microsoft Access , 2 Choose Blank Desktop Database . 3 Click on the yellow folder at the end of the , File Name box and browse for the desktop . 4 Use the file name Patient Appointments , 5 Click Create . ,Explore the Window , 1 Close Table1 with the X under the ribbon or by right clicking on the name of the table . 2 Explore the Ribbon , a Home tab Clipboard Sort Filter Spell Check Refresh Data Format text . b Create Create a database object Tables Queries Forms Reports . c External Data Import and Export data , d Database Tools Advanced Features of Relationships and Data Analyzers . ,Create the Patients Table , 1 Click on the Create tab and choose Table Design . , 2 Type the first Field Name Pt Med Rec , a Data Type Short Text Description Patient s Medical Record Number . 3 Enter in the rest of the fields descriptions not necessary . , 4 Set the Pt Med Rec to be the key , a Click on the big yellow key on the toolbar . 5 Save the Table as Patients , Page 7 , Access 2016 Basics Class Exercise . Entering First Record , 1 Turn to the Datasheet View . 2 Enter our first Med Rec 123 456 , 3 Press tab move to the next field . Pt Med Rec Pt First Pt Last Pt Phone Pt Birth Date . 123 456 Sam Franks 3525551234 1 1 1 , a First Name Sam . b Last Name Franks , c Phone 3525551234 , No dashes . d Birth Date 1 1 1 , If you set it as a DATE TIME field Access will add in the 200 for 2001 . Exit the Database , 1 Exit the database Access will probably not ask you to save . a But it did save the record it does so automatically . 2 Open your database from the desktop , a If necessary Enable Content . 3 Open the table double click from the navigation pane . a Sam is still there , ,Rearrange Fields , 1 In Design View move Pt Birth Date above the Pt Phone . a Click on the row heading the grey box in front of the field name Then Click Drag the . line above the Pt Phone , 2 Switch to the Datasheet View and Save the table . a Data saves itself structural changes have to be saved manually . 3 Enter the next record , Pt Med Rec Pt First Pt Last Pt Birth Date Pt Phone . 789 012 Jacob Smith 2 2 92 3525554321, a No hyphens in the phone number . ,Adding Fields , 1 In Design View create Pt Gender Short Text field above Pt Birth Date . a Insert Rows from Design Tab or from the right click menu . 2 In Data View enter Male the whole word for Sam and Jacob . Page 8 , Access 2016 Basics Class Exercise ,Enter a trouble maker Record . 1 Enter the next record , Pt Med Rec Pt First Pt Last Pt Gender Pt Birth Date Pt Phone . 555 555 Jane Williams F March 3 1983 352 555 5555 . a Enter Gender as just one character , b Enter birth date as March 3 1983 it should change to 3 3 1983 . c Type in the hyphens for the phone number , 2 Go to the Design view and then return to the Data view . a Notice Jane s record moves This is because by default Access sorts by the primary key . field Since Pt Med Rec is our key every time the data is refreshed it will sort the data . by the primary key field , , ,Modify Field Properties Field Size . 1 In Design View set Field Size property of Gender at the bottom of the window to be 1 . a When you save you will get the following warning message saying data may be lost We . want this to happen click Yes , , b Data is lost our Male entries should now only read M . ,Modify Field Properties Format , 1 In Design View set the Format property for Pt Birth Date to be a Medium Date . a Notice there is no field size for a date field because it doesn t matter how many . characters you type in as long as it s a valid date . b Access recognizes dashes 1 1 2001 and slashes 1 1 2001 for date formats . , Page 9 , Access 2016 Basics Class Exercise ,Modify Field Properties Input Mask . 1 In Deign View set an Input Mask for the Phone Number . a Click in the Input Mash Property for Pt Phone , b Click the Build button at the end of the line to launch the wizard . c In the Input Mask Wizard Phone Number is already selected Click FINISH . d Save and View Results , , 2 Fix Jane s Phone Number by taking out the extra dashes . ,Enter a New Record , 1 Enter a new record , Pt Med Rec Pt First Pt Last Pt Gender Pt Birth Date Pt Phone . 527 594 Doris Jones F 4 4 74 3525555432 , 2 Close the Table . 3 Open the Patient s Table , , 4 Close the Table . ,Create Female Patient s Query , 1 Go to the Create Tab and choose Query Design . , , Page 10 , Access 2016 Basics Class Exercise , 2 In the Show Table window push the Add button and then close the window . 3 Double clicking on the field names to add Pt First Name Pt Last Name and Pt Gender . 4 View Datasheet View , , ,Customizing a Query , 1 In the Datasheet view notice the sort order is by Med Rec . 2 In the Design view set Query to Sort by Pt Last Name Ascending . 3 Go to the Data View patients should read Franks through Williams . 4 In the Design View set the Criteria line for the Pt Gender field to be F . a In Datasheet view you should only have two people Jane and Doris . 5 Close and save the Query as Female Patients , , Page 11 .