Microsoft® Access® Step by Step [Book]

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Access closes the table, which is now listed in the Tables group on the Navigation bar. The database now contains one table. You can delete a table by right-clicking it, clicking Delete, and then confirming the deletion in the message box that appears. You can also delete a table by selecting it in the Navigation bar and then clicking the Delete button in the Records group on the Home tab or pressing the Delete key.

In the Navigation pane, click the Customers table to select it. On the Home tab, in the Clipboard group, click the Copy button. Then click the Paste button. The Paste Table As dialog box opens. If you need to create a table that is similar to an existing table, it is sometimes easier to customize a copy than to create it from scratch.

In the Table Name box, type Employees. In the Paste Options area, click Structure Only to capture the fields from the Customers table but none of the customer information. Then click OK. The new Employees table appears in the Navigation pane.

Double-click Employees to open it in Datasheet view so that you can view its fields. Then close the table again. On the Create tab, in the Tables group, click the Table button. The More Fields gallery appears. The Quick Start fields are at the bottom of the More Fields gallery. If necessary scroll to the bottom of the gallery, and then under Quick Start, click Name. Access inserts ready-made LastName and FirstName fields. Repeat steps 18 and 19 to add the Address fields from the Quick Start list.

Close the table, saving it with the name Shippers when prompted. For example, an Orders table should not include information about the customer placing each order, for two significant reasons. First, if the same customer orders more than once, all his or her information has to be repeated for each order, which inflates the size of the table and the database.

Second, if the customer moves, his or her address will need to be updated in the record for every order placed. The way to avoid this type of problem is to put customer information in a Customers table and assign each customer a unique identifier, such as a sequential number or unique string of letters, in the primary key field.

Then in the Orders table, you can identify the customer by the unique ID. If you need to know the name and address of the customer who placed a particular order, you can have Access use the unique ID to look up that information in the Customers table. The process of ensuring that a set of information is stored in only one place is called normalization. The topic of normalization is beyond the scope of this book. If you need to design a database that will contain several tables, you should search for Database design basics in Access Help to learn more about the normalization process.

For example, if you want to look up a phone number but the names and phone numbers are several fields apart, you will have to scroll the page to get the information you need. You might want to rearrange or hide a few fields to be able to simultaneously see the ones you are interested in.

You can manipulate the columns and rows of an Access table without affecting the under- lying data in any way. You can size rows and size, hide, move, and freeze columns. You can save your table formatting so that the table will look the same the next time you open it, or you can discard your changes without saving them. Open the MyTables database, and then follow the steps.

In the Navigation pane, double-click the Customers table to open it in Datasheet view. In the field name row, point to the right border of the Street field name, and when the pointer changes to a double-headed arrow, drag to the right until you can see all of the street addresses. Double-click the right border of any column that seems too wide or too narrow to adjust the column to fit its contents. Point to the border between any two record selectors, and drag downward.

When you release the mouse button, Access increases the height of all rows in the table. The Row Height dialog box opens. You can set the rows to the precise height you want.

Access resets the height of the rows to the default setting. Click anywhere in the FirstName field. Then in the Records group, click the More button, and click Hide Fields. The FirstName field disappears, and the fields to its right shift to the left. You can select adjacent fields by clicking the field name of the first one, holding down the Shift key, and then clicking the field name of the last one.

The two fields and any fields in between are selected. To restore the hidden field, in the Records group, click the More button, and then click Unhide Fields. You can select and clear check boxes to control which fields are visible. Access redisplays the FirstName field. If you can see all of the fields in the table, for the purposes of this exercise, adjust the size of the program window until some of the fields are no longer visible.

With the three columns selected, click the More button in the Records group, and then click Freeze Fields. Scroll the page to the right until the Phone field is adjacent to the LastName field.

The first three columns remain in view as you scroll. In the Records group, click More, and then click Unfreeze All Fields to restore the fields to their normal condition. Click the Phone field name to select that field. Then drag the field to the left, releasing the mouse button when the thick black line appears to the right of the LastName field.

Close the Customers table, clicking Yes to save the changes you have made to the column widths and order. If you see a warning that this action will clear the Clipboard, click Yes.

Refining Table Structure Although you can create the structure of a database in Datasheet view, some structural refinements can be carried out only in Design view.

When you are familiar with tables, you might even want to create your tables from scratch in Design view, where you have more control over the fields.

You can open a new table in Design view by clicking the Table Design button in the Tables group on the Create tab. When you open an existing table in Design view, the tabbed page shows the underlying structure of the table. The table design page. You can then insert a row above the selected one, delete the row thereby deleting the field , or drag the row up or down to reposition its field in the table. The selector also identifies the primary key field of the table by displaying the Primary Key icon a key with a right-pointing arrow.

If you want to designate a different field as the primary key, select the new field, and click the Primary Key button to toggle it on. You can edit the names by using regular text-editing techniques. You can add a new field by typing its name in the first empty cell in this column. By default, the ID field in a new table is assigned the AutoNumber data type, and all other fields are assigned the Text data type.

