written by: Curt Smothers•edited by: Tricia Goss•updated: 12/21/2009
Is it time for you to learn database programming? Maybe you have a wealth of data, but it is languishing on unwieldy spreadsheets or in Word files. This is Part 1 of a 5-part series on MS Access Database structure.
slide 1 of 6
MS Access Database Objects
MS Access uses “objects" to help the user list and organize information, as well as prepare specially designed reports. Just as a spreadsheet uses a table with rows and columns, Access has an array of data “containers" (views, filters, etc.) that elevate one-dimensional lists into the powerful realm of data managment. Those “objects" are the database table, form, query, and report.
slide 2 of 6
Tables Are Where Data Live
The table is the “warehouse" of the information you store in your database. The rows and columns of the spreadsheet become your records and fields. A record is one individual item of information that consists of fields. Picture an address card that stores information on one person. The fields in the card would be the person’s name, address, city, state, zip, and maybe a comment. A collection of cards would be a database. Each card would have the same fields, but unique information for each field, of course.
slide 3 of 6
Forms Bring Order from Overload
Access Forms are simply a unique way of viewing the information already in your table. Tables with hundreds of records and many fields can be wide and deep. Forms help us display, view, and use all or some of the information in the table. Forms also are a more convenient way to enter new data, and access existing records. Remember: whatever deletions, changes or record additions you make on a form is fed into or removed from the table.
slide 4 of 6
Queries Make the Data Focus
Large Access databases need the power of queries to find and display only the information you are interested in seeing and using. Again, queries draw from only the information already in the database table. Say you had a huge Access database with customers who lived all over the state. Using a query, you could specify only customers who live in a specific zip code. The result would be a query table. From that “new" table you could prepare a special form. Again, any changes made in the resulting query or form affect the original table.
slide 5 of 6
Reports Make It All Worthwhile
Reports are “where the rubber meets the road" in Access databases. Using a table or query, you can generate an unlimited variety of lists, groupings and calculations that are kept current through new or revised data entry. The power of queries and the reports wizardry of MS Access make the somewhat steeper learning curve worth the effort.
slide 6 of 6
Where To From Here?
Now that we have an overview of our database objects, we have some idea of what MS Access can do for us. We have seen that the database table is the foundation of all the other objects, so it follows that careful attention to the fundamentals. Those fundamentals include sensible planning that starts with table design to gather the information to be displayed in our reports. We could not, for example, ask the database to generate a query or a report based on zip codes, if we did not include the zip code as a field in our record. Therefore, database programming is mostly about “up-front" planning and design.
Microsoft Access is a powerful tool for database management. This article series introduces the new user to database structure. Read all about database objects and how to tailor your data use through tables, forms and queries.