This is the introduction video only. The full course is 1 hour and 34 minutes long and can be found on my web site at
http://599CD.com/X6ZFZA
PLEASE NOTE: This video was recorded using Access 2013, but it's perfectly valid for every version of Access after 2007. Microsoft Access hasn't changed that much, even up to 2019. So please enjoy!
This Microsoft Access video tutorial picks up where Expert Level 13 left off. In this class we will continue to learn how to use Update Queries to modify data, and Append Queries to copy records from one table to another. Topics include:
- More Update Queries
- Scrubbing (Cleaning) Data
- Data with Inconsistent Formatting
- Use Replace Function to Change Text
- CStr, Nz, InStr, Left, Right Functions
- Separate First & Last Names
- Fix Non-Relational Tables
- Append Queries
- Daily Student Attendence Table
This class picks up where Expert Level 13 left off. In Level 13 we started learning about Update Queries. In today's class we will go over four more very popular examples of Update Queries. We will begin by learning how to "scrub" data. In this example, we've received a list of phone / fax numbers in a table, but there's absolutely no formatting in the table. We want to clean up the data so it's all in a unified format: exactly ten digits long and no punctuation (digits only). We'll use an Update Query for this. We'll learn how to use the Replace and Len Functions.
In the next example, one of our clients recently changed their web domain name, so we need to edit the email addresses of all of their clients in the database and change xyz.com to abc.com. We'll learn how hyperlinks are stored in the table (they're not just simple text fields). We'll use the CStr function to display them as simple text. We'll see how we can use the HyperlinkPart function to break up the components of a hyperlink. We'll use the NZ function to convert null values to zero (or in this case, empty strings).
Next is a very popular problem: how to split first name and last name into separate fields. I get emailed asking how to do this at least once a week. You've got a table that someone typed in the customer first and last names into a single field. We learned on day one that this is a big no-no. You should always put these in as separate fields, but whomever built the database you were given didn't know better. So, we'll learn how to split them apart. You'll learn how to use the InStr, Left, Right, and Mid functions to accomplish this goal.
Our last Update Query example involves a table that someone gave you that wasn't properly normalized (relational). It's a list of employees with their departments typed in as text. Again, a big no-no. So you need to create a department table and then convert the department names over to Department IDs.
Finally, we'll begin learning about Append Queries, which allow you to add records on to the end of a table, or copy records from one table to another. We will create a student table, and a daily attendance table. We'll run our Append Query to copy the list of students to the attendance table every day so that all we have to do is check off which students are absent, instead of having to enter ALL of the students EVERY day.
This is the 14th class in the Access Expert series. There's a lot of great material in this class. Learning Update and Append Queries will add tremendous power to your databases. Of course, if you have any questions about whether or not this class is for you, please contact me.
Complete Outline - Access Expert Level 14
00. Intro ( 9:13)
01. Scrubbing Data ( 15:49)
Cleaning Data with Update Query
Inconsistent Data Formats
Fax Numbers with Different Formats
Clean them to look uniform
Backup Your Data!
REPLACE() Function
Replace one string with another
Watch Field Names Carefully
Make sure you use [Brackets]
Null values can cause problems
LEN() Function
Length of String
Show FaxNumbers 7 digits long
02. Change Email Domains ( 12:39)
Hyperlink fields are not simple text
CSTR() Function
Convert to String
HyperlinkPart() Function
Change Domain Name
amicron.com change to 599cd.com
NZ() Function
Null to Zero
03. Separate First and Last Names ( 19:29)
Create Person Table
Put First and Last Names Together
INSTR() Function
Determine the position of the SPACE char
LEFT() Function
RIGHT() Function
MID() Function (Discussion)
Check for Prefix, Suffix, Middle Initial
04. Fix Non-Relational Table ( 9:51)
Employees and Departments
Turn Department Names into IDs
Query Join on Text Values
Update to IDs from other table
05. Daily Student Attendance ( 18:34)
Create Student Table
Attendance Table
Make Append Query
Append To Table Name
Current Database
Another Database (Discussion)
Only Students from One Class
Criteria in Append Query
06. Review ( 8:14)
Learn more about Microsoft Access here:
https://en.wikipedia.org/wiki/Microso...
https://products.office.com/en-us/access
https://www.microsoft.com/en-us/micro...
Microsoft Access Expert Level 14 - Introduction richard gizbert | |
| 4 Likes | 4 Dislikes |
| 457 views views | 164K followers |
| Education | Upload TimePublished on 19 Dec 2018 |
Không có nhận xét nào:
Đăng nhận xét