access database manual

access database manual

Access preserves the original table as a backup. If you see a check box labeled Show introductory pages?, check the box, and then click Back twice to see the introduction. If you don't want to see the introductory pages again, uncheck Show introductory pages? These articles can help: It sounds like it might be helpful to connect you to one of our Office support agents. That is indeed a great choice. The truth is that Access is a powerful tool that offers numerous features to manage your data efficiently. In this guide, I’ll show you how to use MS Access. Here is a short list of some of the main things that you can perform in Access (not an exhaustive list): Create tables, and link multiple tables together Build Reports Add charts, such as Column, Bar and Pie Charts Apply different functions and formulas, similar to those that you can apply in Excel. In this guide, I’m going to cover the following 5 topics: Creating a table in Access Exporting data Deleting a table Importing data into Access Applying basic formulas There might be a slight variation in the menus depending on the Access version that you’re currently using. Yet, the concepts are virtually the same for Access 2010, 2013 and 2016. For illustration purposes, I’ll use Access 2016 to demonstrate how to use MS Access. So let’s now dive into the steps needed to create a table in Access. It is indeed a fundamental ingredient in Access, and a good starting point to better understand how to use MS Access. I’ll use an example to illustrate how to create a table in Access to store your data. You’ll notice that by default, a new table was created. More specifically, for the Product column, choose the format of Short text from the drop-down list: Then, type the name “Product” (instead of the default name of “Field1”) to represent your Product column: Similarly, for the Price column, press on the Click to Add on the adjacent column to the right.

• access database manual pdf, access database manual, access database user manual, microsoft access database manual, split access database manually, access database instruction manual, ms access database manual, 3700 sql database access manual, microsoft access database user manual, free access database training manual, access database manual, access database manual, access database manual office 2013, access database manual, microsoft access database manual, microsoft access database user manual.

This opens it in 'Datasheet View' They provide functionality for joining tables, performing calculations and filtering data with complex queries. This allows you to effeciently structure your data which reduces human errors and saves a lot of storage space. To maintain up-to-date information and allow maximum flexibility, reports should always be based on queries and not on tables! They can also be used as a manual data upload tool. As reports, forms should always be based on queries and not tables! To shrink them to their necessary size, you may want to use occasionally the 'Compact and Repair Database' function. By continuing to browse this site, you agree to this use. Learn more Let’s take a walk through the paths you can take to create an Access desktop database, add data to it, and then learn about next steps towards customizing and using your new database. A choice of templates is the first thing you’ll notice when you start Access, and you can search online for more templates. If the database requires a login, log in again. To create a table: You can either start entering data in the empty field (cell) or paste data from another source like an Excel workbook. You can also select contiguous columns and drag them all to a new location. This works best if the data is separated into columns. If the data is in a word processing program, such as Word, either use tags to separate the columns or convert into a table format before copying. Linking can be a good option if you have multiple users updating the data and you want to make sure that you are seeing the latest version or if you want to save storage space. You can choose whether you want to link to or import data for most formats. See Import or link to data in another Access database for more information. If you don't see the right format, click More. Here are the external sources that you can import data or link from: The wizard then provides a simple way to organize the data into separate tables.

In our case: The first ID column has the Data Type of Double The second Product column has the Data Type of Short Text While the third Price column has the Data Type of Currency ID column: Product column: Price column: You can change the format for each of your fields through this screen. To do that, select your desired format from the Data Type drop-down list. Since in our case, all the fields (i.e., ID, Product and Price) already have the desired format, you can then just go ahead and press Next: (5) Now you can choose among 3 options to either: Let Access add primary key; or Choose my own primary key; or No primary key As mentioned earlier, a Primary Key contains unique key to identify each record under your table. Since the ID column already contains our unique keys, then you’ll need to choose the second option of “Choose my own primary key” and set it to the ID column. Then, click on Finish: Save Import Steps (7) Now imagine that you have to repeat all those import steps every-time you’d like to import your latest table into Access. That would definitely be a tedious task. Luckily, Access has the capability to save your import steps (in a similar fashion to what you’ve already seen for saving the export steps). In the final topic of this guide on how to use MS Access, I’m going to review the steps to apply basic formulas, such as the total sum. Let’s say that you want to derive the total price across all of your products. So how do you accomplish this goal in Access. Access is indeed a powerful tool that would make your life easier when it comes to managing your data. You may also want to check the following guide for more advanced concepts that you can apply in Access. To maximize your use of. Total Access Statistics for analyzing the data in your Microsoft Access databases, a professionally written and printed user manual is included. The help file is part of the program and provides context sensitive information when invoked.

