Microsoft access 2016 basics unit 1 answers free download.Basic tasks for an Access desktop database

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Access ignores line breaks in a SQL statement. However, consider using a line for each clause to help improve the readability of your SQL statements for yourself and others. The semi-colon can appear at the end of the last clause or on a line by itself at the end of the SQL statement.

The following illustrates what a SQL statement for a simple select query might look like in Access:. This example SQL statement reads “Select the data that is stored in the fields named E-mail Address and Company from the table named Contacts, specifically those records in which the value of the field City is Seattle.

If an identifier contains spaces or special characters such as “E-mail Address” , it must be enclosed in square brackets. A SELECT clause does not have to say which tables contain the fields, and it cannot specify any conditions that must be met by the data to be included.

More information about how you use these clauses is presented in these additional articles:. Like Microsoft Excel, Access lets you sort query results in a datasheet. An ORDER BY clause contains a list of the fields that you want to use for sorting, in the same order that you want to apply the sort operations. For example, suppose that you want your results sorted first by the value of the field Company in descending order, and — if there are records with the same value for Company — sorted next by the values in the field E-mail Address in ascending order.

Note: By default, Access sorts values in ascending order A-Z, smallest to largest. Use the DESC keyword to sort values in descending order instead. Sometimes you want to work with summarized data, such as the total sales in a month, or the most expensive items in an inventory. For example, if you want your query to show the count of e-mail addresses listed for each company, your SELECT clause might resemble the following:.

The aggregate functions that you can use depend on the type of data that is in the field or expression that you want to use.

For more information about the available aggregate functions, see the article SQL Aggregate Functions. If you want to use criteria to limit your results, but the field that you want to apply criteria to is used in an aggregate function, you cannot use a WHERE clause. For example, if you only want the query to return rows if there are more than one e-mail addresses associated with the company, the HAVING clause might resemble the following:.

When you want to review all the data that is returned by several similar select queries together, as a combined set, you use the UNION operator. The SELECT statements that you combine must have the same number of output fields, in the same order, and with the same or compatible data types. When you run the query, data from each set of corresponding fields is combined into one output field, so that the query output has the same number of fields as each of the select statements.

Note: For the purposes of a union query, the Number and Text data types are compatible. When you use the UNION operator, you can also specify whether the query results should include duplicate rows, if any exist, by using the ALL key word. For example, suppose that you have a table named Products and another table named Services.

Both tables have fields that contain the name of the product or service, the price, warranty or guarantee availability, and whether you offer the product or service exclusively. Although the Products table stores warranty information, and the Services table stores guarantee information, the basic information is the same whether a particular product or service includes a promise of quality.

You can use a union query, such as the following, to combine the four fields from the two tables:. SQL syntax. Access SQL: basic concepts, vocabulary, and syntax. Notes: Access ignores line breaks in a SQL statement. Need more help? Expand your skills. Get new features first. Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help. Can you help us improve? To collaborate on a document, first open it, then click the Share icon in the upper-right part of the screen.

Clicking the Share button opens the Share pane on the right-hand side of the screen — this is command central for collaboration. At the top of the pane, type in the email addresses of the people with whom you want to collaborate on the document, separated by commas.

As you type, Word looks through your address book and displays the matches it finds; click the person you want to invite. After you enter the addresses, select either “Can edit” or “Can view” in the drop-down to allow collaborators full editing or read-only privileges.

Type a message in the text box if you want. Your collaborators get an email message like this when you share a document. Click image to enlarge it.

Then copy the link, paste it into an email using any email program, and send it. When your recipients receive the email from you, they click a button or link to open the document, which opens in Word Online in a web browser rather than in the Word desktop client.

At this point, they can view the document but not edit it. Logged in users will see an Edit Document menu, from which they can choose Edit in Word to open the file in the client version of Word, or Edit in Browser to work in the free web version. But for basic editing, it works fine. When collaborating in Word , you must save the document to see changes made by others highlighted in green and to share your changes with them.

When you’re working on a document in Word with other people in real time, each person gets a cursor with their own unique color. You can see what they do as they do it, including deleting, editing and adding text. They see what you do as well. Be aware that how well real-time collaboration works depends on the strength of your internet connection. The Share pane shows a list of people who have access to the document, with a note underneath their name indicating if they are currently editing the document, and if not, whether they have editing or viewing access.

Right-click the icon of anyone currently working on the document and click Open Contact Card; a screen pops out with the various ways you can contact them, including chat, phone and video via Skype if they have Skype and email. That lets you talk or text with them while you’re working on the document together, making collaboration that much more effective. Click the icon of someone working with you on a document to see other ways you can contact them.

Although live collaboration is the biggest addition to Word , there are several other new features as well. Click the task you want to get instructions on how to do it. And it remembers the features you’ve previously selected in the box, so when you click in it, you first see a list of previous tasks you’ve searched for. That way, tasks that you frequently perform are always within easy reach. Here are the latest Insider stories. More Insider Sign Out.

Sign In Register. Sign Out Sign In Register. Latest Insider. Check out the latest Insider stories here. More from the Foundry Network. Excel for Office cheat sheet.

 
 

 

Access Working with Tables

 

Slideshare uses cookies to improve functionality and performance, and to provide you with relevant advertising. If you continue browsing the site, you agree to the use of cookies on this website. See our User Agreement and Privacy Policy. See our Privacy Policy and User Agreement for details. Introduction to the basics of Access and SQL. Home Explore Login Signup. Successfully reported this slideshow. We use your LinkedIn profile and activity data to personalize ads and to show you more relevant ads.

You can change your ad preferences anytime. Intro to Microsoft Access. Upcoming SlideShare. Like this presentation? Why not share! Embed Size px. Start on. Show related SlideShares at end. WordPress Shortcode. Like Liked.

Full Name Comment goes here. Are you sure you want to Yes No. Diksha Tomar Chauhan. Saja FA. Priyanka Sharma. Shreya Dikshit , Student. Show More. No Downloads. Views Total views. Actions Shares. No notes for slide. Intro to Microsoft Access 1. Access, part of the Microsoft Office Suite, is designed to work with relational databases and is therefore referred to as a database management system DBMS.

Access can be used to create data tables for storing information, queries for parsing subsets of tables, forms for displaying and entering data, and reports for printing and distribution. This can save on storage space and ensures that entries are not missed when data is updated. Pet No. In our Students and Pets example, the Student No.

Similarly, the Pet No. In order to link the tables, each Pets table entry also lists the Student No. This is known as a foreign key in the Pets table, as it is the primary key in another table. 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. We can use AS to rename any field, not just calculated ones. It also lists how our tables are joined together. The query is closed with a semi- colon. Also, the densities are higher, indicating that low-income households occur in denser-than-average census tracts.

We must enter the entire calculation expression. Switching to datasheet view lets us see our newly sorted results. Switch to datasheet view to see the results.

 
 

Microsoft access 2016 basics unit 1 answers free download

 
 
India Post Mail Guard. Note that in split form all data will be automatically reflected in below data sheet as well. Forms typically contain controls that are linked to underlying fields in tables.