As our working example, we’ll create an inventory management database that keeps track of all the items in a business’s inventory. Your business most likely has different needs and specifications, but the principles we’ll use can be just as easily applied to lots of other small business applications. By the time you’re finished you should hopefully have the tools to create business databases that are accurate, efficient, organized and easy to use.
[Note: The images below are from Microsoft Access 2010. The methods are the same in versions 2007-2013.]
Creating the Database
To start, open Microsoft Access on your computer. Click on “new database”, and give it a name on the right. I’m giving it the name “Inventory”. Click “Create”.
Object Types
To the left of the screen is your navigation pane. You’ll use this to move around the database. You can have the navigation pane show all objects (as below), only one type (that’s what I usually use), or relationships. I personally don’t often use the types of relationships shown by the navigation pane. You can also assign objects to groups and use the navigation bar to just show those groups.
Access databases are broken up into six object types – tables, queries, forms, reports, macros and modules:
You may have noticed that I’ve used a particular way to name each object I’ve created. Each object name has two parts: a three-letter abbreviation denoting the the object type (“tbl”, “qry”, etc.), followed by an underscore and the name I’ve selected for that particular object. Most programmers leave out the underscore, but I find that it keeps things more organized for me.
Using a systematic way of naming objects is called using a naming convention. Naming conventions keep things organized and manageable. It doesn’t really make a different what your convention is, just so long as you have one. However, you should be careful to avoid using special characters and spaces in object names.
Tables
Tables are basically sophisticated spreadsheets. Each table has several columns and an unlimited number of rows. The columns are where you specify what type of information should be stored, and the rows actually contain the information.
For each table, you’ll give appropriate names for each column and specify what type of data is collected in each. Be very careful to avoid spelling mistakes in table or column names (or any other type of name, for that matter), as these will come back to haunt you later. You’ll edit the table’s structure in design view and switch to datasheet view to enter and edit the actual data. You can switch between views using the button in the upper left:
Design View
|
Datasheet View
|
Items (design view)
Transactions (design view)
|
Items (datasheet view)
Transactions (datasheet view)
|
Queries are “questions” that you ask the database. A query can return a filtered list or calculations – it all depends what you design the query to do. In addition to the standalone queries shown in the navigation pane, forms and reports are also based on their own queries.
As with tables, queries can be viewed in design or datasheet view. There’s also a SQL view, but don’t worry about that now. Again, you can use the button on the top left to toggle between views.
The query shown here, first in design view and then in datasheet view, shows us how many items we have in inventory by summing up all the quantities in our transaction table.
Forms
Although users can interact directly with the data in the tables, forms allow you to create a good user interface that will transform your collection of tables into a cohesive program. A good database should use forms as the primary way that users interact with the data. If you do your job well, you’ll find it easier to use your forms than to go to the tables directly.
You’ll discover an array of fonts, colors, lines and formats you can use to achieve the look you like most. I find that almost every one of my projects takes on its own color theme.
Forms can be viewed in a few different ways, which we’ll explore in more detail later:
- Design: Allows you to place controls (labels, buttons, lines etc.) where you want them.
- Datasheet: Ignores all formatting and just shows you the data.
- PivotTable: Allows you to make a pivot table of the data.
- PivotChart: Allows you to make a pivot chart of the data.
- Layout: Gives you the ability to preview the data and make formatting changes at the same time, instead of flipping back and forth to see what your changes look like.
- Form: A final, formatted view for users to interact with the data.
You’ll be using design view and form view most often.
In design view, the form looks like this:
In form view, the form looks like this, ready for the user to use it:
Each form has its own module attached where you can add in custom code for that form (more on modules below). Code can be attached to each control within the form, so you can call an event (when the user does X, the program does Y) at almost any point in the user’s interactions with the information. In the sample form here, we have a delete button for each record (the button with the red “X” and black exclamation point) and a close button for the form. I’ve also added in some code in the form’s module that capitalizes all the letters in the ItemCode textbox after the textbox has been updated by the user.
Reports
Once you have a collection of (hopefully) good data, you can use reports to compile all that data into a format that’s more understandable, presentable and easier to analyze. Many database platforms come with a report builder, but the one that comes with Access is exceptionally powerful and versatile, and is probably the best one on the market.
Just like forms, each report has a module attached to it. The reports themselves are based on queries, although the queries are usually part of the report and are not standalone queries.
-
Print Preview: These reports are essentially PDFs and do not allow the user to interact with the data. The modules in these reports are most often used for formatting purposes only. Print Preview is included in all versions of Access.
- Report View: This is one of the best additions to Access 2007. Buttons and code can be added to these reports so that users can actually modify the data while they’re reviewing the report. It’s like a cross between a form and a report. The modules in these reports are most often used to edit the data or how it displays, or to open up a related form or report.
A macro is a collection of commands that you group together as one command group. When you run the macro, it’ll run all the commands that are in it. I rarely use macros. Code is much more efficient. But, when code isn’t available (as in web databases), or in certain situations when you want to make a set of commands easy to apply, macros play a useful role.
All the code that you need to write goes into a module. Even as a novice user, you’ll probably be using the built- in modules when working with your forms or reports. Code can be as simple as telling a page to refresh, or it can be so complicated that it can qualify as its own stand-alone program.
Code has a very important role in making your database perform at its best. Without any code, you’re restricted to data entry and reporting. With code, you can automate much more and make the database extremely user friendly. When we get to the posts about modules, we’ll address the three main purposes of code: functional code (making the database work), intuitive code (creating a user friendly environment) and logical code (performing data analysis and updates).
There’s much more to explore in depth about each section, but I hope this provided a good overview for you.
Please feel free to comment or email me with any questions. My email is denglander@workstreamsystems.com.