IT som vrktj Bent Thomsen Institut for Datalogi Aalborg Universitet Introduction to MS Access Bent Thomsen Microsoft Access Window Open Access Start-Programs-Microsoft Access Double-click on an Access file (Student Record) Objects Bar Objects Operations Viewing Objects Properties Microsoft Access Objects Tables Store information with Columns (fields), rows (records) Queries Acquire selected information with certain criteria. Forms Display one record in the window Convenient for entering, displaying, and printing data. Reports Display records with selected fields in a report layout. Display multiple records in a page. Microsoft Access Objects Pages Display records in form of web pages. Macros A set of commands that are executed automatically one after another. Macros are used to automate the performance of any repetitive task. Modules Provides a greater degree of automation through programming in Visual Basics for Applications (VBA) Table-Datasheet View Double-click a table, you are in datasheet view. Add, edit, or delete records Field NamesCurrent RecordNew Record Table-Design View Define the table initially and to specify the fields it will contain. Define Field Name Define Field Type Print or Change Properties of Tables, Forms, Reports, Queries In the database window Right-mouse click an object (report, form, query, page) Print Cut CopyDelete Rename Create a Database Start-Programs-Microsoft Access Select Click on Save the Database Type in name of the database file If you need to save the file into a new folder, you click on and type in the folder name. Click on Type in the file name. Click Create a Table in Design View Double-click For each field Type in a field name Select a data type Select student ID field Click on to set a primary key Click on to save the table Type in the table name Click on Click to input data. Use Tab or arrow keys to navigate. Data Input in the Table Click on to go to the datasheet view when you use Design View to create a new table. Type in information in the cells tab key, or to navigate to a different field. keys to a different record. Adjust the column width Move the mouse to the boarder between the two columns until a sign shows, and drag the boarder to adjust the column width. Double-click the boarder between the two columns and the column width will be adjusted automatically. Delete a record, select any cell of the record and click Add a Field in a Table Created In Design View Add a parents name field before address field Select the address field, go to Insert and select rows Type the parents name for the field name. Click to update the table set up. Create a Table by Using Wizard Double-click on Select the category of the wizard, business Select Student in the sample table window. Select StudentID in the Sample Fields window, click on Click on, type in the new name ID. Click on Select Firstname, Lastname, and other fields individually and use to move the field to the selected field window. Click on Select a Wizard and Fields Name the Table Type in the name of the table Select, click on Select a Primary Key Field Select ID as the primary key, Next Set Up Data Input Create a Table by Entering Data In database window Double-click In the Datasheet view Double-click field1, type a field name ID. Double-click field2, type a field name First Name repeat the above steps until you type all the fields. You need to set up a primary key by going to Design View Click, select ID field cell and click Click to save the table. Click to Datasheet View to input data. Navigating and Updating Data in the Table Previous Record Next RecordAdd a New Record Save Data-Access automatically save a table as soon as you move to the next record or close the table. Updating Records in a Table Adding a record Type in the fields right next to Deleting a record Select a record by clicking, hit delete key or. Change a record Highlight or click the cell you want to change and type new information Create a Form with AutoForm Form, easy to input and update data, update data on more than one table. In the database window, select the table anth100address, go to and select Modify the Form Click on to switch to Design View Drag the right border of the form box to the right to enlarge the size of the form window. Select the Mailing Address box, drag the right border to the right. Click on to switch back to Form View Save the Form Click on, type in the form name and click Update Records in a Form In the Design View of the form Use to select a record Change the information by highlighting a field and typing in new information To delete a record, select a record and click Use to add a new record. The record will be added to the table simultaneously. Search for a Record in a Form In the Design View of the form Edit-Find, type the last name Carpenter, select the table and whole field. Click Create a Form with Form Wizard In the database window, select Double-click Select a table you want to create a form from. Select a field and click. Click to select all fields. Click Create a Form from Wizard Select a form layout such as Click Select a style such as Expedition Click Type a form title. Select Modify the forms design. Click Then you are in form design view and you may modify the form. Set Up Relationships In the Access window Click the relationship icon Click the add a table icon Select allgrades table Click Select anth100address table Click Select anth100grades table Click Add all tables when necessary Click Create a Relationship Click on the ID field in allgrades table. Drag the mouse to the ID field of anth100address table like you are drawing a line between the two fields. Check Enforce Referential Integrity Click Final Relationship Chart Repeat adding a table and drawing a relationship for all tables. Drag the Blue bar on a table to rearrange them as follows and click to save the relationship chart. Create a Report Report Display records in selected fields Display students grades for Anthropology 100 with ID, names, final scores, and grades In the Database Window, select Double-click on Select Fields Select Anth100grades, select a field, click, click Click In the group window. Sort Window Click in the Sort Window. You may sort a field at the report design view later on. Select Sort Field Select fields for sorting. Click In the report, the fields will be listed in the order as sorting order. Select Report Layout Select a layout and click Select a Report Style Select a style and click Title the Report Type the title of the report, select modify the reports design, click. Click to save the report. Preview the Report Click preview icon to go back to the report preview window. Create a Query by Using Wizard Running a query is to display selected fields with certain criteria. Anthropology 100, students who scored higher than 80 with ID, names, final scores, grades. Open the file Student Records by double- clicking it. In the Database Window, click Double-click Select a Table and Fields Select a Table Select a field Click Click Select Ways of Displaying a Query Name the Query Set Up the Criteria and Run the Query Type in the >80 in the Scores criteria cell. Click to run the query. Click to save. Create a Query in Design View Display a query for Art 200 grades with ID, Names, Scores less than 90, and Final Grades. In the database window, click on Double-click on Select Click Click to close the table selection window Select the Table Click, click, click Select Fields, Input Criteria, Run Query Double-click on ID, First Name, Last Name, Score, and Grade field individually. Type >70 in Grades/Criteria cell. Click to run the query. Use Edit-Clear Grid to clear the query fields. A Query with WildCards Using WildCards to select students with Bs grade, including B, B+, and B- Type B* in the criteria cell in the Grade field of Art 200 Grade Table query window. Hit Enter. Click, and double-click Select Art 200 Grade table, click, click Select fields: First Name, Last Name, Scores, Grade. Type B* in the Grade criteria cell of Grade field and hit enter. Click to run the query. Fields, Criterion, and Run a Query Select fields by double-clicking the field. Type B* in Criteria cell of Grade. Hit enter. Click to run the query. A Query with Compound Criteria And One criteria and the other criteria are all met. Students grades >80 in final scores and >80 in final project in Art 200 Type in criteria in two different cells in the criteria row. Practice---Grades >=60 and 80 in final project and >80 for final grades Type >80 in Final Project and Score cells in the criteria row. Click Or Logic Student grades A or B In the Score fields, type A in Criteria cell and B in Or cell. Click to run the query. Sorting Data in A Query List student grade query for Art 200 with alphabetical order in Last Name. Select Ascending in Sort cell of Last Name field. Click to run the query. Click to save the query. Set up Calculations in a Query Select Classes, Scores, and Grades fields. Select Avg in the Scores field in Total row. Select Count in the Grades field in Total row. Total Query - Calculations in a Query Display the average score and number of students in all classes. Click, and double-click Select allgrades table and click and Select Classes, Scores, and Grades fields in the field row. Click Total icon In the Total row the first Scores field, select avg The second Grades field, select count Query in SQL View Right click on query and select Plan and Create a Database Determine the purpose of the database. Decide what tables to create (address, grades) Determine fields in each table (Name, phone,) Decide to create an ID field for each table to serve as the primary key. The primary key for a table is a unique identifier for each record in the table. Student ID# is a type of primary key in a student database. Opgaver og kursusmateriale