With the exception of fields with the OLE Object and Attachment data types, you can change the type of any field by clicking its Data Type entry, clicking the arrow that appears, and clicking a new data type in the list. The list of data types.

Different properties are associated with different data types. They can determine such things as the number of characters allowed in a field, the value inserted if the user doesn’t type an entry, and whether an entry is required.

Properties can also assess whether an entry is valid and can force the user to select from a list of values rather than typing them with the risk of errors. All fields, no matter what their data type, can be assigned a Caption property that will appear in the place of the field name in tables or in other database objects.

For example, you might want to use captions to display the names of fields with spaces, such as First Name for the FirstName field. In the Navigation pane, right-click the Employees table, and then click Design View. Access opens the table with its structure displayed. Because you created this table by copying the Customers table, you need to make some structural changes.

In the Description column, type Unique identifying number. In the empty row below the Phone field, click the Field Name cell, and type Birthdate. Then click the Data Type cell. Access assigns the default Text data type to the new field. The properties in the Field Properties area at the bottom of the design page change to those that are appropriate for this type of field. The properties for the Text data type. In the box to the right of Field Size, double-click , and type You are specifying that this field can contain no more than 10 characters.

Click the State field. You have changed the Field Size and Caption properties of the State field. On the Design tab, in the Views group, click the View button to switch to Datasheet view.

Access tells you that you must save the table before leaving Design view. In the message box, click Yes to save the table. Access saves the table and displays it in Datasheet view. With the table displayed in Datasheet view, click the LastName field name. A new field called Field1 that has the Text data type is inserted to the right of the LastName field. Then in the list that appears, you can click the data type you want. With Field1 selected, type Title, and press Enter. Click the Title field name.

Then in the Properties group, in the Field Size box, click to select it, type 50, and press Enter. The first record of the Employees table. Retain the MyTables database for use in the last exercise.

Creating Relationships Between Tables In Access, a relationship is an association between common fields in two tables. You can use this association to link the primary key field in one table to a field that contains the same information in another table. The field in the other table is called the foreign key. For example, if customer accounts are assigned to specific sales employees, you can establish a relationship by linking the primary key EmployeeID field in the Employees table with the foreign key EmployeeID field in the Customers table.

Each customer account is assigned to only one employee, but each employee can manage many customer accounts, so this type of relationship—the most common—is known as a one-to-many relationship.

Similarly, if every order is associated with a customer, you can establish a relationship by linking the primary key CustomerID field in the Customers table and foreign key CustomerID field in the Orders table. Each order is placed by only one customer, but each customer can place many orders. So again, this is a one-to-many relationship. However, you might use two related tables instead of one to break up a table with many fields, or to track information that applies to only some of the records in the first table.

You might see this relationship in a database that contains Products, Orders, and Order Details tables. The Products table has one record for each product, and each product has a unique ProductID. The Orders table has one record for each order placed, and each record in it has a unique OrderID.

Products and Orders each have a one-to-many relationship with Order Details. Products and Orders therefore have a many-to- many relationship with each other. In plain language, this means that every product can appear in many orders, and every order can include many products.

The most common way of creating a relationship between two tables is to add the tables to the Relationships page displayed when you click the Relationships button in the Relationships group on the Database Tools tab. You then drag a field in one table to the common field in the other table and complete the relationship definition in the Edit Relationships dialog box.

In this dialog box, you are given the opportunity to im- pose a restriction called referential integrity on the data, which means that an entry will not be allowed in one table unless it already exists in the other table. After you have created a relationship, you can delete it by deleting the line connecting the tables on the Relationships page. You can clear all the boxes from the page by clicking the Clear Layout button in the Tools group on the Relationship Tools Design contextual tab.

However, relationships are what make relational databases tick, and Access provides a number of fairly complex mechanisms to ensure the integrity of the data on either end of the relationship.

On the Create tab, in the Tables group, click the Table button to create a new table. To the right of Click to Add, click the arrow, and in the data type list, click Number.

Repeat this step to create a second field with the Number data type. Double-click Field1, and type CustomerID. Then double-click Field2, and type EmployeeID. Each order in the Orders table will be placed by one customer and will be handled by one employee. Close the Orders table. On the Database Tools tab, in the Relationships group, click the Relationships button.

The Show Table dialog box opens so that you can indicate the tables for which you want to create a relationship. With Customers selected on the Tables page, click Add. Then double-click Orders, and click Close. Access displays the Relationships page and adds a Relationship Tools contextual tab to the ribbon.

The two boxes list all the fields in their respective tables. In the Customers field list, click CustomerID, and drag it down and over CustomerID in the Orders field list, releasing the mouse button when two little boxes, one containing a plus sign, appear below the pointer.

The Edit Relationships dialog box opens. At the bottom of the dialog box, Access indicates that this will be a one-to-many relationship. Select the Enforce Referential Integrity check box, and then click Create. Access creates the link between the primary key in the Customers table and the foreign key in the Orders table, and a line now connects the two field lists on the Relationships page.

The symbols at each end of the line indicate that each Customer ID value appears only once in the Customers table but can appear many times in the Orders table.

