Learning microsoft access 2013 pdf free download. MICROSOFT ACCESS 2013 Tutorial and Lab Manual

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Feb 01,  · Microsoft Access Basics & Database Fundamentals hours Microsoft Access is a relational database application. It is the perfect tool when you begin to outgrow your data collection in Excel. With Access, you can obtain better collection results by creating user-friendly forms with rules to protect the validity of your data. This document is an introduction to Microsoft Access , running under Microsoft Windows 7. For further information see Microsoft Access – An Intermediate Guide. Introduction A database is a computer program for storing information in an easily retrievable form. It is used mainly to store. Microsoft Access course Download free Microsoft Office Access file under pages,step by step to learn Access and build your skills, course tutorial training on pdf by Joyce Cox and Joan Lambert. This document is designated to beginning-level to intermediate-level computer users.
 
 

Microsoft Access Step by Step ebook – Home – PDF Drive

 
Introducing Microsoft Access xi Improved conditional formatting You can now use data bars to add at-a-glance insight into the data in Number fields. Ability to export to PDF and XPS files When you want to make a report or other database object available to people but don’t want them to . This document is an introduction to Microsoft Access , running under Microsoft Windows 7. For further information see Microsoft Access – An Intermediate Guide. Introduction A database is a computer program for storing information in an easily retrievable form. It is used mainly to store. MS-Access is that it is a widely available tool. Any-body who has Microsoft Office with MS-Word, also has Access and the programming language Visual Ba-sic behind Access. MS-Access is also a good illustration of many princi-ples that exist on other platforms too, for instance a re-lational database, a Graphical User Interface (GUI).

– Learning microsoft access 2013 pdf free download

Now you can optionaly type in a description over here. You don’t have to, infact I will never use descriptions. You can type in this is the customer’s first name and that will explain to someone else using the database what this field represents. The description field will show up in the status bar on the bottom of form when a user is typing data into that field but again personally I almost never use them.

Now what about Fields for middle initial or middle name, prefix, like Mr. Create as many Fields as you want to store whatever data you think you’re ever going to need. That’s all I ever use in my business. But again break down your information into as much as possible if you ever think you’re going to need it in the future create a Field for it. So if you ever think you’re going to need a salutation Field add that in.

Continuing on I’m going to type in a CompanyName Field is also text. Now I’ve always been happy with a single address field. Some people have Address1, Address2, I don’t bother because an address line will wrap around with second line in and out on a Report.

So that’s really a. For me personally I’ve always been happy with a single address line. What about some different types of data, well lets scroll down here, we can add pretty much as many Fields as we want to in the single Table. Yes there is an upper limit but you should realistically never hit it. We can also type in Website and make that a Hyperlink as well.

This time I’ll just press H on my keyboard. Notice out automatically fills in Hyperlink. There are a couple of reasons why. First ask yourself are you ever going to be performing calculations on this data. Are you ever going to need to find the sum of a bunch of phone numbers or calculate their average? If so use a Number type if not use Text.

Text handles these types of values better. Another factor taken into consideration, leading zeros. Some Zip Codes start with a zero. Well if your using a Number field to store that, that zero will falloff and the value becomes So if the value you’re going to store needs a leading zero stick with text.

Most obviously sometimes phone numbers can contain text. If you see a phone number somewhere for contact and it includes text values. You don’t want to have to stop and grab your phone and translate those letters to numbers. You just type in the text right in your database. Numbers are always sorted numerically whereas Text values assorted alphanumerically. In an alphanumeric Sort for example 10 comes before 2. I’m going to abbreviate number with Num.

That’s okay, just maintain consistency. This is going to be a Number Field and need as I might sometime want to run a Report that says “show me a list of companies with more than 50 employees.

Now when it comes to Number Fields you can specify what kind of number you want. The default type is Long Integer. Long Integers are basically counting numbers and so on and their negatives. Sometimes however you might want a value that has a Decimal or Floating-point number. For example let’s say we want to keep track of their discount rate.

What type of discount does a customer get on his orders? Well that’s going to be a Number and if we come down here in the field size, drop this box down you can see there are several different kinds of numbers that are available. I like to send my customers been around for three years. For example so CustomerSince this will be a date time value.

