Access Beginner Level 1 Adc | PDF | Databases | Microsoft Excel
Similar to an OLE object, an Attachment data type field can store pretty much any kind of file in Windows. You may not give or sell it to someone else when you’re finished with it. Wireframing Essentials. Queries can be saved and used later so you don’t have to keep redesigning them. If you maximize your window or make it larger by resizing it you’ll see the buttons take more space.
– Pdf microsoft access 2013 free
Remember keep in mind the skill level of the average user of your database. If you’re building this database for other people to work with you want to make it as simple and as user-friendly for them. Plus having your Forms drawn-out acts like a roadmap, you can see on paper what you should be designing on the screen and it just makes it easier. The next step, gather together all your printed Reports. You probably have paper Forms you are using now, or at least Reports you been generating with Excel or Microsoft Word.
Get those altogether so you can see the different types of Reports that you’re going to need to generate from your database. The bottom line here is Plan Ahead! A complex database takes a lot of planning. Now don’t worry about laying out your Queries at this point, Queries are usually something you design on-the-fly, but do take the time to make yourself a list of Tables, the Fields needed in each one of those Tables, a rough sketch of what Forms you want and how you want your on-screen display to look, and what Reports the database should generate.
Now you might even see Microsoft Access show up on the Start Menu directly if you’ve used it recently but if not click on All Programs, click on the Microsoft Office folder and then click on Microsoft Access This opens up Microsoft Access, now unlike Word and Excel, Access does not automatically start you in a blank document. We have to first create a database file. You’ll see here, in the center of the window a section called available templates. Now Microsoft has provided you with some prebuilt databases that you can use if you’re in a hurry.
We’re here today to learn how to build a database from scratch so I’m going to click on blank database. Now down in the bottom right corner you’ll see a section to specify a filename for your database.
Access wants to know what name you want to give your database. Right now it says database1. You may or may not see that accdb depending on your Windows settings and whether or not you see file extensions. In either case don’t worry about it that just tells Windows that this is an Access database file. That is a fictional company that i’ve set up, call PCResale. We’re going to build a customer database for this fictional company.
Now below the filename you’ll see the folder that Access is going to place this database file in. If you want to change it you can click on the little folder button right here and browse for a different folder to place your database in, but since I’m happy with that folder I’m going to click on the create button.
That’s because Access started out by creating a blank Table for us, Now I don’t want to start by just entering data into a blank Table. I want to define the Table first. I want to setup the rules for this Table so I’m going to come over here and click on this X that will close down this Table.
Right now its empty and we’re going to put some stuff in it in a minute. Now before we start actually working with database lets learn about the parts of the Access interface.
If you’ve worked with Word or Excel you’ll find a lot of this is the same, but there are a few different things. In the upper right corner we have are familiar window controls. These include the Minimize, Restore or Maximize buttons and the Close button below.
Below the Title Bar we have the Ribbon. The Ribbon is a new menu interface that was introduced in Access If you’ve used versions of Access before you’ll find the Ribbon is a radically different menu interface. Personally I didn’t care much for the Ribbon when it first came out and Access but after working with it for a while I finally came around.
The Ribbon really is better than the old menu interface. The Ribbon is divided up into different tabs. Here you can see the Home Tab. There is a File Tab, a Create tab where we will go to create objects, External Data for working with data outside of our database and more advanced Database Tools. We’ll spend most of our time on the Create and Home tabs. Now right now you’ll see that most of the features on the Home tab are grayed out.
We dont have any Queries, Tables or Forms to work with so we have to create those things on the Create tab. Inside each tab you can see the various command buttons are organized into groups. For example these are all the buttons here that deal with creating different Tables. Here you can see Queries and Forms and so on.
Now the Ribbon is designed to be dynamic it will change based on what you’re doing. It will also change based on how large your Access window is. You can see as a resize my window the buttons on the Ribbon change, the groups will collapse or expand based on how much space they have available, so the menus you see on my screen might not look exactly the same on your screen.
If you maximize your window or make it larger by resizing it you’ll see the buttons take more space. If screen space is at a premium and you don’t have a lot of room in your window you can minimize the Ribbon by simply double-clicking on one of the Ribbon tabs.
That will shrink it up and save you some more space. To bring it back just double click again and it will re-expand the ribbon. You might see additional tabs on the Ribbon based on what you’re doing. These are menu options that only appear if you’re working with Tables. So as you can see Access automatically hides these commands if your not working with Tables, you don’t need to see them. In the upper left corner of the window you’ll find a Quick Access toolbar.
The Quick Acess toolbar is right here and out-of-the-box Access comes with Save, Undo, and Redo as commands on this toolbar. We’ll talk about what these commands do in a little bit but you can use the Quick Access toolbar to add commands that you use all the time, let’s say for example that you always use the Table design, well you can right-click on it and go add the Quick Access toolbar and that will put a copy of that button right up here.
I’m going to get rid of it though by right clicking on it and selecting remove from Quick Access toolbar. This is where a list of all of your Access objects will appear, your Forms, Reports, Tables, Queries, and so on.
Here I’ve opened one of my other databases and you can see in the Navigation Pane a list of Tables. If I scroll down you’ll see different groups for Queries, Forms, and Reports. I can open up these groups by clicking on the little double Chevron and that’ll open up all the queries.
Until you create your first objects though your Navigation Pane will be blank. You can resize the Navigation Pane if you’d like to by clicking on its right most border and dragging. You can also hide it.
Way down near the bottom of the window you’ll see the Status Bar. Most of the time the Status Bar just says “Ready” but it does pop up occasional messages. You can also program it with custom prompts, here. We’ll see how to do this in the future lesson. Finally we have this big area here call the Object Pane. This is where the objects in your database will appear when you open them up. You can see Tables, Queries, Forms, Reports, whatever other objects you open up. So that’s a quick tour of the Microsoft Access interface, in the next lesson will begin by building our first Table.
Go back to your index cards and find the one for your customer Table. These are all Fields going to add to the Table in the database. Now there are two ways to create Table. Click on the Create tab and here you’ll see Table and Table Design. Now Table by itself puts us in datasheet view and this is what Access started before. I personally don’t like datasheet view, datasheet view is okay for entering data. This is Table Design View.
This is where we specify the structure of the Table first before putting any of the data in it and that’s the main benefit of using a Table in Access is you can define the structure of the Table and enforce rules and what kinds of data go into each Field. Spreadsheet programs like Microsoft Excel just let anybody type anything anywhere on the sheet where with Access we want strict control over what information goes where. The first column is where we type in the Field name.
Then we specify the data type what kind of information does the Field hold? Then optionally we can type a description. Remember I don’t like putting spaces in my Field names. So its capital First no space capital Name.
Now this is really more of a matter of style then anything else but this is my personal preferred style. In fact they might not even capitalize first name that’s fine as well. The problem basically is that if you do use spaces in your Field names later on we get in the programming or writing Macros or some SQL statements you have to remember to put brackets around everything and that just becomes a pain.
So choose one method of naming your Fields and maintain consistency. Personally I recommend that you stick with my naming conventions. Once you’ve got FirstName typed then press the tab key.
Now were at the data type column. This is where you specify what kind of data the FirstName Field is going to store. If you drop this box down you’ll see the complete listing of all the different data types that Microsoft Access supports. Short Text fields are most printable characters A-Z, a-z, plus pretty much anything else in the keyboard that is a printable character. Short Text fields can be up to characters long so they can store a decent amount of information.
The next datatype is a Long Text fields. Long Text fields are essentially very long Text fields. Where Short Text fields can only store characters of data, a Long Text field can store over 65, characters of data. Long Text fields also support formated text. So if you want to bold or italicized text or maybe change the text color you can use a Long Text field for that. Don’t use Long Text fields for everything though because they do lack some of the functionality that simple Short Text fields have.
We’ll talk about the differences in a future class. Essentially for small bits of information like someone’s name or address or phone number use a Short Text field. If you had to type in lots and lots of information or formatted text use a Long Text field. Number fields can store either counting numbers also called Integers or Decimal values also called floating-point values. Unlike Text fields you can perform calculations on numbers so you can calculate the Sum or Average of a bunch of Number fields for example.
Next we have a very important datatype called an AutoNumber. An AutoNumber is essentially an automatic counter field. It will start at 1 with the first record and acts as automatic incerment that numbers for you with each following record so the next records 2 and 3 and so on. You don’t need to worry about maintaining that AutoNumber yourself.
We’ll use AutoNumbers for identifying unique Records. Next we have have OLE objects. It can be a picture, a document file, a video an Excel spreadsheet, a sound clip.
Anything you can copy and paste in Windows can generally be stored in an OLE object. Similar to an OLE object, an Attachment data type field can store pretty much any kind of file in Windows.
Attachment fields have some advantages and disadvantages, for example attachments can be. We’ll talk about the differences Attachments and OLE objects in a future lesson. A Hyperlink field is good for storing a link to a webpage or an e-mail address. If the user clicks on the Hyperlink their web browser or e-mail program will launch automatically. A Calculated field stores the result of a calculation in your Table.
There are some exceptions, we’ll talk about those later. The Lookup Wizard allows you to look up the value from another Table. For example you could use it to select a customer while you’re in your order Table.
So now we know the basics of all the data types, which one do you think we should use for the FirstName field? 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. Introduction to Microsoft Access But also many other tutorials are accessible just as easily!
You should come see our Database documents. You will find your happiness without trouble! The latest news and especially the best tutorials on your favorite topics, that is why Computer PDF is number 1 for courses and tutorials for download in pdf files – Introduction to Microsoft Access Download other tutorials for advice on Introduction to Microsoft Access We will do everything to help you!
And you dear surfers what you need? The best course and tutorial, and how to learn and use Introduction to Microsoft Access The introduction to the Microsoft Access interface and covers the various aspects of database creation and management in Access is very usefull. 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.
Pdf microsoft access 2013 free –
Get started with a FREE account. Microsoft Access Step by Step ebook – Home. Pages · · MB Download MS Access Tutorial (PDF Version). Download an introduction to the Microsoft Access interface and covers the various aspects of database creation and management in Access.