On the Design contextual tab, in the Relationships group, click the Show Table button. Then in the Show Table dialog box, double-click the Employees table, and click Close. Access adds a box listing all the fields in the Employees table to the Relationships page. On the page, drag the title bars of the three field lists to arrange them so that they are side by side and equidistant. After Access draws the relationship line between the primary key and the foreign key, close the Relationships page, clicking Yes to save its layout.

Open the Orders table. Then in the CustomerID field of the first record, type 11, and click below the record to complete it. Access displays a message box telling you that you cannot add the new record to the table. Click OK. Then change the value to 1, and click below the record to complete it. This time, Access accepts the value because there is a record with the value 1 in the primary key CustomerID field of the Customers table.

You can also set the data type and certain properties. Caution: changing some properties might affect the data. Like forms, reports give people easy access to the information stored in a database. Reports are used only to view information. Reports can be previewed on the screen, but they are usually printed. Reports usually present summaries of larger bodies of information. For example, your database might hold detailed information about thousands of orders. If you want to edit those orders or enter new ones, you can do so directly in the table or through a form.

When you are designing a report, it is important to consider the point you are trying to make, the intended audience, and the level of information they will need. Creating Reports by Using a Wizard You can divide the content of an Access report into two general categories: information derived from records in one or more tables, and everything else. The everything else category includes the title, page headers and footers, introductory and explanatory text, and any logos and other graphics.

Just as you can create a form that includes all the fields in a table by using the Form tool, you can create a report that includes all the fields by using the Report tool, which is located in the Reports group on the Create tab. But such a report is merely a prettier version of the table, and it does not summarize the data in any meaningful way. You are more likely to want to create a report based on only some of the fields, and that is a job for the Report wizard.

So the first step in creating a report is to consider the end result you want and what information you need to include in the report to achieve that result. After you provide that information, the wizard creates a simple report layout and adds a text box control and its associated label for each field you specify. For example, you might want to use a Products table as the basis for a report that groups products by category.

When you give the grouping instruction to the wizard, it first sorts the table based on the category, and then sorts the products in each category.

In the space at the top of each group called the group header , the wizard inserts the name of the category. Then follow the steps. On the Create tab, in the Reports group, click the Report button.

Access creates a report based on all the fields in the Categories table, displays the report in Layout view, and adds four Report Layout Tools contextual tabs to the ribbon.

The default report created by the Report tool. This is not the report we want, so close the Categories report, clicking No when prompted to save it. On the Create tab, in the Reports group, click the Report Wizard button.

The Report wizard starts. The Available Fields box now lists the fields in the Products table. The first page of the Report wizard with the correct table selected. You can save yourself the effort of rearranging the fields in the report by entering them in the desired order in the wizard.

At the bottom of the page, click Next. The wizard asks whether you want to group the records. When you group by a field, the report inserts a group header at the top of each group of records that have the same value in that field.

In the field list on the left, double-click ProductName. In the preview pane on the right, the wizard moves ProductName into the group header area to show that records will be grouped by this field.

In the lower-left corner of the page, click Grouping Options. The Grouping Intervals dialog box opens. You can refine the grouping specification in this dialog box. Display the Grouping intervals list, click 1st Letter, and then click OK.

The group header now indicates the grouping interval you have assigned to the grouping field. Click Next. The wizard asks how you want to sort and summarize the records.

You can sort by up to four fields, each in ascending or descending order. The only numeric field in this report is UnitsInStock, and it is not appropriate to summarize that field. Click the arrow to the right of the 1 box to display a list of fields, and click ProductName. Then click Next. The wizard asks which of three layouts and which orientation you want for this report. The preview on the left shows the effect of the options on the right.

In the Layout area, click each option in turn to see a preview in the report thumbnail to the left. When you have finished exploring, click Outline.

With Portrait selected in the Orientation area and the Adjust the field width so all fields fit on a page check box selected, click Next. The wizard prompts you to supply a title for the report. In the title box, type Alphabetical List of Products, and then with Preview the report selected, click Finish.

Access creates the report and displays it in Print Preview. The new report in Print Preview. Page through the nine-page report, noticing how it is arranged. Then close it. As with forms, the report consists of text box controls that are bound to the corresponding fields in the underlying table and their associated labels. For simple adjustments, it is easier to work in Layout view, where you can see the layout with live data, making the process more intuitive.

For example, Access informs you if a report is wider than the page it will be printed on. Error checking is turned on by default. If you want to turn it off, display the Backstage view, and click Options to open the Access Options dialog box. In the left pane, click Object Designers, clear the error-checking check boxes at the bottom of the page, and then click OK. You will also apply a simple rule that formats values differently if they meet a specific criterion.

Open the GardenCompany05 database, and then follow the steps. The pointer changes to a magnifying glass with a plus sign in it. Click the previewed page once to zoom in. The current zoom level appears to the left of the slider. On the View Shortcuts toolbar, click the Layout View button. Access adds four Report Layout Tools contextual tabs to the ribbon.

The controls that are bound to fields in the Products table are hidden so that you can concentrate on the group header controls. Below the title, click ProductName by 1s, and press the Delete key. The label is removed from all the group headers.