That will be a Currency value. How about an “IsActive” field that will be a Yes or No value. Now this is where a Field might need some explanation. Someone who doesn’t know your database might not know what is active means.

You could put here in the description field on mailing list. And that will explain to an onlooker or someone using your database that if the customer is not marked active he won’t receive my mailing list. I pretty much included a Notes field in just about every Table because almost everything that I can think of might need some extra notes attached to it. We still have no way to uniquely identify each Customer and that is what the AutoNumber Field is for. Now it becomes especially important in the expert classes when we start talking about relating tables together that we have a good auto number.

We might have 20 Johns; we could have 15 John Smith’s. Phone numbers can change or be shared between customers. So the AutoNumber is really the best way of uniquely identifying each customer. It will never change and Access will automatically maintain a list for us. The first customer is 1 the second customer is 2 and so on. AutoNumbers never get reused so we never have to worry about deleting customer 4 and then another customer 4 coming in later. So it’s the perfect Field for uniquely identifying each record.

In fact you don’t even ever have to see AutoNumbers in your database if you don’t want to. You can if you want to. You can put them on your invoices or show them in your customer Form or you can leave them completely behind the scenes and never see them.

Properly built each Table in your database should have its own AutoNumber field. The customer Table should have a CustomerID. The product table will have a ProductID.

The order table should have an OrderID. So each Table gets its own AutoNumber Field. I just built it this way so I could show you how to move fields around. Usually when I start building a table the very first thing I do is add the ID field. But now I can show you how to move them. Take your mouse and click here on this little box to the left of where it says customer ID. Let the mouse go, that will select the entire row. You just moved a Field to the top of the Table. I personally like to have them at the top.

So now that we got all the Fields in a Table let’s save the Table. Again I capitalize Customer, capitalize T, no spaces in your Table names just like your Field names for the same reasons. Essentially a Primary Key is that one unique value that sets off each Record from every other Record.

You can do that manually by clicking on the “Primary Key” button but I almost always forget to do it which is why I left this in the video. I want you to see that I always forget to do it. Now you know what this means. Now that our Tables are all set I’m going to go ahead and close it by clicking on the X and now you can see the CustomerT right here in the Navigation Pane all set to be opened up and have some data entered into it.

Let’s go ahead now and enter in some Records into our Customer Table. Take your mouse and double- click on the CustomerT and that will open what we call datasheet view because it looks like the big spreadsheet zone, in Access they call it datasheet. Rost Amicron. This brings us to the Phone number Field. Personally I like typing just the digits into a phone number field.

Do not type in any parentheses or dashes – and somewhere later on we get the formatting. I will show you how you can format that phone number to appear however you want in your Forms and Reports, for now though just type in the numbers.

This brings us to the NumEmployees Field. One one of the nice new things in Access is the added Date Picker, this little Calendar control right there, see that little box that looks like the Calendar, go ahead and click on that and you’ll see a little Calendar opens up, you can scroll through the months, you can pick a day by just clicking on it, you can also jump to Today’s Date and there it goes, so can use this to pick whatever Date you want.

Now a few notes on Dates, first of all in a future class I will teach you how to set a default Date so you create new records they can automatically insert Today’s Date for you, a little more advanced talk about that later , the second thing is type in a day and a month and Access will automatically default to the current year, currently so if I type in I get , if you type in a two digit year in 00 29 Access will default to to , if you type in a two digit year from 30 to 99 Access will default to through and so if I type in I get if I type in I get to , the cutoff year is It works extremely well for notes fields.

Now this click to add column over here is where you can optionally add additional Fields to your Table. Okay I’m ready to enter in my second Customer. I don’t have his e-mail address, that’s okay, I just press tab. I’ve always been of the mindset that it is better to have no data than they have bad data that’s why I very seldom force users to have to input information. No notes and now I’m on to the next customer. Now if you don’t feel like typing and you want to use the same data I have you you can go to the special webpage and download a copy of the data, just go to CD.