Then, choose the format of Currency from the drop-down list: Finally, type the name “Price” to represent your Price column: Adding Records into your Table (5) Now that you have your columns, you’ll need to add the actual data into your table. A primary key can be used to link tables in Access. You can assign your own unique key for each record, or let Access automatically generate those values for you. For the time being, let Access generate these unique IDs (in the form of numbers) for each of your records under your table. Then, press OK: (8) You can open and edit your newly created table at any time by double-clicking on the table’s name under the All Access Object menu: In the next part of this guide, I’m going to show you two methods to export your data from Access into Excel. Here, I chose the standard Excel format of xlsx Export Options. It’s not a must option to use, but it can save you some time, especially if you update your table in Access frequently, and need a quick way to export your data on an ongoing basis. If this is what you want, then press Yes ): So far, you have seen how to create a table in Access and then export it. But what if you want to delete your table. In the next section, I’m going to review the steps to delete a table that is no longer needed. You can easily delete a table by following these steps: (1) The first thing that you’ll need to do is to close the table you’d like to delete. Then, select Delete from the drop-down list: Click on Yes to delete your table: Now that we saw how to create and delete a table, we are going to address the following questions: What if you have a large number of records. Are you going to type them in Access one-by-one. In the next section, I’m going to demonstrate how you can import your data from Excel into Access. So how do you import the above table into Access.

You’ll learn more about sharing Access with groups of people in Part 6. Understanding Access Databases As you already know, a database is a collection of information. In Access, every database is stored in a single file. That file contains database objects, which are the components of a database. Database objects are the main players in an Access database. Altogether, you have six different types of database objects: Tables store information. Tables are the heart of any database, and you can create as many tables as you need to store different types of information. A fitness database could track your daily running log, your inventory of exercise equipment, and the number of high-protein whey milkshakes you down each day, as three separate tables. Queries let you quickly perform an action on a table. Usually, this action involves retrieving a choice bit of information (like the 10 top-selling food items at Ed’s Roadside Diner or all the purchases you made in a single day). However, you can also use queries to apply changes. Forms are attractive windows that you create, arrange, and colorize. Forms provide an easy way to view or change the information in a table. Reports help you print some or all of the information in a table. You can choose where the information appears on the printed page, how it’s grouped and sorted, and how it’s formatted. Macros are mini-programs that automate custom tasks. Macros are a simple way to get custom results without becoming a programmer. Modules are files that contain Visual Basic code. You can use this code to do just about anything—from updating 10,000 records to firing off an email. Access gurus refer to all these database ingredients as objects because you manage them all in essentially the same way. If you want to use a particular object, you add it to your database, give it a name, and then fine-tune it. Later on, you can view your objects, rename them, or delete ones you don’t want anymore.

It also includes the help file and manual. The results are placed. Even though Microsoft has spent millions of dollars making Access easier to use, most people still see it as the most complicated Office program on the block. They’re probably right. Access seems more daunting than any other Office program because of the way that databases work. Quite simply, databases need strict rules. Other programs aren’t as obsessive. For example, you can fire up Word, and start typing a letter straight away. Or you can start Excel, and launch right into a financial report. But Access isn’t nearly as freewheeling. Before you can enter a stitch of information into an Access database, you need to create that database’s structure. And even after you’ve defined that structure, you’ll probably want to spend more time creating other useful tools, like handy search routines and friendly forms that you can use to simplify data lookup and data entry. All of this setup takes effort and a good understanding of how databases work. In this chapter, you’ll conquer any Access resistance you have, and learn to create a simple but functional database. Along the way, you’ll get acquainted with the slick Access user interface, and you’ll learn exactly what you can store in a database. You’ll then be ready to tackle the fine art of database design, which is covered in detail throughout this book. FREQUENTLY ASKED QUESTION: Using Someone Else’s Database Can I use an Access database I didn’t design. Although every database follows the same two-step process: first somebody creates it and then people fill it with information, the same person doesn’t need to perform both jobs. In fact, in the business world, different people often work separately on these two tasks. In fact, if the database is well designed, people who have little understanding of Access can still use it to enter, update, and look up information. Amazingly, they don’t even need to know they’re running Access at all.