Click the control containing A, and drag it to the left edge of the header. When you release the mouse button, all the corresponding controls move to the corresponding location in their own group headers.

When the shadow box is positioned where you want it, click away from the control. Point to the right border of the selected A control, and when the pointer changes to a double-headed arrow, drag to the left until the control is just big enough to hold its contents. Again, all the corresponding controls assume the new size. It is easier to work with the controls in the group header when the report details are hidden.

On the Design tab, in the Tools group, click the Property Sheet button. The Property Sheet opens. On the Format page of the Property Sheet, in the Top property box, type 0. Then close the Property Sheet. In the group header, the letter control and label controls are now aligned 0.

The numbers in the Units In Stock column are right-aligned. Click the first text box control under the Units In Stock label, and on the Format contextual tab, in the Font group, click the Center button.

On the Design contextual tab, in the Themes group, click the Themes button, and in the gallery, click the Austin thumbnail. Although nothing much appears to change, the report takes on the color scheme and font scheme assigned to the selected theme.

Inside the shaded area of the report header, but away from the title, click a blank area. When you created this report, alternate group headers were shaded with the same color as alternate data rows. This coloring confuses rather than clarifies the report structure.

Click outside the dotted border to the left of the first group header. In the Control Formatting group, click the Shape Fill button, and in the palette, click a light brown color.

The entire group header is shaded except the alphabet controls A, B, C, and so on that you moved earlier. If you wanted to shade them as well, you could select one of them and repeat step 17 to apply the light brown fill. Click outside the dotted border to the left of the first row of data in the report, and remove the alternate row color of the data rows.

Then click the white space above the report header to see the result. Only the backgrounds of the report header and group headers are now colored. Click any control, and then in the Selection group, click the Select All button. In the Font group, click the Font Size arrow, and then click 9. Select the controls in the group header, and make them bold and dark green.

Scroll down the report, noticing that a few of the values in the Units In Stock column are 0. We want these values to stand out in the report to remind buyers that it is time to order more of these products. Click any control in the Units In Stock column. Then in the Control Formatting group, click the Conditional Formatting button. The Conditional Formatting Rules Manager dialog box opens. Click New Rule. You can create rules that compare the current field value to a specific value or to other values in the same field.

With Check values in the current record or use an expression selected as the rule type, in the Format only cells where the area, click the arrow for the second box, and click less than. Then in the third box, type 1. In the bottom area, click the Bold button, and change the Font color setting to red.

Values that are less than 1 will be bold and red. Then scroll down the report again, noticing that all the 0 values are now bold and red. Close the report, clicking Yes to save your changes to its design. Previewing and Printing Reports Using Print Preview to preview Access reports is very similar to using this view in other Microsoft Office programs.

When previewing reports, you will want to pay special attention to how the pages break. In a grouped report, you can control whether group headings are allowed to appear at the bottom of a page with no data and whether groups are allowed to break across pages.

You can make changes to the setup of your report pages from the Page Setup contex- tual tab in Layout view or from the tab displayed when you switch to Print Preview. When you are ready to print, you click the Print button on the Print Preview tab of the rib- bon to display the Print dialog box. You can also display the Print page of the Backstage view and then print one copy of the report with the default print settings by clicking the Quick Print button.

On the page navigation bar at the bottom of the window, click the Next Page button repeatedly to view each page of this report. Because of the changes you made to the report in the previous exercise, the report is now six pages. Several of the groups start on one page and continue on the next page. The group at the top of this page is a continuation of one that started on the previous page.

The Group, Sort, And Total pane opens at the bottom of the report page. You can use this pane to quickly add grouping and sorting levels and set related properties. These are the current grouping settings. Click the do not keep group together on one page arrow, and in the list, click keep whole group together on one page. Switch to Print Preview, and page through the report. Now none of the groups is broken across pages. This supremely organized reference is packed with hundreds of timesaving solutions, troubleshooting tips, and workarounds.

It’s all muscle and no fluff. Discover how the experts tackle Access — and challenge yourself to new levels of mastery! Master essential data management and design techniques Import and link to data from spreadsheets, databases, text files, and other sources Use action queries to quickly insert, update, or delete entire sets of data Create custom forms to capture and display data Design reports to calculate, summarize, and highlight critical data–and learn advanced techniques Automate your application with macros and Visual Basic for Applications VBA Use Access Services to extend your database application to the Web Try out the sample client and web database applications in both bit and bit versions A Note Regarding the CD or DVD The print version of this book ships with a CD or DVD.

The sample client and web database applications are provided in both bit and bit versions. Note that while we provide as much of the media content as we are able via free download, we are sometimes limited by licensing restrictions.

For customers who purchase an ebook version of this title, instructions for downloading the CD files can be found in the ebook. Experience learning made easy-and quickly teach yourself how to create dynamic presentations with PowerPoint Topics include creating great-looking slides using templates or your own designs; creating sophisticated charts and diagrams; using animation, sound, and other special effects; creating presentations simultaneously with others over the Web; delivering presentations; and other core topics.