Now in this case I had already typed in these first two records so those two records are duplicated now in the database. To delete a Record just click on that same spot again, that selects an entire Record and then press Delete on your keyboard. I was playing around a little earlier copying and pasting some data, I deleted a few sample Records so as you can see here IDs 3 through 13 are now gone forever, and will never be reused but again that’s okay we don’t have to worry about those IDs Access tracks those for us.

Now when you download any database from the web you’re going to see the security warning pop-up. Now this database only has one object in it, the Customer Table but as you can see when I open it up there’s all my data.

Now as I mentioned in the introductory video for this course we have student forums available on the website where can post any questions you have about each lesson. If you’re watching this course in our online theater you’ll see the student forum for each lesson appear next to the video. Here are some of the questions that students asked about this lesson back in the Access class:. In database terminology we call it a dirty record. When you close a table Access also saves the records.

In fact the only time you actually have to save something is when you make a design change or a layout change, now a design change would be modifying one of the Fields.

Changing a field name or a property type or if you make a layout change for example. If I make this column a little wider, if I go to close the table Access says do you want to save the layout of the table you can say yes and then Access will remember the width of that column, so the next time you open it will be the same width but that has nothing to do with data in the table which is saved automatically.

In the last couple of classes we loaded some data into our Customer Table, right now we only have a 11 Records in our Customer Table which makes the data pretty manageable, what happens if we have 1, or 11, records in this Table then the information becomes a little more difficult to work with.

Right now, for example, if I wanted to see all of the customers from New York I could just scroll over find the State Field and you can clearly see them here, a few more down below, if I have 11, customers in the Table it might not be as easy to see those customers, So what happens if the boss comes up and says all right we have 20, customers I want to see a list of customers only from New York Sorted by last name and I want it on my desk in 5 min, what you do?

Sorting is pretty straightforward all you have to do is use the drop-down arrow next to the column name for example here’s LastName, if I drop this little arrow down here you can see there is sort A- Z or Z-A. If you sort a column like State here and that has missing values, also called null values, drop that down and sort A- Z, you can see that the empty or null value show up at the top of the list:.

You can also Filter your data to show just a subset of the Records that are, for example, the boss only wants to see Companies that are from New York. So come up here in the State column drop the box down and you’ll see down here a bunch of filters.

There are checkboxes here for all of the different items that are in that list, right now all of the items are selected. If I check this box again that says Select All it turns them all off. You can see it says 1 of 5 now instead of 1 of You don’t want end-users playing with your Tables, this is fine if you just want to get in here maybe take a look at some Records, see some things a different way, Sort some stuff, that’s fine for you but not your end users.

You want to be able to generate all these different kinds of lists of customers without having to come back into the Table to make changes, that’s what Queries are good for. You can set up a Query once, customers from New York sorted by LastName save that as a Query then in the future if you want to run that again you just open up the Query and the work is already done.

So in the next lesson we will learn how to set up a Query to do exactly what we just did with the Table, however the Query can be saved for future use. In this lesson we have the same mission that we had in the last lesson, the boss wants a list of customers from New York State sorted by my LastName, more importantly I want to create a Query to do this so I can pull it up at a moments notice anytime in the future. This is one of the weirdest screens to understand but once you get the hang of it it’s really quite simple.

So let’s say I want to see FirstName and LastName in my query, click and drag FirstName and drop it right down here in the first column:. Now to see the results that the Query is going to produce take your mouse and click right here on the Run button, see where it says Results, there is a Run button, looks like an exclamtion point!

Go and click on that now and there are the results of the Query. That it’s not sorted yet there’s no filter, I just told the Query show me all the Customers, show me FirstName and LastName, Access gave us exactly what we asked for:.

Perhaps the reason the boss wants this list it to have someone call all these customers, so lets add Phone number to this Query. So to add another Field, just find it up here in the Table where his Phone number, there it is right there, now here’s a trick you can click and drag it like I showed you earlier or just double-click on it, watch this, click click and there it goes, it drops right down there into the next column:. Now how do we Sort this information, well we could use the same Sort and Filter buttons right here in the Datasheet View but those don’t necessarily get saved with the Query.

