Published on March 8, 2014
Getting Started With MS Access Introduction to MS Access
@_ParthAcharya Steps to create a query • Understanding the requirements of the query • Gathering the tables required for the query • Creating Query • Set the query • Run the query
@_ParthAcharya Understanding the query requirements • Each query requires certain fields Let us understand the query requirements for the following queryThe query should display the following information: employee ID, employee first name, employee last name, employee address, employee salary, department name. For the given query, the requirements is: Employee ID Employee First Name Employee last name Employee address Employee salary Department name
@_ParthAcharya Gathering the tables required for the query • Each field in the previous slide comes from some of the given tables Tip: You can also double click the table names to see the fields and data.
@_ParthAcharya Creating Query with the gathered tables 1. Click on Query Design 2. Select the tables required for query and click on ‘Add’
@_ParthAcharya Creating Query (Cont.) with the gathered tables 1. Once you add the tables 2. Create Relationship To connect two fields, click on one of the fields and drag your mouse to the other field Go to next slide for tips on creating relationship between tables
@_ParthAcharya Tips Create Relationship between tables • Look out for a primary key (fields with a key on its left) in one of the tables to create a relationship with a field in the other table. • Look out for data type match. The fields connecting two tables should have the same data type. • We will look at three fields, two from the Department table and one from the Employee table. Here, the DepartmentID field from the table ‘Department’ has the same data type as the Department field in the ‘Employee’ table. Note that, the Department field in the ‘Department’ table has different data type than the Department field in the ‘Employee’ table. And hence, you shall not connect them, even though the field names are same.
@_ParthAcharya Set the query • To set the query select the fields your query requires. In our case, we want Employee ID, Employee First Names, Employee last name, Department Name…. Etc. Click on the Table dropdown and select the table you want a field from Click on the Field dropdown and Select the field you want. Here, let’s select Employee ID Select all the fields the query requires in the same way
@_ParthAcharya Run the query 1. Hit the ‘Run’ button to run the query 2. Check the results
@_ParthAcharya How to set criteria? • There can be cases where you will need to set criteria • If you want the employees from just ‘Accounting’ department. 1. Go to the design view 2. In the criteria for Department field, type “Accounting” and run the query Make sure you set the criteria for the appropriate field. Here, the criteria is “Accounting” department, which is related to the Department field. And so, we will set the criteria for the Department field, and not for any other fields.
@_ParthAcharya How to set criteria? (Cont.) • There can be cases where you will need to set criteria • If you want the employees from just ‘Accounting’ department and with Birthdate October 15th 1988. 1. Go to the design view 2. In the criteria for Birthdate field, type #10/15/1988# and run the query
@_ParthAcharya How to set criteria? (Cont.) • There can be cases where you will need to set criteria • If you want the employees from just ‘Accounting’ department and with Birthday month May. 1. Go to the design view 2. In the criteria for Birthdate field, type Month([Birthdate])=5 and run the query The syntax is: Month([filed_name])=month_number => Month([BirthDate])=5
@_ParthAcharya How to sort? • There can be cases where you will need to sort certain fields • If you want the employees from just ‘Accounting’ department with First Name in alphabetical order 1. Go to the design view 2. In the criteria for FirstName field, click on Sort dropdown and select Ascending and run the query
Next Part Getting Started with Pivot Tables
Getting Started with Pivot Tables Introduction to Pivot Tables in MS Excel
@_ParthAcharya Before starting with Pivot Tables • How to export an MS Access query to Excel In the External Data tab of MS Access, click on ‘Excel’
@_ParthAcharya Creating A Pivot Table 1. Select the top left cell 2. Hit Ctrl+A This will select your entire sheet. of the exported excel sheet 3. In the Insert tab, select Pivot Table
@_ParthAcharya Creating a Pivot Table (Cont.) • A dialog box would pop up, make sure the ‘New Worksheet’ radio button is selected. Hit ‘OK’.
@_ParthAcharya Steps to create Pivot Tables 1. Understand the requirements (Which fields are needed) 2. Think of possible table layouts for the asked requirements 1. Which field should be set as ‘Row’? 2. Which field should be se as ‘Column’? 3. Which field should fit well as ‘Values’? 3. Look at the created pivot table and modify if needed
@_ParthAcharya Understand the requirements • Suppose we need to see Customers who bought Skateboards and TShirts. We also want to see how much did they spent on those two product. Here, the requirements would include the following fields: • Customer Name • Product Description • Sales
@_ParthAcharya Possible Layouts Layout 1. Row: Customers Column: Products Values: Sales Layout 2. Row: Products Column: Customers Values: Sales Layout 3. Row: Products Column: Sales Values: Customer As you can see, Pivot Table can have many different layouts, we need to choose the one that fits our requirements the best.
@_ParthAcharya Following Layout 1 1. Click on the required field 2. Drag it to the Row section 3. Similarly, drag the other two required fields to their respective sections (as decided for Layout 1)
@_ParthAcharya The Created Pivot Table Try to create Pivot Tables with other two Layout options and see if they fit well with our requirements You will see that the Pivot Table created with Layout 1 fits our requirements the best
@_ParthAcharya Filters • If you remember, in our requirements, we want our Pivot Table with just two products: ‘Skateboard’ and ‘T-Shirt’. To reduce our Pivot Table with Columns just for these two products, we will use filter. 1. To apply filter on 2. Select the required Product Description products Column, click on the Column Label dropdown button
@_ParthAcharya Modified Pivot Table On applying the filter, you will the Pivot Table that fulfils our requirements.
@_ParthAcharya Some Excel Formulas Create ‘Full Name’ column from ‘First Name’ and ‘Last Name’ columns =CONCATNATE(first_name,” “,last_name) • Here, first_name is your data in the ‘First Name’ column; last_name is your data in the ‘Last Name’ column • Make sure to put a space in “ “ Create ‘Month’ column from ‘Date’ column =TEXT(date,"mmmm") • Here, date is your data in the ‘Date’ column
Connect? Blog: vtalktech.wordpress.com Social: linkedin.com/in/parthacharya Email: firstname.lastname@example.org About: about.me/acharyaparth
1.Getting Started With MS Access Introduction to MS Access. 2. @_ParthAcharyaSteps to create a query • Understanding the requirements of the query ...
Access; Excel; OneDrive; OneNote; Outlook; ... Get started. PivotTable report ... Build a PivotTable report. See sales by country. See sales by date. Pivot ...
Getting Started; FAQ; Sign in ... rebuilding pivot tables/graphs and macros when access 97 did ... MS say that I can't down grade to Access 2010 ...
Getting Started; FAQ; ... Microsoft Access 2007 pivot tables DA. ... I would like to place a button link for pivot tables/charts on a ...
This page contains info about creating Pivot Tables and Pivot Charts ... Getting Started Guide. Creator Help. ... Enable shared users to access the ...
MS Access Engine - EXCEL Pivot Tables. ... MS Access Engine - EXCEL Pivot Tables; MS Access Engine ... Turn off "Getting Started"
... Pivot Tables In-Depth - Getting Started Video-DVD Training Tutorials online. O. ... Access; Apache; ASP.NET; Azure; Excel; Excel For Mac; Exchange ...
Getting Started; Code Samples; Resources. Patterns and Practices; App Registration Tool; Events; ... Before starting to program pivot tables for Access ...
Access; Excel; OneDrive; ... Business; Word; Install; Office 365; Training; Admin; Get started with Power Pivot in ... and create date tables in Power ...