Ms Access Database Front End (22 Hours)

Tables ,Queries, SubQueries. Functions, Forms, SubForms, Inbuilt Macros ,Data Automation

What you'll learn

This course has 5 sections-Tables, Queries, Functions, Forms and Macros (22 hours content.)

You shall learn here a compete Access Front End. Its a Basic to Advance Level course

Access VBA Variables,Loops . SQL, Arrays, Collection Loops and much more

Learn how to create Dashboards using Access VBA -Super Advance

How to integrate MS Access with Excel Applicaiton to make reports and do data analytics

Answering student questions also. Supported files are available for you to download

Requirements

You should have MS Access application on your system. 2007 Version or above is ok

Description

This Course has Tables, Queries,  Forms, Functions, Macros - 5 sections - Covered entire MS Access DBWhat are the Tables- Their use, how to design them.Why we use the Tables- Import and Export the data from external sources like text file, excel file, csv files and other sources like MS access itself.Use of saving import and export steps to save time and automate the report. Its a great feature.What are the different types of Validation we can apply on tables .What are the rules one has to follow and keep in mind  while designing the Tables in MS Access- In detail discussionSelect query with so many different examples including the SQL statement of same.  Know select from front end as well as its SQL and why it is helpful if we know SQL too.Update - Delete- Append- cross tab- Make Table - update - drop query - all action and select queries are discussed with examplesHow to use query wizard to find out unmatched records or duplicate records .How to use Group option in queries. To summarize the data like a pivot, how group feature worksUse of Sub- queries. It means how we can use one query inside the other query and how it works.We talk about live situations and how to deal with them using joins or sub queriesHow to use IIF in IFF  which is called nested IFF functionDeal with all date functions like dateserial, date,month,year,dateadd,datediff etcHow we can use text functions , upper, lower functions. MID LEFT RIGHTHow as a beginner we use the access query zoom option and wizard to find out the functions if they exist or notUse functions using wizard and what are the things one should keep in mind while using themLearn how to create a form from basicsWhat is the use of different types of views- Design,Layout and form viewKnow about labels,text boxes,check boxes,option buttons,list-box and combo-boxHow to put values in combo-box using SQL syntaxHow to put value in combo box using select query wizard or manuallyHow to use row source property and customize the query being used in these controlsHow to set up relationship between two combo-boxesHow to set up relationship between combo and list boxLearn how to use macro and query in forms - just a basic partHow to link form controls with queries and be able to generate wonderful projectsLearn about sub-forms from their design to their actual use in real life.What is difference between control source and row source property of form controls and how to use control source for calculationsHow to use add existing field tab This series is for those students who want to automate their access tasks by learning the in built access macrosBasic -intermediate and Advance level Macros explained in detailHow to use message boxes and input boxes with practical situationsUse of dynamic IF conditions using "AND" "OR" operators with else if How to user IF macros and when and why we need multiple IFs so we can tackle multiple condtions.Know the data import and export from outer applications like ms excel, text files and databases like ms access itself.Learn about Auto-Keys and Auto Exec macros - their useHow to run queries, open tables and open formsHow to call one macro from other. How we can create a single macro which performs many actions in one goHow to define variables and use them in access macros.Know about set temp vars and set property tasksHow to use DLOOKUP function in your projects and how it can lookup the IDs and validate the login information, if right user is logging in or not.Copy object and delete the objects How to set Filters

Overview

Section 1: Access Front End - Deep dive into TABLES

Lecture 1 Access Terms - Let us get comfortable with Fundamentals

Lecture 2 lmport Data techniques

Lecture 3 Set Data Validations and Checks - Table Design

Section 2: QUERIES - for Data modifications

Lecture 4 Select Query

Lecture 5 Delete Query

Lecture 6 Append Query

Lecture 7 Update and Make Table queries

Lecture 8 CrossTab & Wizard features

Lecture 9 Group Union & Drop Queries

Lecture 10 Parameter Query & Project for you

Section 3: JOINS TYPES with FUNCTIONS

Lecture 11 Joins with Primary Key

Lecture 12 Joins -Unmatched

Lecture 13 How to write functions - Left and Mid fx

Lecture 14 Date functions

Lecture 15 IF Function

Lecture 16 DLOOKUP

Lecture 17 SUM AVG MAX

Lecture 18 GroupBy

Lecture 19 SubQuery

Lecture 20 Your Question - Use two functions to create a new output

Section 4: FORMS

Lecture 21 Forms- Introduction

Lecture 22 Design View

Lecture 23 Add Delete Duplicate Undo Form Buttons

Lecture 24 Combo Boxes in forms

Lecture 25 Linking of Combo Boxes

Lecture 26 SubForms inside a Form

Lecture 27 ListBox

Lecture 28 How to use a query with form

Lecture 29 Form Tips to improve Productivity

Lecture 30 Formatting of Forms for a better view

Lecture 31 Image Control

Lecture 32 PROJECT

Section 5: MACROS - Inbuilt

Lecture 33 MessageBox

Lecture 34 How to Open a form

Lecture 35 Multiple Macros in single macro

Lecture 36 Set Var

Lecture 37 IF OR

Lecture 38 Import Export

Lecture 39 Else if

Lecture 40 DLOOKUP for Login Search using IF

This course is for Data Analysts , MIS Analysts and those who want to clean, trim, structure data in ms access,If you want to create automation of reports, dashboards using access .especially if your data is very large