Notice the CustomerQ appears over here in the Navigation Pane and we now have a new section called Queries. CustomerQ appears here on the tab as well. Now if I close the Query all I have to do in the future to run it to get the same set of Customers is just double click on my CustomerQ and there it is. How do we filter based on the State? Now I haven’t filtered it yet, again we could cheat and come up here and apply a filter right here but these don’t reliably get saved in the Query. So go back into Design Mode and down here you’ll see a row that says Criteria.

Now go-ahead run the query again and there you can see just the Customers from New York are displayed:. And then I’ll hit OK, the object is saved, now click on the File tab again to close that down and here you can see open this up a little bit, here you can see both of our Queries: CustomersFromNY and our old CustomerQ. Remember Queries themselves do not store data they’re just displaying the data from the table in a different way so even though you’re only seeing LastName.

The answer of course is no, I’m just showing you the very basics of Query design right now, later on I’m going to teach you how to create something called a Parameter Query where you can set up a Query that the user types in the State that he wants to see when he runs the Query.

So now we know how to build Tables and simple Queries in the next lesson will start working on Forms. Now Forms are used to work with data on the screen. Forms provide you with additional control and security for your data, you can control exactly where people can look, at which Fields they can see, which ones they can add, are they allowed to delete Records or add new Records, all these things can be controlled using Forms. If you currently have paper Forms or perhaps even an Excel spreadsheet that you’re working with you can design a Form to mimic those existing paper forms or Excel spreadsheets, this makes it easier for users to transition into your database.

You can display data from multiple Tables together on one Form, for example you can show the contact history for each customer on the Customer Form. We can show a summary of the recent orders. Forms can display summary information, if you have a Form that shows a list of orders you can put totals in the footer of the Form so at the bottom of the column you can see the total for all the orders.

Changing round the controls, where they go, changing the colors, it just takes practice; This is something that the more you do it the better you become. So here is how you create a simple Form.

First click on the Table or Query that you wish to base your Form on. There are several different ways to build a Form in Access, the simplest is just to click on the Form. Next we have Form Design, which is you building the Form from scratch. Blank Form will build the Form for you and then lets you add the controls where you want them, again we will see this in the future lesson in. The Form Wizard will help you to build a Form by asking you some simple questions; you can then customize the Form once it’s been built.

I personally don’t like the Form Wizard and will cover this in a future lesson. Navigation allows you to set up a simple menu interface with buttons, where you can click on them to go between the different parts of your database.

We will see a couple of these today like Multiple Items, Datasheet and Split Form, we will talk about the others in future classes. So as you can see there are a lot of different options available for Forms but for now let’s just click on the simple Form here. When you click on it a second goes by and then Access throws together the Form for you. On the Form you’ll see a header across the top, here that says CustomerT, then you’ll see each one of the Fields from your Table down below, here is FirstName and LastName and so on, this is called a Single Form because each screenful shows one Record, a single Record at a time, you can see each field consists of a Label and a Textbox.

The Label is right here on the left, it indicates which Field this is. On the right side you’ll see the Textbox, this is where the actual data goes. You can see that Layout View has a little orange border around it, this means we can edit the layout of the Form. Now Design View we will talk about in next class. Design View lets you get in there and make changes to the properties of the objects, move things around a little bit more and basically make more changes than you can with just editing the layout, we will talk about that, again, in the next class.

I don’t need them to be that large so lets make them shorter. When you’re in Layout mode Access tends a work in columns and rows. So if you want to resize the column for example just move right here, on the right border of one of the Textboxes and in the column click and drag and notice how the entire column is changed.

You could also do that with the Labels themselves, click over here on FirstName and you can change the width of that column, so take a few minutes now and practice resizing these Textboxes to be the height and width that you want.

And there we go, I’m now happy with the way my Form looks and I’m ready to Save it. Personally I never use these so I’m going to click on it and press Delete on my keyboard, that will get rid of it, you can also change or delete this little logo right here, I’m going to get rid of it, that’ll save me some space on the screen, I almost never use those little Titles and Logos up top on the Form.