The smart way to build applications with Microsoft SharePoint Designer — one step at a time! Experience learning made easy — and quickly teach yourself how to create SharePoint applications and workflows with Microsoft SharePoint Designer With Step by Step, you set the pace — building and practicing the skills you need, just when you need them!

Design solutions to meet your business scenario — without code Customize lists, libraries, and SharePoint site pages Use Web Parts to display, edit, and modify list item data Access data from a variety of external sources Create workflows to automate business processes Use master pages and CSS to control how sites work Your Step by Step digital content includes: All the books practice files — ready to download and put to work.

Fully searchable online edition of this book — with unlimited access on the Web. Experience learning made easy—and quickly teach yourself how to create professional-looking business and technical diagrams with Visio With Step by Step, you set the pace—building and practicing the skills you need, just when you need them!

Build a variety of charts and diagrams with Visio templates Draw organization charts, floor plans, flowcharts, and more Apply color, text, and themes to your Visio diagrams Use Visio shapes to link to, store, and visualize data Collaborate on diagrams with Microsoft SharePoint Create custom diagrams with your own shapes and templates Your Step by Step digital content includes: All the book’s practice files—ready to download and put to work.

Fully searchable online edition of this book—with unlimited access on the Web. Free online account required. The smart way to learn Microsoft Office Professional —one step at a time!

Determine the best Office tool for specific tasks Use Office efficiently on touch-enabled devices Create attractive documents, publications, and presentations Manage your e-mail, calendar, meetings, and communications Put your business data to work with Excel and Access Organize and share your notes and ideas with OneNote.

The quick way to get started with Microsoft Office ! This is learning made easy. Jump in wherever you need answers–brisk lessons and colorful screenshots show you exactly what to do, step by step. Covers Word, Excel, PowerPoint and Outlook Format documents for visual impact Quickly prepare personalized email messages and labels Build powerful workbooks for analysis and reporting Analyze alternative data sets with Quick Analysis Lens, Goal Seek, and Solver Prepare highly effective presentations Strengthen your presentations by adding tables and graphics Organize your email, scheduling, and contacts Look up just the tasks and lessons you need.

Experience learning made easy-and quickly teach yourself how to manage your projects with Project Topics include building a project plan and fine-tuning the details; scheduling tasks, assigning resources, and managing dependencies; monitoring progress and costs; keeping projects on track; communicating project data through Gantt charts and other views. The smart way to learn Microsoft SharePoint —one step at a time! Experience learning made easy—and quickly teach yourself how to boost team collaboration with SharePoint Get more done quickly with Office Jump in wherever you need answers – brisk lessons and colorful screenshots show you exactly what to do, step by step.

Covers Word, Excel, PowerPoint and Outlook Format Word documents for maximum visual impact Build powerful, reliable Excel workbooks for analysis and reporting Prepare highly effective PowerPoint presentations Use Outlook to organize your email, calendar, and contacts Includes downloadable practice files.

The expert guidance you need to get the most out of Access Get the Access information you need to succeed with this comprehensive reference. If this is your first encounter with Access, you’ll appreciate the thorough attention to database fundamentals and terminology. If you’re familiar with earlier versions, you can jump right into Access enhancements such as the new Access user interface and wider use of XML and Web services.

Teach yourself Visual C one step at a time. Ideal for developers with fundamental programming skills, this practical tutorial features learn-by-doing exercises that demonstrate how, when, and why to use the features of the C rapid application development environment.

You’ll learn how to use Microsoft Visual Studio and Microsoft. NET Framework 4. You’ll also delve into data management technologies and Web-based applications. Experience learning made easy—and quickly teach yourself how to create impressive documents with Word With Step By Step, you set the pace—building and practicing the skills you need, just when you need them!

Experience learning made easy—and quickly teach yourself how to build database solutions with Access Advance your everyday proficiency with Access And earn the credential that proves it! Demonstrate your expertise with Microsoft Access! Hands-on experience with the technology is required to successfully pass Microsoft Certification exams. What you need, when you need it!

Need answers quickly? Microsoft Office on Demand provides those answers in a visual step-by-step format. We will show you exactly what to do through lots of full color illustrations and easy-to-follow instructions. The quick way to learn Microsoft Excel ! Get more done quickly with Microsoft Excel Jump in wherever you need answers—brisk lessons and detailed screenshots show you exactly what to do, step by step.

This supremely organized reference is packed with hundreds of time-saving solutions, troubleshooting tips, and workarounds. Discover how the experts use VBA to exploit the power of Access—and challenge yourself to new levels of mastery! A friendly, step-by-step guide to the Microsoft Office database application Access may be the least understood and most challenging application in the Microsoft Office suite.

This guide is designed to help anyone who lacks experience in creating and managing a database learn to use Access quickly and easily. In the classic For Dummies tradition, the book provides an education in Access, the interface, and the architecture of a database. It explains the process of building a database, linking information, sharing data, generating reports, and much more.

