10 Best Access Training

Best Access Training in 2023

---

---

---

---

---

---

---

---

---

---

---

Mastering Microsoft Access - Creating Great Useful Databases - Lesson 1

In the next few lessons I will guide you step by step through some of the more common and not so common features of Microsoft Access and teach you how to set up your own database for great record keeping and reports.

Step1 - Creating a New Database

The first step in creating any database is simply open Access and click on File then scroll down to "New" and click. If an option pops up on the right side of the page, then choose "blank database" from the list. Then, you will be prompted to name your database and choose a save location. If this is a database that you will be using a lot, or might possibly want to share it, I recommend saving it to "C:\Documents and Settings\All Users\Desktop" The reason I always use this is because it will show up on your desktop which is easy to find. But if you share it, it will show up on the person who receives it's desktop as well since the file path doesn't contain your specific username on your computer.

Step2 - Understanding the Objects

When you first open your new Access Database, you'll notice that on the left hand side of the screen there is a list of items. At the top it says, "Objects". Below are the words Tables, Queries, Forms, Reports, Pages, Macros, and Modules. Click on the link below for a screenshot.

Click Here for Screenshot

I'll go into detail about the others in upcoming lessons, but first, Tables.

Step3 - Creating Tables in Access

In Access, much like Excel, the data is saved onto a table with rows and columns. One of the key issues with Access is making sure going in that you know what it is you want to track the data of, and how you want to track it, i.e., what will be the headers for each column, etc. The difference with Access is that you can do a lot more with the data once you have it in the tables.

Let's say for instance, that you want to track your sales on ebay or some other site. This is a pretty quick easy excercise for Access. First we need to determine what fields you will have in your table. That is determined by how detailed you want to get in your tracking. But for arguments sake, lets start out with just a few. Item Number, Item, Initial Cost, Fees, Category, Sold, and Sales Price. Thats not too bad. So now, lets set up our first table using this information. To do this, on the left side where the objects are, click on "Tables". Once you have done this, double click on "Create Table in Design View" Click on the link below for a screenshot.

Click Here for Screenshot

When the screen first comes up, it should look like this picture at the link below. Click on the link below for a screenshot.

Click Here for Screenshot

Now, lets name the fields for the table. In the picture you'll notice that there are 3 columns. Field Name, Data Type, and the third column, even though it doesn't show on the picture is called "Description". So, in the Field Name column, lets type the first field name, "Item Number" This will be a autonumber and used for tracking and indexing the data. So in the data type column, choose "AutoNumber". The description on all of these is optional. Now I've just gone down and typed in the rest of the field names, Item, Cost, Fees, Item Category, Sold (which is a yes/no checkbox), and Sales Price. I then filled in the Data Type and Description for each. Click on the link below for a screenshot.

Click Here for Screenshot

Now you must set your field, "Item Number" as the Primary key by right clicking on the block next to that field. Then, scroll down and choose "Primary Key". That's it. Click on the link below for a screenshot.

Click Here for Screenshot

Next you just click on the save button at the upper right hand. At this point you will be asked to name the table. Since this will be the main table where data is stored, I always use somethinglike "MainTable" but hey, that's me. Call it Fred if you like. You just have to always remember what the name is. Click on the link below for a screenshot.

Click Here for Screenshot

Once you've saved the table close it by clicking on the "X" on the upper right hand corner. This will bring you back to the main screen. You should see your new table listed under the "create table" links. Click on the link below for a screenshot.

Click Here for Screenshot

Step3 - Linking Tables in Access

Now, lets create a new table "Category". This table will be used to hold all of the possible categories you can put your items into. First double click on "Create Table in Design View" as before. Then, when the editor opens, create fields for "Category ID" and "Category". The Category ID field will be the "Primary Key". Then save the table as "Category". Click on the link below for a screenshot.

Click Here for Screenshot

Now that we have created the 2nd table lets make up some Categories to go in it. To do this click on the "View" button located in the upper left hand corner. Once you have done this you will see the empty table. You'll notice that the fields don't give you much room to type. You can actually adjust the size by holding your mouse over the divide between the cells on the top and when the pointer changes you can click and drag the cells to your preferred size. Next start typing in the Categories you want to use. You do not need to type in the ID numbers, they will auto generate. Only type the categories. I came up with 7 off the top of my head. Click on the link below for a screenshot.

Click Here for Screenshot

Now that we have typed a few Categories in, save the file and close. Next lets open the Main Table in Design view. This can be done by clicking once on "Main Table" in the main screen. Then, go up to the "Design View" Button and click that. Click on the link below for a screenshot.

Click Here for Screenshot

Once the screen opens, highlight the field "Item Category" by clicking once to the left of the row. Click on the link below for a screenshot.

Click Here for Screenshot

Now, you'll notice that at the bottom of this screen there are 2 tabs, General and Lookup. 1st lets click on the Lookup Tab. When it first comes up it will say "Text Box". If you just click on it, you'll notice that a dropdown arrow appears to the right. When this happens, scroll down to "Combo Box" and choose it. Click on the link below for a screenshot.

Click Here for Screenshot

Once you have chosen "Combo Box", this will open up the rest of the options under Lookup. So, for the second field, choose "Table/Query". Then for the third field, when you click on the field, you'll notice 2 things pop up, one is a drop down arrow, and the other is a small box with 3 dots. Click on that icon (Query Builder) to continue. Click on the link below for a screenshot.

Click Here for Screenshot

Once you have clicked on the small icon with 3 dots, your "Query Builder" will pop up. Once it does, it will ask you 1st to choose which table to use. Click on the Category table and then click the add button. This will cause a small window of that table to pop up behind the one you are on. Then click the "Close" button to start working on the Query. Click on the link below for a screenshot.

Click Here for Screenshot

To create the query, simply double click on the "Category" field within the "Category" table once. When you do, it will put it in the query for you. There is a lot more you can do here in this screen but for this demonstration, I won't go into it now. Click on the link below for a screenshot.

Click Here for Screenshot

Once you do this, just close it. It will ask you to save the query and just say Yes or Ok. This will take you back to the previous page. When you get back you will see that there is now text in the Row Source cell. It should say, " SELECT Category.Category FROM Category; ". Now go down to Bound column and type 1. This means only the 1st column of the table "Category" is used. Now go to Column Count which should also be one. This means that only 1 column total is to be used. For Column Heads, put No. This will make the headers show up as an option which you don't want. The rest of it will fill in automatically. Click on the link below for a screenshot.

Click Here for Screenshot

Now that this is finished, it's just a matter of checking to see if it worked. Save the table as before and click on the "View" icon in the upper left hand of the screen. You will then see the actual blank table pop up again. Go to the field "Category" and just click on it. Immediately you should notice that there's now a drop down list. Click on the drop down list and you should see all of the Categories that you entered previously. This will come in really handy later. Click on the link below for a screenshot.

Click Here for Screenshot

So, did it work for you? I certainly hope so. In my next lesson I will go more into detail about what other fun things you can do to enhance your database using Forms. Until then, take care and your comments are always welcome.Thanks,Vincent Pendley