Form View is where your users will work all the time. This is where you come in and make changes to the data in the Form, remember this data is not actually saved in the Form it’s really saved in the Table, the Form is just displaying it for you in a different way, so any change you make here in are reflected in the data in your Table. If I want to move between the Records I can come right down here and click on these Navigation buttons, that will move me to Record 2, 3, 4 and backwards.

And this little button moves me to a new blank Record so you can begin entering a new Customer. If you say Yes you cannot undo that, the Record will be gone for good. I want to quickly show you how to create two additional types of Forms that are extremely handy so click on CustomerT then click on Create, come over here to More Forms and then you’ll see Multiple Items, and click on Multiple Items.

This is called a Continuous Form, we will spend a lot more time with this type of Form in future classes but I know a lot of people always ask me how to create these so I wanted to show you where it is real quick. These are just a couple of examples of the many different types of Forms that you can build in Access and again over the next couple of classes I will show you how to create lots and lots of different types of Forms.

For now, again, I’m going to close this Form and I’ll say No to Save changes, so that in a nutshell is how you can build a simple Customer Form. So far we’ve learned how to create Tables, Queries and Forms. Reports are pretty much just like Forms and are designed to be printed out or sent as an e-mail attachment or basically presented to someone who’s not using your database.

It’s very similar to the Forms section. We have the Blank Report where Access just throws a Blank Report together and lets you insert Fields where you want them. We will walk through the Labels Wizard a few minutes. But first let me show you the basic Report. As you can see Access put together a simple Customer Report. It looks just like a Continuous Form that we built earlier in our Forms section. Again just like Forms, Reports have different views. Now you have a limited control over the height of these objects because Access needs to print all of the information in each text box so as you can see here the CompanyName is forced onto a second line so it has to be able to grow that Textbox vertically in order to show the information, you can get away without doing this on Forms because on a Form a Textbox you can scroll up and down, you can’t quite do that on Report, so the Report you could really only easily control the horizontal size.

Notice in the bottom here we have zoom controls just like in Word and Excel where we can zoom out to get a better view of a document, we can scroll down, we can go to Page 2 right here, then Page 3. Right now they are set to narrow, you can set to Wide or Normal. Here is we could also come to Export this Report as a data file. If you want to send it as an E-mail Attachment click on E-mail. Report View shows you the data and doesn’t let you make layout changes whereas Layout View obviously allows you to make those changes.

When you have your end users opening up your Reports you don’t want them playing with the layout changes and yes in a future lesson I’ll show you how to restrict all that.

Now let’s close this Report. Do I want to save this Report? Now the Report is only getting its data from the Query so as you can see here I’m only getting 5 records instead of all 11 and I can change this Report header by just clicking in it and editing it that way: CustomersFromNY. With this access an introduction tutorial you will master this important program and increase your chances for getting the job position that you have always wanted!

Free tutorials access an introduction – PDF. Introduction to Microsoft Access Description : Download an introduction to the Microsoft Access interface and covers the various aspects of database creation and management in Access Size : Access An introduction. Description : Download free an introduction to MS access , course material, tutorial training, a PDF file on 18 pages.

Access databases for researchers. Description : Download free Access An introduction to databases for researchers course material, tutorial training, a PDF file on 17 pages.

Quick Start Guide Access Description : Microsoft Access looks different from previous versions, so we created this guide to help you minimize the learning curve. PDF file. Access Relational Databases and Subforms.

Description : This document has been developed to show you how to use multiple tables and relational databases in Access Microsoft Access Forms. Description : This document has been developed to help you learn more about several useful features in Access such as creating a Form. Access Create web-based databases. Introduction to Access Access Reports and Queries. Home Database Introduction to Microsoft Access Introduction Microsoft Access allows people to effectively and efficiently organize data.

Learning Objectives After completing the instructions in this booklet, you will be able to: Identify the components of the Access interface. Create a new database. Understand how to create a table. Know the purpose of the primary key. Implement error traps. Enter descriptions for fields. Understand how to add fields. Know how to enter various types of data into the table. Description : Download an introduction to the Microsoft Access interface and covers the various aspects of database creation and management in Access Level : Beginners Created : October 15, Size : Summary on tutorial Introduction to Microsoft Access