Programming for your own use by MS Access
4.5 (2 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,224 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Programming for your own use by MS Access to your Wishlist.

Add to Wishlist

Programming for your own use by MS Access

Override Excel limitation by Access
4.5 (2 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,224 students enrolled
Last updated 5/2017
English
Current price: $10 Original price: $35 Discount: 71% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 5 hours on-demand video
  • 4 Articles
  • 8 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Learn basic Access programming
  • Override Excel limitation by Access
  • Breakthrough Excel's barrier of One to Many Data protocol
  • Generate dynamic report out of existing data in the blink of an eye
  • Override Excel's complicate Vlookup with Access simple Query Matching
  • Value added on career path
  • Learn how to handle bundles of data by ultimate database handling tool
  • Data mining will be just a piece of cake
  • Learn how to design program
  • Can make systems analysis with actual prototype in just a day
  • Learn how to convert program from standalone to network multi users system
  • Can produce printed form out of specific data, e.g. Staff Record, Invoice, PO, Quotation, Receipt etc.
View Curriculum
Requirements
  • Only basic Excel needed. And we'll cover the rest.
Description

Use the right tool for the right job.
Most of us use Excel for Database handling while doesn't know the existence of Access which stand directly for Database job and being put on Microsoft Office next to Excel for decades.

Excel, actually, is great for Calculation, plenty of mighty function included and even good at figures presented with fantastic chart of all means.  However, it's far inferior than Access in term of Database handling.

There are many problems being addressed by using Excel for database handling, no matter of 'one too many' protocol, tough using of VLOOKUP, normalization, Dropdown making, data filtering, relational data handling and Network conversion etc.

Through this course, you will learn how to use Access, manage your database effectively. 
And you will discover by yourself that all difficult tasks faced on Excel for database handling 
will become just a piece of cake on Access.

Let's enjoy knowing how Access work!

This Course covers  Basic Access Programming, System Analysis, How to Design Effective Data Structure,  Create Table; Query; Form Interface; Report; Printed Form and final at Program Conversion from standalone to network.

By the completion of this course, you will be able to create any Access programs on your own, causes all programs normally going on the same path but different on individual vision.

My course designed by Ms Access 2016 on basic tools, then it can be applied to all Access version from 2007, 2010, 2013. The only difference is menu or command location that change place from time to time on each version

About the course:

  • Lifetime access
  • Unconditional Udemy 30-day money-back guarantee
  • No previous experience on Access is needed. Just Excel basic is enough
  • Learning on doing


Who is the target audience?
  • The one who feel at edge of Excel and cannot go further
  • The one who willing to know how Access worked
  • Anyone who is looking for some tools next to Excel for effective data handling
  • Anyone who always face with miserable on report issued from Excel fix format
  • Anyone who willing to create custom programs for own use
  • Programmer or people who looking for some quick tools to make system analysis or express their idea on system design in form of Actual Prototype
Students Who Viewed This Course Also Viewed
Curriculum For This Course
54 Lectures
05:04:53
+
Introduction to the Course
2 Lectures 06:00

Welcome to 'Transcends Excel Limit with MS Access

Major tools on Microsoft Office consisting of Excel, Word, Power Point and MS Access.  But most of us know only three except Access.

This course will guide you to Access on how to take good benefit out of it.  

Access actually is a direct tool for database handling.  However, since most of us have no idea on how it works. We normally use Excel which is a great tool on working space with fantastic calculation function, but inferior on database handling.

Many times we faced with its limit, but have to accept as is helplessly while Access is still there.

Through this caused, many limits faced on Excel will be solved, no matter the limit of 'One to Many' statement, e.g. Employee's History, Invoice, Purchase Order, etc. or limit of very complicate Vlookup on matching, dynamic filtering, printed form generated out of existing data once needed, instead of saving Invoice one by one on sheet by sheet which hard to retrieve.

Preview 05:44

Download Resource for Lectures here.

Pre-Resource consisting of all codes needed in PDF and sample that we used in our Lectures, including Access Blank File for Query operation.

Post-Resource consisting all the same as above, except Blank File filled with all Queries samples throughout our Lectures including final HRTraining.accdb to reveal all processes and coding that you has to pass through all over our Lectures.

Download Resources involved in all training steps
00:16
+
Why Database and How to make it right
1 Lecture 06:45

Know difference between Database and non database can save times a lot!

Preview 06:45

Try Out to find which one should be correct Database Format

Test for basic knowledge about Database
1 question
+
Data Mining
11 Lectures 01:12:36

Equal to....  Filter for all records with the selected exact word
Does Not Equal to... Filter for all records beside selected exact word
Sort from A-Z ... Ascendant sort
Sort from Z-A...  Descendant sort


Filter Option varies for each data type as follows: -
Text Filters - Only needed is Contains....
Number Filters - Use Less than... and Greater than...
Date Filters - Use Between... and....

Preview 10:46

Normal Filtering by right click directly on Datasheet View cannot be saved.  If we want to save our filtering format, we have to do it on Query.

Query is actually SQL Coding to select any specific data from Database according to our design, for which we have no need to have any coding knowledge, just have to do what we want and Access will compile it to SQL or Standard Query Language for us (this is beneficial that programmer on other language not have). 

Filtering criteria on Query could be :-

  • Constant Value e.g.  "Stephen"; 5000; Between #1/1/1960# And #12/31/1970#
  • Parameter e.g. [What Name?]; [What Department?]
  • Get criteria value from the Text Box variable on Form which needed to code with exact correct path like what we select on windows file path but different only for using of "!" instead of "\" and have square bracket on each path e.g. [Forms]![Form1]![Position]

Caution: All variable needed to be in Square Bracket to let Access know that they are not simple Text.  Also Access has a wizard tool for Auto Path Finding by just Right Click on Query Span and choose "Build".

Query Filtering 1
07:44

Query Filtering 2
05:15

Query Filtering 3
08:41

Group or Summary Query normally creates for your eyes only.

It is not editable and not suggest to link with any other tables as it will infect non-editable affect to the others, due to one record actually represented so many records, group up or sum up together.

And that's why Group or Summary Query always end up with Make Query to make it usable e.g.

Group [Product] with [SumOfInQty] minus [SumOfOutQty] to get balance and Make new table call BalanceTbl out of it for real time balance usage.

Group and summary 1
06:14

Group and summary 2
06:59

For normal calculation, there is nothing different from Excel except it refer to field name instead of cell locations and no equal symbol e.g.

Excel : [a3] =[a1] * [a2] while [a3] column's name is "Amt", [a1] name as "Qty" and [a2] name as "price"
Access : Amt: [Qty] * [price]

Normal Calculation
02:30

If then else has nothing different from Excel except function name 'if' is 'iif' (immediate if) instead e.g.

For one condition - SalaryRange: iif([Salary] > 2000, "High", "Low")
i.e. if 'Salary' >2000 is True, result will be High, and if it comes out False then result is Low.

For more conditions formula will be - SalaryRange: iif([Salary] > 2000, "High", iif([Salary]>1000, "Medium", "Low")


If Then Else
07:15

There're only a few types of date conversion that we always use on our application.

And here is those function that we gonna use to convert [InvoiceDate] into Year, Month, Day or Year/Month

Year = Year([InvoiceDate])
Month = Month([InvoiceDate])
Day = Day([InvoiceDate])
Year Month = Format([InvoiceDAte],"yyyy/mm")


Preview 04:16

Matching is the biggest benefit that you can get out of Access, no matter you can create a perfect program or not. Only you know how to use Query for matching, you can replace the very tough and time consumed Excel Vlookup from many work hours to a minute, and boost up your work in the most effective way like what I show you on video about Account Pay Reconciliation.

For Join direction, the trick is, only arrow head must point from Main Interest Data to Add-on Data.


Preview 12:44

Excercise 1 : Data Mining and Join Test
00:12
+
Normalization - 4 basic concepts
5 Lectures 22:46

Normalization is consisting of 4 basic concepts of how to design good database, to avoid typing error on essential data and solve problem of 'one to many' protocol, which always make Excel user feel uncomfortable on where to put the bundle of history of each individual employee on.

This 4 concept consisting of : -

Normal 1 : No typing allowed for any repeated data
Normal 2 : Vertical Thinking
Normal 3 : Split Header and Details to avoid blank space
Normal 4 : Details out all valuable data

Normalization
02:17

Create Drop down for any repeated data, e.g. customers' name, product, vendor, etc. for the following reasons: -

  • Minimized typing error
  • Less key-in time
  • Get unique data that fit for the summary
  • One place correction only on supporting table
  • Has relationship build in on linking to supporting data for more related details
Preview 05:35

'One to many' protocol must be designed by Vertical Thinking e.g.

  • One Invoice has many line items
  • One Purchase Order has many line items
  • One Job has many processes to go through
  • One Employee has many types of history to be recorded

From above, all parts of many have to be designed on vertical direction e.g.

  1. Job No1, Process1
  2. Job No1, Process2
  3. Job No1, Process3
  4. Job No1, Process4
Normal 2 Vertical Thinking
06:34

To support Vertical Thinking of  'One to Many' protocol, Data must be split into the Header and Details and make it link by have linking field on Details table

Normal 3 Split Data into Header and Details for 'One to many' statement
05:14

Left one REMARK FIELD  on the main job table.  And leave it free for 6 months at least, to collect the missing data which not details out from the beginning.  Then distribute each of missing data on new added up individual field.

Normal 4 Details out all valuable data
03:06
+
System Analysis and Program Design
6 Lectures 19:44

All names created in Access are variable, and variable have naming rules :- 

  • No common
  • No symbol
  • No space
  • No duplicate
Variable Naming
05:49

Key all data needed on Excel and try to be assured that not even one single data left behind, then send it to all concerned and keep on look at it for a week, you can find there still many data to be added up.

Gathering all data needed on Excel
04:13

Looking for group of data which may subjected to 'One to Many' protocol that may lead program to Dead End caused of Horizontal limit of correction such as 

  • Option e.g.  John score on - Sci; Bio; Chem
  • Process e.g. Job 1 - Start On; Plan On; Production On: Finished On
  • Date e.g. Computer Production on - Jan 1, Jan 2, Jan 3

All these Dead End of 'one to many' protocol, must be regarded to Vertical Thinking for Endless.

Utilized Normal 2
03:07

Split details which initiated from Vertical Thinking design to another table and add a linking field to make it related then become Header and Details or Mother and Child or Layer 1 and Layer 2.

Utilized Normal 3
01:14

Highlight repeated data which supposed to be Drop down and make supporting data for them.

Utilized Normal 1
05:09

Please download "DesignExercise.xlsx" from resource of this Exceise and convert Data from "Raw Data" sheet to correct Database structure by using all concept that we have learnt from this section including all Normalization and Variable Naming.

Remark: Please try by yourselves first before moving over to "Answer" sheet.



Exercise 2 : Design Practice
00:12
+
Table/Database
7 Lectures 38:32

Configure setting needed for all machines concerned prior to start using our program.  However, it's just one time setting if there are no machine format or new Ms Office installed.

Configure setting will be as follows:-

  1. On Current Database > Document Windows Options > Choose Tabbed Documents
  2. Client Setting > Confirm > unchecked 3 options
  3. On Trust Center > Trust Center Settings > Message Bar > choose Never show........
  4. On Trust Center > Trust Center Settings > Macro Settings > Enable all macros......
Configure Setting
04:38

Create Table by copy and paste.  Don't forget to also copy column header together as it will become Field name on Access.

Create Table
04:14

Insert ID AutoNumber with Primary key assigned, on every table for linking purposes, hence this kind of data type is unique that fit for fast matching, save machine resource and stable.

Also Access will give promotion of auto link once related table faced with each other on Query.

Give ID Key to all tables
04:59

There are 4 types of data that we need to config format on each individual

  1. Text or Short Text (2016)
  2. Date/Time
  3. Number
  4. Lookup

Text or Short Text
Field size should fit with actual data, but should not exceed 60 digits where possible, cause Access will create a text box with 'New Line in Field' on 'Enter Key behavior' property and 'vertical' on 'Scroll Bars' property of whichever field exceeding 60, that may affect users who just key-in at fast without looking at the monitor.

Date/Time
Date/Time format must be 'Medium Date' cause it gives month as text that will enable us to avoid misunderstanding of 02/03 which not certain that it is Feb or Mar in term of Regional Setting on Control Panel.

Number
Number field is allowed only for calculated figures only.  For others like Ref No., ID Card No. or Telephone No. etc., data type should be 'Short Text' hence there may happened to have some Text Characters added up in future.
Number format normally will be 
Field Size = Double
Format = Standard
Decimal Places = 2
Default Value = 0 (Very important: Do not leave it blank as it assumed for null in case of no data and will cause buck on calculating)

Lookup
By using wizard, if we actually assigned an ID to all tables, Access will keep ID on this Lookup Field not text as displayed.  Therefore, please always keep in mind that, if it is Drop down, it is 'Number' Type not 'Text' (what we see is not real).


Adjust Data format and Create Dropdown 1
05:00

Adjust Data format and Create Dropdown 2
07:12

Adjust Data format and Create Dropdown 3
08:28

Relationships being created by Wizard on Dropdown process by Linking of Auto ID between 2 tables.

There are 2 types of relation :- 

  1. Normal relation between support data and drop down to make for standard input of repeated date.
  2. Relation of Mother and Child which relationships must be set to Enforce Referential Integrity and Cascade delete related records to avoid orphaned data on the related child table.
Relationships
04:01

This quiz just to remind all important points that needed inTable creation

Quiz 2
7 questions
+
Form - User Interface
9 Lectures 53:32

There are 5 types of form that fit for each specific purpose :

  1. Single Table Form View - Fit for Main Data Table that long
  2. Single Table Datasheet View - Fit for Main Data that short
  3. Single Table Popup Form - Fit for Data that needed to maintain from time to time
  4. Mother and Child Form View - Fit for Header and Details interface with both data fills in at the same time e.g. PO, Invoice, Receipt, Quotation.
  5. Mother and Child Datasheet View - Fit for Data input that Header come first and Details to be filled in afterward e.g. Subject Registration with existing Student List, Maintenance transaction updated for each of Existing Machine.
Preview 09:14

Assign proper Form for each table by not limit only one form for one table
It's depend on each manner of need.

Form assign
03:51

Good Form must be created from Query, since Query can add all necessary formulas or code. 

And also we can draw out related link fields from other tables, e.g. Customers Address from Customers Table to be added to Sales Query or Standard Price from Product Table to be added to Sales Details.

Remark:  One Query needed for one form or one report.  Do not use one Query for being data source of more than one form or one report.

Lecture 35: Create Query For Form
04:41

Form Wizard available for any Forms on Form View

There are 3 steps to check off for Form View

  1. Set ID Enable Property to "No"
  2. Take care of Text Box with 'Field Size' over 60 digits, by switch 'Enter Key Behavior' property from 'New Line in Field' to 'Default' and 'Scroll Bars' property from 'vertical' to 'None' and adjust Text ฺBox Size to normal.
1 Single Table Form - Form View
06:36

No Wizard available for Form create on Datasheet View.


To create Form on Datasheet view, there are 2 forms needed.

  1. Blank Form as a background that provide us space to add Title, Button or Search Box. And since it is Blank Form, 'Record Selectors' and 'Navigation Buttons' on Format Tab of Property Sheet need to be set to 'No'.
  2. Sub Form on Datasheet format.


2 Single Table Form - Datasheet View
09:05

3 Single Table Form - Pop Up Form
04:38

Nothing to do much on creating Mother and Child in Form View. Just choose 2 related tables of Header and Details, then let wizard handle it all.

4 Mother and Child - Form View
06:11

This type of Form need VBA Code on Event 'On Current' of Form Property Sheet to claim mother status over Details Form, and to make it requery on every move of record selection. Here is the code:

Me.Parent![In2SF].Requery

Beside coding above, we also need to set Link Fields for both 'Link Master Fields' and 'Link Child Fields' on Data Tab - Subform Property Sheet of Details Form in order to let program know how to requery right on the Link.


5 Mother and Child - Datasheet View
09:02

This exercise is just to test your understanding on how to use which type of form for which type of working environment to make user more comfort on working on our Form Interface.

Please download 'FormAssignExerecise.xlsx' to make a test.

Remark: Please try by yourselves on 'Case Study' sheet first before moving over to "Forms Assigned" sheet.


Exercise 3 Form Assigned
00:14

Let's have some properties set up and code test on form creation 

Quiz 3
3 questions
+
Automation
3 Lectures 15:15

Actually we can search or filter through right click filtering, however by create Search Box, our program will be more friendly to users.

For VBA Code on this lecture you can copy from here or download from resource of this lecture.

What you have to do is just replace your searched field name on 'HName'.  And that's all.

Dim FT As String 
FT = Me.St
Me.Form.Filter = "[HName] like '*" & FT & "*'"
Me.Form.FilterOn = True
Me.St = Null

Note :  For search or filter on Drop Down Fields, please do not forget that Drop Down Fields keep ID instead of Text displayed.  We therefore, have to find out its actual text from related table and place it on Query then on Form accordingly.  And search on such actual text field instead.

Search or Filter from Form View
04:41

To search from Sub-form, only add up Sub-form on Code.

On below template sub-form name is 'BomSF1'.

What you have to do is just replace it with your Sub-Form name, and replace your searched field on 'InvNo'.
And that's all.

Dim FT As String  
FT = Me.St  
Me.BomSF1.Form.Filter = "[InvNo] LIKE '*" & FT & "*'"  
Me.BomSF1.Form.FilterOn = True  
Me.St = Null


Search or Filter from Datasheet View
05:08

Sometimes we want to copy values from some sources to paste on the one or more designated field, we can make Code for that, through Double Click Event.
And its syntax will be pasted field on LEFT while copying field on Right e.g.

Forms![HRTrainF2]![H2SF1].Form![H2Training]  = Me.Tid
DoCmd.Close acForm, "trainf"

In This case  "Forms![HRTrainF2]![H2SF1].Form![H2Training] " is the pasted field 
and "Me.Tid" is the copying field.

Remark: Copied field is "Me.Tid" instead of "TName" because "H2Training" is Drop Down Field that keep ID instead of "Short Text" (refer back on how to create Drop Down by Lookup Wizard).

Preview 05:26

To make you more familiar to coding

Quiz 4 Code test on Automation
2 questions
+
Report
5 Lectures 43:51

Once you have all data in hands through our precise table designed, no matter where it is on whichever related tables, what you have to do to dig data out for data mining either trace back or track for progress, is just making up a Query with all necessity link to draw out your wanted data, then send it to Excel for report or attache mail for follow up etc.. And that's all.

Remark : Excel is great at making Report fancy than Access.

Query Report for Trace and Track Part 1
08:52

Query Report for Trace and Track Part 2
03:39

To support Paperless policy, I normally do summary by export all needed data to Excel Pivot.

And since it is summary, Format([TransactionDate],"yyyy/mm" might not be forgotten, so that we can have YearMonth column for summarization.


Preview 04:49

For me, I use Access Reports for Printed Form only (real report I use Query). And Create from design mode is more fit than using wizard.

We normally use Access Reports for Purchase Order, Invoice, Receipt, Quotation and others that needed for formal print out in Printed Form.

Create Printed Form is not so complicate but take time a lot on arranging to make it look OK.

The only thing we have to care about is where to place which field on. What part is Header, which part is provided for details. And how many Header and Footer out there. How its use. Here are the details:

  1. Report Header and Report Footer: Header locates only head of first page, while Footer locate only on the last page where data ended (not at the end of the last page)
  2. Page Header and Page Footer: Header locates on top of every page, while Footer locate at the end of every page.
  3. Group Header and Group Footer: Header locates on top of each group, while Footer locate on end of data of each group, and this is where we place our Total, Tax or any calculation field on, including the amount in words.

Tip: To remove border of each Field - select all fields and select 'Border style' on Property Sheet as transparent.\
To remove alternate back color of each line item - right click on Details area and select 'Alternate back color' as no color.

Printed Forms Part 1
11:36

Printed Forms Part 2
14:55
+
Manage Program
3 Lectures 15:40
Create Switchboard
10:35

To reduce file size, we can activate Compact & Repair.  Access will return all excess reserve space caused by excess field size setting of 255 or 100 or 60 at time of Data Type format fixed, and also will return memory used for undo purposes of any designs we made too.

Caution :  Do not activate Compact while there're still some users running our program, caused program will be cracked and cannot repair.  And that's why auto compact is not suggested.

For Backup, just copy and paste as what we deal with normal Excel Files

Caution:  In case of program already been transformed to Network Program, compact and backup must be done on 'Back End' instead.

Compact
02:14

For this basic Access course, Delete Data can be done at ease by just sort and delete.  However, backup should be done first.

Backup and Delete Old Data
02:51

Quiz 5 Compact
1 question
1 More Section
About the Instructor
Veerachai Junhunkit
4.5 Average rating
67 Reviews
1,374 Students
2 Courses
Programmer

I'm handling logistic and supply chain for more than 20 years.

Since it is chain operation, I know if I want to work well on my job I needed data from all parties concerned, no matter from Sales, Purchase, Planning, Store, Production, Warehouse or even my own Logistic, including Accounting and HR.

In the past, I had no real time data on hands, I'm blind. What I did, is just dump everything in as much to prevent shortage of material which will cause more worse on time and wages than space and interest paid for inventory. However, that's not a real solution, caused I finally, ran out of space. Dead and slow, scattered around my store by such a dumping.

Then, I happened to realize that what I seriously needed is a Data and it is the only best solution to out of the mess. That's where my first course in IT began.

I took PPD (Professional Programming Developer) course for 2 years, and hang on with Ms Access for over 20 years after that, because I learnt that Access is best fit for me. I'm not a programmer by the original. I just want some easy tool that can build up program for me and my chain on the fast track with highest mobility since trial and error is inevitable.

Access do the best job for me, whenever I need someone to give me info on real time, I give them my program interface. Once they key in, I got it! and all problem solved before it goes on big. And that's where my Mini ERP started.

Since I got a lot of benefit from Access to my own Job, I'm willing to share it out to the one who are in the same situation or one who have in mind on how to improve their career part or their own organization, but no tool to express it out for real. I, then, open my private Access Training Class to give people all what I know from 22 years experience accumulated. And it's already 5 years passed with more than 200 peoples being direct train up, and over 200,000 on YouTube, but I still willing to give more message to the rest that still keep on finding some tools to answer their requirement on data concerned jobs. So I create this course to share more.

Areas of Expertise

-Microsoft Access
-Logistic and Supply Chain
-ERP (Enterprise Resource Planning)
-Shipping and forwarding
-Account and Finance
-Banking and Forex
-Life programming