As the Microsoft Office database application, Access may be the least understood and most challenging part of the Office suite Access For Dummies walks newcomers through building and using their first database Covers linking information in a database, setting relationships, modeling data, and building tables Explores how to extract data from Access and get specific answers, create forms, and export data in reports A section for more experienced users looks at analyzing errors and creating an interface Fully updated for the newest version, Access For Dummies gets new Access users up to speed and helps veterans get the most from the Office database application.

 
 

Microsoft office access 2010 step by step pdf free. Microsoft Access 2010 Level 1 in pdf

 
Download/Read Microsoft Access Step by Step By Joyce Cox Here exchanging data with other databases and Microsoft Office documents;. Download free Microsoft Access An Essential Guide course material and training tutorial, PDF file on 25 pages. Access is a powerful Database application that allows users to produce tables, forms, queries and reports within a datbase it can also contain calculations.

 

MICROSOFT ACCESS STEP BY STEP GUIDE – PDF Free Download.

 

On the screen where Access is asking you about a Primary Key you should ensure that you select the option Let Access add primary key. This adds a new field called ID to the table. In the Import to Table: box enter tblkites. NOTE: This is a meaningful table name. The tbl shows you that it is a table and the Kites gives an idea of what kind of data is being held.

Click on to import the data and then to close the wizard. Double click on tblkites to display the imported information which should look like this: tblkites containing the imported. Changes to the field types, or other properties, can be made from the Home tab. In the Views section, click on the Design View icon. The task instructed you to set the Size field to 1 decimal place.

You can check this by clicking the left mouse button in the Size field and viewing the number of Decimal Places in the General tab at the bottom of the window. As you can see this is not set to 1 decimal place but set to Auto. Click on the cell containing Auto and use the drop-down list to set this to 1 decimal place. Use the same method to set the Price field which is currency data type to 2 decimal places.

To change the Boolean field so that it displays Yes or No, click in the Stock Item field and in the General tab select the Format cell. Save the database for later use by clicking the symbol. Open your Kites Table in Design View. The task asks you to take a screenshot of the Field Names and Data Types used within the table. To do this, simply press PrtScn on the keyboard. Open up an empty Microsoft Word document and then click Paste.

Your finished screenshot should look something like this: 5. Print a copy of your screenshot. Q37 Save the data.

Inserting new records – How to do it: 1. Double click on tblkites to view the records. The asterix indicates that this row is where new records are input. New records inserted here 3. Enter the 3 new records as specified in task It is vital that your data entry is EXACTLY the same as the information stated in the question or you will run into problems when you come to search the database later in the exam.

Contains a new field called Order which is calculated at run-time. This field will calculate the Price multiplied by 3 2. Has the Order field set as currency with 2 decimal places 3. Shows only the records where Number is less than 2 and Stock item is Yes 4. Shows all the fields and their labels in full 5. Fits on a single page 6. Has a page orientation of landscape 7.

Sorts the data into ascending order of Make with Airush at the top 8. Calculate the total value of kites to be ordered and: o Shows this total value at the bottom of the Order column o Formats this total value to currency with no decimal places o Has the label Total order value for the total value 9.

Include the heading Kites we need to restock at the top of the page This task is the most difficult of them all. It requires you to produce a report. However before you can make the report you must create something called a query. What is a query? A query is a way to search through the data stored in the database to produce meaningful results. In this question the query needs to search the database in 2 ways: 1. Includes a field called Order which is calculated at run-time and multiplies the Price field by 3.

What is a report? A report is a method we use to display our information in the clearest way possible. Reports are essentially the output of the database. The report in this question only wants us to display information that meets certain criteria and, therefore, we also need to create a query. In short: Any tasks which sound like you need to search for information are tackled within the query.

Any tasks which sound like they are talking about the display of the information should be tackled within the report. Creating the query How to do it: 1. Click Create and then Query Wizard.

On the next screen, you should make sure that tblkites option is selected. Use the arrows to move the fields from the Available Fields: window into the Selected Fields: window. Select the Detail show every field of every record option then press Next.

If the task required a summary of data then you would choose Summary 5. Choose a name which suits the task. I suggest Kites Order Query. Select the Modify the Query Design option. This lets us create our searches. Click Finish.

This takes us to the Query Design screen and from here we can tell Access which data we would like to search for: Query Design P a g e This field will calculate the Price multiplied by 3 Calculated fields only work during run-time.

This means that the calculation is made as the query is activated or ran. In query design view find the Stock Item field. Click the mouse cursor into the empty field to the right of Stock Item.

The Order field will store the result of the [Price] fieldmultiplied by 3. Breakdown of what is happening here: This tells Access what to name the new field This tells Access which current field to use in the calculation P a g e 18 This tells Access which mathematical calculation to use.

Run the query by clicking the Datasheet View button. Your query result will be displayed with a calculated field called Order that contains the Price field multiplied by 3. Order field multiplies the contents of the Price field and displays P a g e Has the Order field set as currency with 2 decimal places 1. To do this, right click the query and then select Design View.

Highlight the Order field then right click and select Properties. In the Properties Sheet change the Format to Currency by using the drop-down list.