Don’t stick with the name Access picks automatically (like “Database1.accdb”). Instead, pick something more descriptive. In this example, Bobblehead.accdb does the trick. It’s generally safest to stay away from other special characters, some of which aren’t allowed. Figure 1-2. This database will be named Bobblehead.accdb. As you can see by the file path below the File Name box, it will be saved in the folder C:\Users\matthew\Documents. You can edit the file name by typing in the File Name box, and you can browse to a different folder by clicking the folder icon. Note Depending on your computer settings, Windows may hide file extensions. Instead of seeing the Access database file MyScandalousWedding.accdb in file-browsing tools like Windows Explorer, you may just see the name MyScandalousWedding (without the.accdb part on the end). In this case, you can still tell the file type by looking at the icon. If you see a small Access icon next to the file name, that’s your signal that you’re looking at an Access database. Choose the folder where you want to store your database. Like all Office programs, Access assumes you want to store every file you create in your personal Documents folder. If this isn’t what you want, click the folder icon to show the File New Database window, browse to the folder you want ( Figure 1-3 ), and then click OK. Figure 1-3. The File New Database window lets you choose where you’ll store a new Access database file. It also gives you the option to create your database in the format used by older versions of Access (.mdb), instead of the more modern format used by Access 2007, Access 2010, and Access 2013 (.accdb). To change the format, simply choose the corresponding Access version from the “Save as type” list, as shown here. Click the big Create button (under the File Name box). Access creates your database file and then shows a datasheet where you can get to work creating your first table.

POWER USERS’ CLINIC: Telling Access Where to Store Your Databases Access always assumes you want to store databases in your Documents folder. And though you can choose a different location every time you save or open a database, if there’s another folder you need to visit frequently, then it makes sense to make that your standard database storage location. You can configure Access to use this folder with just a few steps: Make sure you’ve opened a database or created a new one. You can’t make this change from the window you see when you first start Access. The Access Options window appears. In the list on the left, choose General. In the page on the right, look for the “Creating databases” heading. Underneath, you’ll find a “Default database folder” text box. Type the path to the folder you want to use (like C:\MyDatabases ), or click Browse to navigate to it. When you’re finished, click OK to save your changes. Once you create or open a database, the Access window changes quite a bit. An impressive-looking toolbar (the ribbon ) appears at the top of your screen, and a Navigation Pane shows up on the left. You’re now in the control center where you’ll perform all your database tasks ( Figure 1-4 ). Figure 1-4. The navigation pane on the left lets you see different items (or objects) in your database. You can use the navigation pane to jump from a list of products to a list of customers and back again. The ribbon along the top groups together every Access command. This ribbon is the mission control that lets you perform various tasks with your database. The document window in the middle takes up the rest of the window. This window is where you’ll do your work, such as designing tables and entering data. If you haven’t used the ribbon before (either in Access or in another Office program), the Introduction covers the basics of how the ribbon works. Otherwise, carry on to the next section, where you’ll learn how to add a table to your brand-new, empty database.

