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:
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.
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.
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....
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 :-
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".
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.
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]
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")
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")
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.
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
Create Drop down for any repeated data, e.g. customers' name, product, vendor, etc. for the following reasons: -
'One to many' protocol must be designed by Vertical Thinking e.g.
From above, all parts of many have to be designed on vertical direction e.g.
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
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.
All names created in Access are variable, and variable have naming rules :-
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.
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
All these Dead End of 'one to many' protocol, must be regarded to Vertical Thinking for Endless.
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.
Highlight repeated data which supposed to be Drop down and make supporting data for them.
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.
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:-
Create Table by copy and paste. Don't forget to also copy column header together as it will become Field name on Access.
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.
There are 4 types of data that we need to config format on each individual
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 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 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)
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).
Relationships being created by Wizard on Dropdown process by Linking of Auto ID between 2 tables.
There are 2 types of relation :-
This quiz just to remind all important points that needed inTable creation
There are 5 types of form that fit for each specific purpose :
Assign proper Form for each table by not limit only one form for one table
It's depend on each manner of need.
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.
Form Wizard available for any Forms on Form View
There are 3 steps to check off for Form View
No Wizard available for Form create on Datasheet View.
To create Form on Datasheet view, there are 2 forms needed.
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.
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:
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.
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.
Let's have some properties set up and code test on form creation
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.
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
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).
To make you more familiar to coding
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.
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.
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:
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.
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.
For this basic Access course, Delete Data can be done at ease by just sort and delete. However, backup should be done first.
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
-Logistic and Supply Chain
-ERP (Enterprise Resource Planning)
-Shipping and forwarding
-Account and Finance
-Banking and Forex