Run the query in Datasheet View and check to make sure that your Order field is set to Currency. NOTE: Currency fields should be set to 2 decimal places by default. Shows only the records where Number is less than 2 and Stock item is Yes 1.

Click in the Criteria: section of Number field: 3. Click in the Criteria: section of the Stock Item field: 5. The question wants us to search for records where Stock Item is Yes. To do this simply type Yes into the criteria box. Run the query in Datasheet View and check to make sure that the database has only returned records that match our criteria Number less than 2 and Stock Item of Yes.

Sorts the data into ascending order of Make with Airush at the top Once we have our completed query we can take this information and display it in the form of a report. Click Create and then Report Wizard. On the next screen, you should make sure that Kites Order Query option is selected.

Press Next and the Next again. In the Sort Order section, use the drop-down box to select the Make Field. Make sure that it is set to Ascending Low to High. Click Next. Landscape page orientation: 4. Choose a report name which suits the task I chose Kites Order Report.

This lets us customise our report. Showing all fields and labels and making sure the report fits onto a single page: 1. You should be in Report Design View. If I ran this report in Report View you will see what I mean. Click the drop down arrow on the View option and select Design View. This lets us make the required changes. Click on the Make field and drag the handles out to create more space for the information.

Click back on Report View to make sure that the field has been resized enough to hold all of the information. It looks okay. Continue doing this until all of the fields are just the right size to hold the information. You may need to reduce the size of some of them if they are too large. NOTE: Fields can also be resized to make them higher instead of wider.

This is useful to force longer pieces of data onto a new line and, therefore, take up less room widthways. This will make them easier to read. Move the cursor into the Page Header section and click on the ID label. Centre the label using the Centre Alignment tool in the Design tab.

Repeat this for the ID field in the Detail section. Use the same techniques to re-align the other problem areas in the report. Much neater layout P a g e Calculate the total value of kites to be ordered and: o Shows this total at the bottom of the Order column o Formats this total value to currency with no decimal places o Has the label Total order value for the total value. To add a total to the report you will insert a text box into the Report Footer section.

By default the Report Footer section is hidden from view and so you have to create some room for the text box. Creating some room in the Report Footer. Position your cursor to the bottom of the Report Footer bar so that the cursor changes to an arrow.

Hold the left mouse button down and drag the Report Footer row down a little bit to make some room. In the Design section click the Text Box option. Draw the text box into the Report Footer underneath the Order field. Make sure the report is open in Design View. Click the text box which contains the calculation. In the Design section click Property Sheet.

The Property Sheet for the text box will open. You should make sure that you are in the All section. Find the Format setting and use the drop-down box to select Currency. Use the Decimal Places setting to select 0. Close the Property Sheet and click in the label for your calculated field. Change the text to Total order value.

Resize or move label if necessary 3. Run the report in Report View and scroll to the bottom. Your calculated field and label should appear. Includes the heading Kites we need to restock at the top of the page. Move the cursor into the Report Header section. Click in the heading label we currently have and delete the contents. Type in the required text Kites we need to restock. Has your name, Centre Number and Candidate number on the left footer of each page.

Move the cursor into the Page Footer section. In the Design section click the Label tool. Type your name, Centre Number and Candidate number into the label. Use Report View and scroll to the bottom of the page to check that the required information is fully visible. It should look something like this: Save and Print this report. Fit two side by side on the page 2. Displays the field name as well as the data 5. Are sorted into ascending order of Size 6. Include the heading Special offer for kite surfers at the top of each label 7.

This task, again, requires you to produce a query that searches for the required records described in task 2 of Q The rest of Q39 can be tackled in the process of making the labels. Creating the query How to do it: NOTE: The question clearly requires you to make the labels based on all the data so you should ensure that the query is sourcing tblkites and not your Order query.

Selected Fields Available Fields Arrows 4. If the task required a summary of data then you would choose Summary 6. I suggest Labels Query. Open the Labels Query in Design View. Click in the Criteria: section of the Use field. Any record containing the words Kite Surf will be returned.

Wildcard searches should be used as the criteria for any search questions that ask you to find records that contain specific words. Type Yes into the Stock Item criteria field. Type Not Beginner into the Skill Level criteria field.

In this example there were 3 types of record held in the Skill Level field: Beginner Intermediate Experienced Including Beginner within the Not criteria excludes it from the search and Access will only look for records containing Intermediate and Experienced.

Access will now search for the records that match all 3 of the search criteria that we have created. Fit two side by side on the page 3. Are sorted into ascending order of Size Labels are a special kind of report. They are designed to provide a short, summary of information and you are usually required to fit two labels side by side. Click on the Labels Query without opening it. Click Create and then Labels.

Fitting 2 labels side by side 1. The task asks you to fit two labels side by side so select an option where the number across is 2. Label Type option should be Sheet Feed. The next screen allows you choose font styles and sizes but the task does not specify any formatting so it would be advisable to leave this alone.

Type the field name Make into the Prototype Label window with a space at the end of the letter e. Space after the letter e 2. Click on the Make field and use the arrow to move it into the label. Make field inserted into the label after the field name we added 3. Press enter to move the cursor to the line below.