Note Designing a database is the process of adding and configuring database objects. For those keeping score, an Access database can hold up to 32,768 separate objects. In this chapter, you’ll consider only the most fundamental type of database object: tables. But first, you need to create a blank database you can work with. Starting a Database When you start Access, you begin at the welcome page. From there, you’re just a few clicks away from generating a database of your very own. In this chapter, you’ll slap together a fairly straightforward database. This example is designed to store a list of prized bobblehead dolls. (For those not in the know, a bobblehead doll is a toy figure with an oversized head on a spring, hence the signature “bobbling” motion. Here’s how to create a blank new database: Start Access. Access starts you out with what is, for Microsoft, a remarkably streamlined window ( Figure 1-1 ). Here you can create a new database or open an existing one. Figure 1-1. When you start Access, you see this two-part welcome page. On the left is a list of recently opened databases (if you have any). On the right is a list of templates that you can use to create a new database. You’ll get the same list of templates as when you first launch Access. Click the “Blank desktop database” template. When you choose to create a blank database, that’s exactly what you get—a new, empty database file with no tables or other database objects. Starting from scratch is the best way to learn about Access. It’s also the favorite choice of database experts, who prefer to create everything themselves so it’s exactly the way they like it. Other templates let you create databases that are preconfigured for specific scenarios and certain types of data. The box on Templates: One Size Fits Some has more information. The cool-sounding “Custom web app” template is a special case. It lets you create a web-enabled database that runs on SharePoint.

You’ll explore this new feature (and its limitations), in Chapter 20. No matter which template you click, Access pops open a new window that lets you choose a name and location for your new database ( Figure 1-2 ). UP TO SPEED: Templates: One Size Fits Some The example in this section shows you how to create a blank database. However, if you scroll down (on the right side of the Figure 1-1 ), you’ll find a long list of prebuilt databases, which are known as templates. Templates aim to save you the work of creating a new database and let you jump straight to the fine-tuning and data-entry stage. As you might expect, there’s a price to be paid for this convenience. Even if you find a template that stores the type of information you want to track, you might find that the predefined structure isn’t quite right. For example, if you choose to use the Home Inventory template to track all the stuff in your basement, you might find that it’s missing some information you want to use (like the projected resale value of your stuff on eBay) and includes other details you don’t care about (like the date you acquired each item). To make this template work, you’ll need to change the design of your table, which involves the same Access know-how as creating one. In this book, you’ll learn how to build your own databases from the ground up and customize every square inch of them. Once you’re an Access master, you can spend many fun hours playing with the prebuilt templates and adapting them to suit your needs. To give it a whirl, click one of a dozen or so templates that are shown in the main Access window. Or, even better, hunt for more by using the Search box at the top of the Access window, which scans through the thousands of templates available on Microsoft’s Office website. Type a file name for the database you’re about to create. Access stores all the information for a database in a single file with the extension.accdb (which stands for “Access database”).

Building Your First Table Tables are information containers. Every database needs at least one table—without it, you can’t store any data. In a simple database, like the Bobblehead database, a single table (which we’ll call Dolls) is enough. But if you find yourself wanting to store several lists of related information, you need more than one table. In the database BigBudgetWedding.accdb, you may want to keep track of the guests that you invited to your wedding, the gifts that you requested, and the loot that you actually received. In Chapter 5, you’ll see plenty of examples of databases that use multiple tables. Figure 1-5 shows a sample table. Figure 1-5. In a table, each record occupies a separate row. Each field is represented by a separate column. In this table, it’s clear that you’ve added five bobblehead dolls. You’re storing information for each doll in five fields (ID, Character, Manufacturer, PurchasePrice, and DateAcquired). Before you start designing this table, you need to know some very basic rules: A table is a group of records. A record is a collection of information about a single thing. In the Dolls table, for example, each record represents a single bobblehead doll. In a Family table, each record would represent a single relative. In a Products table, each record would represent an item that’s for sale. You get the idea. When you create a new database, Access starts you out with a new table named Table1, although you can choose a more distinctive name when you decide to save it. Each record is subdivided into fields. Each field stores a distinct piece of information. For example, in the Dolls table, one field stores the person on whom the doll is based, another field stores the price, another field stores the date you bought it, and so on. Tables have a rigid structure. In other words, you can’t bend the rules. If you create four fields, every record must have four fields (although it’s acceptable to leave some fields blank if they don’t apply).

Newly created tables get an ID field for free. The ID field stores a unique number for each record. (Think of it as a reference number that will let you find a specific record later on.) The best part about the ID field is that you can ignore it when you’re entering a new record. Access chooses a new ID number for you and inserts it in the record automatically. You’ll learn much more about ID fields on AutoNumber. UP TO SPEED: Database Planning for Beginners Many database gurus suggest that before you fire up Access, you should decide exactly what information you want to store by brainstorming. Here’s how it works. Some details are obvious. For example, for the bobblehead doll collection, you’ll probably want to keep track of the doll’s name, price, and date you bought it. Other details, like the year it was produced, the company that created it, and a short description of its appearance or condition may require more thought. Once you’ve completed this process and identified all the important bits of data you need, you’re ready to create the corresponding table in Access. The bobblehead doll example demonstrates an important theme of database design: First you plan the database, and then you create it using Access. In Chapter 5, you’ll learn a lot more about planning more complex databases. Creating a Simple Table When you first create a database, it’s almost empty. But to get you started, Access creates your first database object—a table named Table1. The problem is, this table begins life completely blank, with no defined fields (and no data). If you followed the steps in the previous section to create a new database, you’re already at the Datasheet view ( Figure 1-5 ), which is where you enter data into a table. All you need to do is customize this table so that it meets your needs. You can customize a table in two ways: Design view lets you precisely define all aspects of a table before you start using it.

Almost all database pros prefer Design view, and you’ll start using it in Chapter 2. Datasheet view is where you enter data into a table. Datasheet view also lets you build a table on the fly as you insert new information. You’ll use this approach in this chapter. The following steps show you how to turn a blank new table (like Table1) into the Dolls table by using the Datasheet view: To define your table, simply add your first record. In this case, that means choosing a bobblehead doll to add to the list. For this example, you’ll use a nifty Homer Simpson replica. Note It doesn’t matter which doll you enter first. Access tables are unsorted, which means they have no underlying order. However, you can sort them any way you want when you need to retrieve information later on. In the datasheet’s rightmost column, under the “Click to Add” heading, type the first piece of information for the record (see Figure 1-6 ). Based on the simple analysis you performed earlier, you know that you need to enter four fields of information for every doll. Although you could start with any field, it makes sense to begin with the name, which is clearly an identifying detail. Figure 1-6. To fill in your first record, start by entering something in the first field of information (like the doll name “Homer Simpson”). Then, hit Tab to jump to the second column, and then enter the second piece of information. Ignore the ID column for now—Access adds that to every table to identify your records. Press Tab to move to the next field, and return to step 2. Repeat steps 2 and 3 until you’ve added every field you need, being careful to put each separate piece of information into a different column ( Figure 1-7 ). You may notice one quirk—a harmless one—when you add your first record. As you add new fields, Access may change the record’s ID value of the record (changing it from 1 to 2 to 3, for example).

Because the new record hasn’t been inserted yet, every time you change the table’s design by adding a new field, Access starts the process over and picks a new ID number, just to be safe. However, there’s really no reason to worry about the ID number. As long as it’s unique—and Access guarantees that it is—the exact value is unimportant. Figure 1-7. The only problem with this example so far is that as you enter a new record, Access creates spectacularly useless field names. You see its choices at the top of each column (they have names like Field1, Field2, Field3, and so on). The problem with using these meaningless names is that they may lead you to enter a piece of information in the wrong place. You could all too easily put the purchase price in the date column. Note If you press Tab without entering any information, you’ll move to the next row and start inserting a new record. If you make a mistake, you can backtrack using the arrow keys. UP TO SPEED: Putting Big Values in Narrow Columns A single field can hold entire paragraphs of information. But if you have lengthy values, you may find yourself running out of viewing space while you’re typing them into a narrow column. And although you’re free to scroll forward and backward through your field, this gets annoying fast. Most people prefer to see the entire contents of a column at once. Fortunately, you don’t need to suffer in silence with cramped columns. To expand a column, just position your mouse at the right edge of the column header. (To expand a column named Field1, move your mouse to the right edge of the Field1 box.) Then, drag the column to the right to resize it as big as you want. If you’re just a bit impatient, there’s a shortcut. Move the mouse over the right edge of the column, so it turns into a two-way arrow. Then, simply double-click the column edge.

The column resizes itself to fit its largest piece of information (as long as doing so doesn’t stretch the column beyond the edge of the Access window). It’s time to fix your column names. Double-click the first column title (like Field1). The field name switches into Edit mode. Type a new name, and then press Enter. Repeat this process until you’ve cleaned up all the field names. The proper field names for this example are Character, Manufacturer, PurchasePrice, and DateAcquired. Figure 1-8 shows how it works. Figure 1-8. To specify better field names, double-click the column title. Next, type the real field name, and then press Enter. Page 90 has more about field naming, but for now just stick to short, text-only titles that don’t include any spaces, as shown here. Tip Don’t be too timid about tweaking your table. Access asks you to supply a table name (see Figure 1-9 ). Figure 1-9. A good table name is a short text title that doesn’t have any spaces (like Dolls here). Type a suitable table name, and then click OK. Congratulations! The table is now a part of your database. Note Technically, you don’t need to save your table right away. Access prompts you to save it when you close the datasheet (by clicking the X at the document window’s top-right corner), or when you close Access. As you can see, creating a simple table in Access is almost as easy as laying out information in Excel or Word. But before you get to that stage, it makes sense to take a closer look at how you edit your table. Editing a Table You now have a fully functioning (albeit simple) database, complete with one table, which in turn contains one record. Your next step is filling your table with useful information. This often-tedious process is data entry. To fill the Dolls table, you use the same datasheet you used to define the table. You can perform three basic tasks: Editing a record.