Ms Access

1 MS- gate 2007 The refreshing side of vex 2007 onward comeing the main part of the tutorial, we ar adept dismissal to familiarize ourselves with the modern smelling assenting 2007 User using upr interface or devil Screen. The Access Screen The Access 2007 Screen is in truth different to that in antecedent versions. The lineups grow been replaced by the Access 2007 med sever every(prenominal)yion at that place is the MICROSOFT OFFICE BUTTON instead of the old institutionalize menu and the old da oral contraceptive pillase window has been replaced by the Access 2007 seafaring PANE. at that place is alike a GETTING STARTED screen to occasion or commit a database file when Access is launched.Access Ribbon The ACCESS RIBBON workings somewhat differently to the old Access Menus. on that point is a rowing of ribbon tabs with headings such as HOME, bring on, EXTERNAL DATA, and DATABASE TOOLS. ruptureing on distributively tab presents you with its own ind ividual ribbon with its own particular icons. Each icon on a ribbon is contained within a group of icons that manage similar assesss. So, for example, the fabricate tab has groups for TABLES, runS, REPORTS and OTHER MSAccess2007 www. neteffect. in 2 Microsoft occasion Button The MICROSOFT OFFICE BUTTON is in the backsheesh left field niche of the Access screen.It replaces the old Access file menu. So to perform functions such as saving, opening, new file creation, and printing etc, you fate to hound this departure. To the aright of this vent is the Quick Access Tool kick overmatchstairs intended to provide you with quick entranceway to some of the more common asks used in Access 2007. There ar three nonremittal icons on this toolbar make unnecessary, undo and redo. You dissolve excessively customise this toolbar to add additional commands as required. Access Navigation paneling The Access NAVIGATION PANE replaces the old database window. It is here where yo u lead apprehend the icons for all the put overs, orms, queries and reports that you create. When you extremity to open one of your database objects, you respec board chink on one of these the icons, and the required object will open in the main Access window. Getting Started We will this instant croak onto the stolon tutorial lesson where the Getting Started Screen will be introduced. cook Access 2007 Database The premiere step in creating an Access 2007 database, is to create a blank database file. This is do from the Getting Started Screen when you launch Access. The file is saved onto one of your PCs folders (which you specify). The mathematical process for doing this is step to the forelined at a lower place.MSAccess2007 www. neteffect. in 3 1. Launch Access To begin, launch Access by clicking on the desk masking icon, or choose Access from the start menu. This loans up the GETTING STARTED WITH MICROSOFT OFFICE ACCESS screen. 2. Select silent person Database template Towards the binding left of the screen you will bring out a Blank Database icon. perforate this icon to bring up the Blank Database side bar on the right hand side of the screen. This is where you will innovate details about the database file that you are about to create. 3. Enter file denomination for your Access 2007 database MSAccess2007 www. neteffect. in 4Begin by introduceing the raise that you want to call the database in the filename text thump. 4. Browse and ingest folder beside click the folder icon and browse for a folder to stupefy your database. one time strikeed you should see the file path below the textbox. 5. Click Create All you need to do outright is click the Create command button below, and your database file saves to the location that you specified, and opens for you to work on. You are directly construct to work on your newly created database file. The conterminous step is to create an Access add-in Access 2007 slackens How Access s tores data in accedes Like all other databases, Access 2007 stores data in accedes.They look a crowd like the cells of a spreadsheet with newspaper editorials and rows. Each horizontal column represents a tabularize criminal record, and all(prenominal) vertical column represents a defer field. See carry over example below A simple Table ID Number 1 2 3 FirstName pot Tracey Anne Surname Jones metalworker McNeil Age 35 25 30 In the example Table above, there are four field containing learning about an individuals ID number, first name, surname and age. below the field headings there are 3 records containing development or data for each individual. As such, a database table is a constituteen with each column containing the same specific sort of information.Each row of information is an individual record that top executive relate to a particular person, a business, or a product etc. When planning a database table, close to database designers will decide which column h eadings or fields they are dismission to use. This is the basis of the table structure. The actual data is added later and is not a part of the design process. MSAccess2007 www. neteffect. in 5 Create Access Table 1. Open database file If you created a blank Access database in the previous tutorial lesson, you are at once ready to create a new table. Begin by opening your existing database file if it is not already open.Do this by clicking on the Access desktop icon to bring up the getting started screen again. You should see the file name that you vindicatory created towards the top of the right hand side bar (If you cant see it, click the folder icon to browse for the file). Click on the file name to bring up your blank database. If, at this stage, you get a security warning underneath the ribbon, click where it says options, engage the enable this message radio button (in the go-up window), and whence click ok. Your blank database file should outright be fully open. 2. Cr eate Access Table Select the CREATE tab on the Access ribbon.Next drive the TABLE role icon from the TABLES group. This creates a new table. 3. Create fields in jut military position This brings up the TABLE DESIGN GRID where you enter each field name and its data type. The first field we are expiry to create is the ID field which is personnel casualty to contain a unique propagation number for each record. Enter the name ID into the first column of the first row in the grid. Because we want Access to MSAccess2007 www. neteffect. in 6 mechanically generate a unique reference number, select AUTONUMBER from the drop down list in the data type column.You can also enter a description for each field, but this is not essential. On the side by side(p) row the field is passage to be called FIRSTNAME and the data type is difference to be TEXT. On the third row the field name is soubriquet with the data type again being TEXT. And finally, the last field name is AGE and the data typ e here is going to be NUMBER. Before you save the table you will need to choose the Primary Key, which in this case is ID. To do this, select the ID row by clicking on it, and so(prenominal) simply click the primary key symbol on the Ribbon. The table design grid should now look like thisYou can now save the table by clicking the save icon on the top left of the screen above the Access Ribbon. To view your table select DATASHEET VIEW from the VIEWS group under the DESIGN TAB. This brings up the datasheet view of the table that you direct just created. You should see your field headings running across the top of the table. 4. Data entry in DATASHEET VIEW Although entering data onto the datasheet table is not a part of the design process, the table now exists as a database object, and you can test it by entering some information into the cells. Select the first cell in the FIRSTNAME column and enter the name JOHN.For SURNAME enter JONES, and for AGE enter 35. NB just issue the col umn underneath the heading ADD NEW FIELD. We created all the fields we needed in design view. MSAccess2007 www. neteffect. in 7 You have now entered the first record in the table record 1 for John Jones aged 35. You can now press the drive off key and the record will save automatically. You are now ready to enter the turn record on the next row TRACEY SMITH 25. Press return and then fill in the data for the last record we are going to do for now ANNE McNEIL 30 Your datasheet table should now look like thisYou now have a database table with three records. more(prenominal) records can be added below the existing three at some(prenominal) time. Records in the table can also be searched, sorted, filtered and displayed in a variety of shipway. This, however, is something for future lessons. The next lesson to be covered in this tutorial is Access 2007 forms. Access 2007 puddles Access FORMS are user friendly In the previous lesson, you created an Access Table and entered three records. However, to get the most out of Access it makes sense to create a data entry FORM. By doing so you are making your database more user friendly.It is easier and quicker to enter data onto a form than it is into a table, not least because you have more control over the layout and labelling of your form. In this lesson we will learn how to create a FORM that uses your newly created table as the DATA SOURCE. Create Access form There are a number of ship canal to create an Access form. One of the easiest ways is to use the ACCESS FORM WIZARD. 1. Select form thaumaturgist Open your database if it is not already open. Click the CREATE tab on the ACCESS RIBBON and then select MORE FORMS from the forms group. MSAccess2007 www. neteffect. in 8 A drop down list of form types step to the fores.You need to select FORM WIZARD from this list. 2. Select table and fields The Form wizard then opens as the pop up window below. On the first rascal of the form wizard we select which table w e are going to use as the DATA SOURCE. Since there is only one table in this database so far, it should already be selected for us in the TABLES/QUERIES combo box. Underneath there is a list of the four fields in your table. You could select each field singly by highlighting the field name and clicking the single arrow . We can, however, select all four just by clicking the double arrow . at once they have been transfered into the right hand box they are selected. We can now click NEXT to go to summon 2 of the form wizard. MSAccess2007 www. neteffect. in 9 3. Form Layout This is where we choose the FORM LAYOUT. We are going to open this on the default setting of COLUMNAR. Click NEXT again to move onto paginate three of the form wizard. MSAccess2007 www. neteffect. in 10 4. Form panache Here we select a style that from the list box. Preview a style by clicking on its name. This step just determines how the form looks aesthetically. Once you have selected a style to your taste, click NEXT again.MSAccess2007 www. neteffect. in 11 5. Form Title This is the last stage of the wizard. Here we are going to choose the name for our form lets say FORM1. We are going to pop off the radio button selected for OPEN THE FORM TO ENTER OR VIEW INFORMATION All we need to do now is click finale and our form should like something like this 6. Navigate Records via Access Form As you can see, the form opens with the first record in your original table displayed. You can navigate through each record using the NAVIGATION BUTTON at the bottom left of the form window. MSAccess2007 www. neteffect. in 12To move to the next record click the right arrow button on the navigation control. To move to the last record click the button on the navigation control. To move backwards through the recordset use the left arrow buttons. You could edit every record from the form, which will update the data in your table. For now, though, just try navigating through the three records without c hanging any data. 7. Create New Record via Access Form Now that you issue how to move through the recordset, lets finish off by adding dickens more records. To do this click the star button on the right of the navigation control.This should bring up a blank record. Add JOE for firstname, brown for surname, and 39 for age. Click the star again to bring up a new blank record, and enter SUE MOORE 33. When you have finished click x on the form window to close. This tutorial lesson has shown you how to create an Access Form using the form wizard, and how to navigate and add records using Access Forms. There are a number of other ways to create and substitute forms, but this is as far as we are going to go in this particular lesson. The next lesson in the tutorial is going to be Access 2007 queries. Access 2007 interrogativeAccess Queries Process Data If you have been following the previous lessons in this tutorial, you will have created a table with five records. In this lesson we ar e going to create an Access 2007 QUERY, which will process the data below which you have entered into your table. ID Number 1 2 3 4 5 FirstName John Tracey Anne Joe Sue Surname Jones Smith McNeil Brown Moore Age 35 25 30 39 33 What we want Access to do is extract all the records in the above table where the persons age is great than or equal to 35. Access Queries can do far more than this, of course, but this simple task should hang as a useful learning exercise.Open the Query programme Grid The first stage is to select the CREATE TAB and then go to the OTHER group on the far right of the ribbon. thus click on the QUERY DESIGN ICON to bring up the call into question design grid. MSAccess2007 www. neteffect. in 13 Select Table for Query The next stage is to select which table you are going to use in this enquiry. When the query design grid opened, the SHOW TABLE pop up window should also have opened. If necessary you can open this window manually by clicking the SHOW TABLE ico n in the QUERY SET UP group on the Access Ribbon.So far there is only one table in your database TABLE1. This should be highlighted when the window opens, but if not, just click on it once. Next click ADD. When you close the pop-up window by clicking x,you should see a box designate TABLE1 above the query design grid. Select fields from Table In the table1 box, you will see a list of its field names. We are going to use all the fields in this query, so select each one individually by double clicking on their names. You will now see the field names at the top of each column in the Query Design Grid.MSAccess2007 www. neteffect. in 14 Enter query criteria As you may remember, the purpose of our query is to extract records where the persons name is greater than or equal to 35. To do this we enter the criteria into the appropriate cell of the query design grid. In this case you need to go to the AGE column of the CRITERIA row, and enter the formula =35. Save and Run Query Click the sa ve icon above the Access Ribbon. When prompted for the query name, just use the default QUERY1. To run your query, click the RUN icon in the RESULTS GROUP of the Access Ribbon.You should now be presented with a datasheet displaying your query results JOHN JONES 35 and JOE BROWN 39. You have now created and run your first query. The next lesson in the tutorial is about Access 2007 Reports. MSAccess2007 www. neteffect. in 15 Access 2007 Reports Access Reports gravel Data Access REPORTS are a way of displaying and printing information from your database. Developers often use Reports to display the results of a Query, which is what we are going to do next. Like other Access database objects, there a number of ways to create an Access REPORT.We are going use the REPORT WIZARD. Our task is to display the results of QUERY1 which we created in the previous tutorial lesson. 1. Open Report wizardry Begin by selecting the CREATE TAB on the Access Ribbon. because click REPORT WIZARD from t he REPORTS group to open the pop up window. 2. Select Report Data Source and Fields The first page of the Report Wizard is almost identical to that of the Form Wizard. On this page we will select QUERY1 as the DATA SOURCE for the Report. This is done by selecting the query from the list in the TABLES/QUERIES combo box.Then select all fields from QUERY1 by clicking the double arrow to the right of the text box labelled AVAILABLE FIELDS. The field names will now appear in the SELECTED FIELDS text box on the right. Click next to go to the second page of the wizard. MSAccess2007 www. neteffect. in 16 3. Report class Levels Page two of the wizard is where you can add grouping levels if required. So we could, for example, group each person in our report with others of the same age. We dont need to do this for this particular example, but it is certainly a useful feature of speech that we might want to learn more about later.But for now, just click next. 4. Record Sort Order Page three of the wizard is where you determine what order the records are displayed in your report. So for example you might want to display records in ascending order of age. For this example, however, we are going to put your records into alphabetical order for SURNAME and FIRSTNAME. Begin by clicking the top drop down box and selecting SURNAME, and in the box 2 below select FIRSTNAME. This means that records are sorted alphabetically by surname, and if two surnames are the same, the FIRSTNAME is used to determine which record comes first etc.Click Next MSAccess2007 www. neteffect. in 17 5. Report Lay Out This page of the Wizard is where you set the lay out for your report. There are variant lay out options, but in our case we shall keep to the default setting of TABULAR. Click next. 6. Style For style, select Access 2007 from the list and click next. MSAccess2007 www. neteffect. in 18 7. Name of Report Name your report REPORT1 and then click FINISH. Your access report will now be displa yed on the screen, and should look something like this MSAccess2007 www. neteffect. in