Ascending means From low to high. This means that the data will be sorted from the lowest kite size to the highest. Click on the Size field and use the single arrow to move it into the Sort By: window. By default, Access will sort the field into Ascending Order. Single Arrow Click Next. Choose a suitable name for the label I suggest Kites Labels.

Select the option See the labels as they will look printed and click Finish. Your labels should look something like this: Field names as well as the data Each field on a separate line All records sorted into ascending order of size Ammending the label s heading and footer How to do it: The problem: Produce labels from all the data which: 6.

Include the heading Special offer for kite surfers at the top of each label. Have your name, Centre number and Candidate number at the bottom. Your label should be switched into Design View: 2. For information that you only want to display ONCE at the bottom of each page. For information that you want to display over and over. For example every piece of information should be displayed one after the other until we have nothing left to show.

Because our heading needs to be displayed at the top of EACH label we need to insert a label into the Detail section. Before we can insert a heading at the top of each label we need to create some room. Click and drag around each of the fields to select them. Drag the fields down a little bit to create some space at the top of the label. Click Design and then select the Label option. Draw your label in the Detail section, just above the fields. Type your heading into the label Special offer for kite surfers.

Repeat this underneath the label with your Name, Centre number and Candidate number. Expand the View menu and select Print Preview. The final labels should look something like this: Save and Print the Labels.

Selects only: o Small kites of less than size 3 o Those suitable for beginners 2. Shows only the fields Make, Model, Size and Price. Sorts the kites into ascending order of price. The first thing to note here is that the question does not require you to produce a report. An extract of information is purely a query. Creating the query How to do it: NOTE: The question clearly requires you to create the extract based on all the data so you should ensure that the query is sourcing tblkites and not your Order query.

Create a query in the usual way making sure that you use the tblkites table as the source and not one of your queries. Save the query with the name Extract Query and then open it in design view. Adding criteria to select small kites and those suitable for beginners. Kites which are suitable for beginners should be queried so you should find the criteria section of the Skill Level field and type Beginner Not Beginners.

NOTE: To make sure you are typing the correct words in the query always check the data in the table to see how it is spelt or worded etc. For example.. If we typed Beginners into our query it would fail because there are no records named this in the Skill Level field. If we checked the table we would see that the records in this field are called Beginner.

Showing only the Make, Model, Size and Price fields. The question stated that only the Make, Model, Size and Price fields should be displayed in the query so we need to hide the rest. Look for the Show: section of query Design View. You will see check boxes with ticks in them. To hide the fields we do not want to show just un-tick the boxes in the Show: section. Make sure you leave ticks in the fields we need to show. Removing ticks from the fields we do not want to show.

We just won t display the contents of the Skill Level field. Sorting the query into ascending order of price. The question stated that the kites should be sorted into ascending order of price. To do this you need to find the Sort: section of the Price field.

Click in the Sort: section of Price and use the drop-down box to select the Ascending option. It should look like this: Make, Model, Size and Price fields. Showing data from sizes less than 3 and prices sorted from low to high. Q44 Import this data into your document as a table after the paragraph which ends: Here are some small kites suitable for a beginner, some of which will still be suitable as you progress to intermediate skills. Make sure that all required fields and their labels are fully visible and that there is one blank line above and below the table.

Question 43 is asking you to save the data so that it can be used within a Word document. Saving data for use in another document is known as Exporting data. Exporting the data How to do it: To export the extract of data created in Q42 you must Right Click the query. From the menu choose Export. Experience learning made easy—and quickly teach yourself how to build your own database with Access Skip to main content. Start your free trial. Book description Experience learning made easy-and quickly teach yourself how to build database solutions with Access Show and hide more.

Table of contents Product information. Simple Database Techniques 1. Relational Database Techniques 6. Database Management and Security Stockman, Gavin Powell I recommend this book for anyone who wants a strong foundation in Access.

 
 

Microsoft office access 2010 step by step pdf free.Microsoft ® Access ® 2010 Step by Step

 
 

Skip to main content. Start your free trial. Book description Experience learning made easy-and quickly teach yourself how to build database solutions with Access Show and hide more. Table of contents Product information. Simple Database Techniques 1. Understanding how to adjust the form properties.

Being able to add and remove items on the form. Having the ability to adjust tabs on the form. Understanding how to input data into a form. Being able to work with the data in the database. Knowing how to attach photos to a record. Having the ability to create calculated fields. Download the file.

List of courses Microsoft Excel Level 1 This tutorial includes an introduction to the Microsoft Office interface, and covers the various aspects of creating, formatting, editing, saving, and printing a document in Word Microsoft Word Level 1 PDF tutorial It includes an introduction to the Microsoft Office interface, and covers the various aspects of creating, formatting, editing, saving, and printing a document in Word All you need to do is download the course and open the PDF file.

This specific program is classified in the Database category where you can find some other similar courses. Thanks to people like you? Who share their knowledge, you can discover the extent of our being selected to easily learn without spending a fortune! Access An Essential Guide. But also many other tutorials are accessible just as easily! You should come see our Database documents. You will find your happiness without trouble!