Introduction to microsoft access 2013 pdf free. Introduction to Microsoft Access 2013 in pdf
Looking for:
Introduction to Microsoft Access download free tutorial in pdf |
Introduction to microsoft access 2013 pdf free.Access 2013 videos and tutorials
[PDF] Introduction to Microsoft Access free tutorial for Beginners
Other relationship types include one-to- one and many-to-many. One-to-one relationships, while uncommon, can be used to store infrequently used data in another table e. The many-to-many relationship, considered the most complex, is illustrated in the above example.
Each student in the Students table can take many courses in the Courses table and each course can be taken by many students. In cases such as these, a third table, known as a match, junction, or mapping table is needed to connect the other two tables.
For us, the Enrollment table contains a unique entry for each enrollment pairing of a student and course. The Enrollment table, and all match tables, must contain foreign keys that can be matched to the primary keys of the tables that require matching. Student No. The ribbon at the top changes based on the tab selected.
The panel of the left side of the screen acts as a table of contents for the opened project, listing all tables, queries, forms, and reports that are saved in the project. The majority of the interface is devoted to the workspace. This is where tables, queries, forms, and reports appear. Select Import the source data into a new table in the current database and click OK.
Select Delimited and click Next. Click Next. The next screen allows us to change the data types of our imported data. Also, numbers with leading zeros, like our FIPS codes, can be corrupted if stored as integers. Access can create a new field containing sequential integers as primary keys. However, since we will eventually link this data to our other tables, we should select a field that uniquely identifies each entry that will also be listed in at least one of our other tables.
Click on the field name to highlight the column. Then click-and-drag to move the column to the beginning of the table. Right- click on the Counties table tab in the workspace and click Close. On the left end of the Ribbon is the View button, which toggles between Datasheet and Design views when tables are opened.
The table displayed in Design view has one entry for each field in your table and lists the field name and data type. To make this the first column of our table, click on the key to select the row, then click-and-drag it to the top of the list. Looks like Census Tract has the largest land area at 82,, square meters. A query is a set of instructions that you give a program like Access that selects specific data from a table or set of tables based on the parameters you set.
You can then save the query and run it whenever your need to gather information, save the results into a new table, or both. Select all tables by holding Shift and clicking on each one. Click Add then Close. Arrange the tables as I have above by clicking and dragging them via their titles.
Click Create to accept all defaults. By setting these primary-foreign key pairs, we are telling access which fields in each table contain the same information in order to join the tables together. For our purposes, accepting all defaults in the Edit Relationships window will work. In the future, you may want to check the Enforce Referential Integrity box es to ensure linked tables update correctly or change the Join Type to include unmatched entries.
From the Create Ribbon, click the Query Wizard button. The datasheet we see now shows the results of our query, including the census tract name, number of households, and land area. If we want to export this data to a table, we can right-click on the query from the Table of Contents panel and select Export to save it as an Excel file, a CSV, or an Access table within this or any other Access database project. You can also access export options from the Export portion of the External Data Ribbon.
Open the Query Wizard and start a new simple query. This lets us customize more parts of our queries. Select all tables, click Add, and then click Close. Once the tables are loaded, you can rearrange them by clicking and dragging on their title bars and resizing them by clicking and dragging their corners.
If we ran this query now, we would have one row per entry in the income table, since we have not told Access to group or summarize our data. Right click in any cell in the Query Design info table and click Totals. This will group the census tract household counts by county and sum that information. Click Datasheet View or Run to view the results. It should be the same as the previous Query Wizard query. Return to Design View. Instead of a total household count, what if we wanted to know the household density per sq.
Copy in the expression shown above. Essentially, this is standardized coding that selects a specific subset of data. This allows you to see what our GUI-built or Query Wizard-built queries get translated into so that Access can select our requested data. By learning SQL, you can further customize your queries. Click on the Userid column heading 3. Right click on the selected column headings and again choose Hide Fields If you want to change the order of the fields on the screen, you can either use cut and paste or, more simply, drag and drop.
Both these techniques should be familiar to the Microsoft Office user. To list the students starting with their full name including title in the correct order: 5. Click on the Surname column heading to select the column 6. Move the mouse cursor back into the column heading, hold down the mouse button then with the button still depressed drag the column to the right to a position immediately before the Year field 7.
Release the mouse button to drop the field in its new position Note: you are only changing the screen display - the data is still stored in its original order. Finally, you might want to print your list. First, it's a good idea to preview it: 8. Normally you would now print your list, but here: 9. Click on [Close Print Preview] on the far right of the new Print Preview tab to turn off the preview To redisplay any of the hidden fields: Right click on any column heading and choose UnHide Fields You can in fact save a filter as a query by turning on the advanced filter option.
This can be a useful aid in designing a query. As an introduction to queries, save the current filter students born in May who came in a particular year : 1. A Filter Design pane appears. It looks complicated but Access has done the hard work for you. This is very similar to the Query Design pane, which you will be using next. Examine how the criteria have been set up. Click on the [Save] button — the Save As Query dialog box appears 3. Close the students table - don't save the changes to the design click on [No] Tip: The simplest way to redisplay all the fields if some are hidden is to close the table without saving the changes to its design.
When you reopen it, it will appear in its original format. End by closing the query - click on its [Close] button Once a filter has been saved as a query, it's easy to modify its design if necessary. Using a filter in this way is straightforward but a little limited. To do more complicated selections you have to use a Query. Queries also offer various other facilities, including sorts within sorts.
In fact you may always want to view your data through a query — here, for example, you might want to see the students listed alphabetically by Surname then by FirstName.
The Select Query pane may look a little confusing, but in fact it's very simple to use. The cursor should be flashing in the Field: row in the lower part of the screen waiting for you to define the fields to be displayed. Here in a query, you can: 4. Click on the list arrow on the right of the Field: cell and choose students. If you were to run the query as it stands, you would see the data in its original unsorted format i.
Click on the list arrow in the Field: cell in the second column and choose Surname 6. Repeat step 5 in the third column but choose FirstName 7. Move down to the Sort: third row and type a for Ascending in both columns 2 and 3 8.
In the Show: fourth row, untick the check boxes in both columns 2 and 3 you can click anywhere in the cell - if you don't, the names will appear twice as they are already included in students.
To carry out the query, click on the [Run] button or you can use [View] to move from Design View to Datasheet View You should find that the students are now listed in their correct order look at the Smiths and that this query should be used whenever you want to look at the complete set of data. This time your new query will be making use of the fact that you already have the students sorted by name a query can be based either on a table or another query : 1.
Set the Field: in the first column to Surname and that in the second column to FirstName You now need the Hall field in a separate column to set up the selection criteria. Another way to fill up a field is to double click on it in the field list in the top half of the Select Query window. You can try this next: 5. Double click on the Hall — it should be added to the next empty column in the query 6.
Move down to Criteria: in column 3 and type the name of the required hall - e. Wessex 7. To carry out the query, click on the [Run] button on the far left of the Ribbon or use [View] to move from Design View to Datasheet View or right click on the Query design and choose Datasheet View One difference between a query and a filter is that you can save it directly for future use. Click on the query's [Close] button 9. Now try re-running the query: With a query, however, you can change the criteria each time you run it by making it a parameter query.
The design is very similar to what you have already seen except that instead of setting a fixed criteria, Access asks for the information at run time. Modify the Hall query to do this: 1. With the Hall query still open, click on the [View] button to change to the Design View 2. Type in a new criteria saying: [Which Hall? Click on the [Run] button or on [View] to switch to Datasheet View 5.
When asked the question Which Hall? Wantage 6. Here, however, to run the query again: 7. Click on the [View] button to change to the Design View 8. Click on the [Run] or [View] button again 9. Type in the name of a different hall - e. This mechanism is used when you look up a book in the Library, for example. More Complex Queries Next, try some more complicated queries. What if you want to have an alternative criteria in a parameter query? For example, you might want a list of students living in either one hall or another.
To do this, you have to set up criteria on two different lines. Click on the [View] button to change to Query Design 2. In the second line of the Criteria: in column 3, type: [or? Click on the [Run] button or on [View] to switch to Datasheet View 4. When asked Which Hall? When asked or? You now have the students from both halls - [Close] the query, saving the new design You have seen how to match values in a query but you can also use criteria such as greater than, less than, not equal to, between one value and another, or matching part of a field.
For example, how do you set up a query to pick out just the female students? The answer is that you can use a special notation called Like. For the female students: 1. Set the Field: in the first column to SortedStudents. Click on the [Run] button to run the query - or switch to Datasheet View To set up a second condition on this subset of data e. Whereas alternative conditions are set up on different lines, simultaneous conditions must be set up on the same criteria line: 8.
Click on the [View] button to move back to Design View 9. Set the Field: in the third column to Tutor Turn off Show: by unticking the box In Criteria: in the third column, top line, type: [Which Tutor? Click on the [Run] button to run the query - or switch to Datasheet View When asked Which Tutor? One fault with the original example was that the students' names first name and surname were printed in separate columns.
In a query you can calculate a new field, joining these together: 1. Though both appear to work, plus signs can occasionally cause problems.
Set the Field: in the second column to Hall and in the third column to StudNo 5. In the fifth column, repeat step 5 but set the Field: to FirstName 7. Click on the [Run] button or on [View] for Datasheet View to run the query 8. Double click on the dividing lines between the column headings to widen the FullName column 9.
To see how it was calculated Open the Students table and click on the [View] button to move to Design View This means it has not created an active hyperlink i.
This appears if you right click in the Field: row or, indeed in Criteria: and choose Build You then have access not just to the field names but also to built-in functions. Even more importantly, a Criteria: can be set to pick up values held on forms. Forms offer a friendly way of viewing the data in that they show a single record at a time.
Forms can also be used to display results from queries. They are also used to facilitate data input.
A form has already been set up for the students table: 1. The buttons work as they did before, allowing you to move around, add new records, delete records, filter, sort and search etc. Use this form to type in your own information again: 5. Fill in the fields with your own information, as you did before 7. In the Title field, select the required title using the list arrow 8. In the Hall field, start typing the name of the hall and watch Access select from the list of values 9.
The Tutor field also has a list arrow attached Tip: As was mentioned earlier, when you insert a picture file in Access it often appears as an icon. Fortunately, Access has wizards to do most of the work for you. To modify a form, you view the form design: 1. Click on the [View] button or right click and choose Design View Note that Access provides you with three new tabs on the Ribbon to help you with the design.
You are not going to modify the design of this form - you will see how to later, in Part 2 of these notes. For the moment: 2. Click on the [View] button to move back to Form View Filter by Form If you want to select a subset of the records using a filter, you can still use the [Selection] button as before. This isn't very convenient, however, if you want to base the filter on information not displayed on the current form. Here, you might want to search for students living in Wessex Hall which isn't on the current record.
You can do this using the [Filter] button as before, but Access also provides a special filter for use with forms. Click on the [Advanced] button and choose Filter by Form 2. Click on the list arrow attached to the Hall field and select Wessex 3.
Again, click on [Advanced] and choose Filter by Form — your previous filter is shown 6. Click on the list arrow attached to the Title field and select Mr 7. Now click on the Or tab at the bottom of the filter pane for a second filter 8. Select a Title of Miss and a Hall of Windsor 9.
Close the form by clicking on its [Close] button Note that you can also use [Filter by Form] on a table a blank record appears for you to type in the criteria. This allows you to create and store reports which can then be printed. The mouse button acts as a zoom facility - position the magnifying glass over a particular piece of text and click on the mouse button to magnify it or use Zoom in the bottom right corner of the screen Note: if you use Zoom, this will alter the magnification level setting for the mouse button.
Right click on the blank area of the report and choose Design View Note how similar Report Design and Form Design are - you have matching Design tabs on the Ribbon to draw the various components. Again, you will see how this is done later.
Reports can also be viewed in Report View: 5. Click on the [View] button to see the report, right click on the report and choose Report View 6. You will be typing in a couple of records and then retrieving some more from a file. Designing the Table As much as possible of a table design should be done in advance on paper. Here, however, you will be creating the table on the screen so that you can see the stages as they are implemented.
The table you are going to create contains information about the Halls of Residence at the University. In Part 3 of these notes, you will see how to link this information to the data in the students table: 1.
As you enter data into this column, another Add New Field appears and the first column is relabelled Field1. Access recognises the type of data entered and automatically allocates a Data Type to it e.
Click on the [Table Design] button 3. Sign Up for Free Already have an account? Sign In. Open in App Create free Account. Search for:. Get Pass Pass. Home » Computer Awareness. Download Post as PDF. Continue Reading in App. Next Post. More from testbook. No more posts found!
Comments
Post a Comment