Introduction

Welcome! Contact History Table: Query & Report

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Beginner Level 9. In this course we will focus on building a contact history for your customers, starting with creating a contact table and form. We will discuss data integrity, build queries to show records between two dates using explicit parameters, and spend time designing detailed contact reports. We will talk about report sections, Can Grow and Can Shrink properties, and cover tips like string concatenation and page numbering. You will also learn how this class fits into the overall Beginner, Expert, Advanced, and Developer series, and how to make the most of the lessons and community forums.

Navigation

Prev: B9
Next: Lesson 01

Keywords

Access Beginner, contact history, contact table, contact form, contact report, contact query, explicit query parameters, report header, page header, Can Grow, Can Shrink, string concatenation, page numbering, tab order, combo boxes, list boxes

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access 2010 Beginner Level 9, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. Today's class is going to focus on building a contact history for your customers. Every time you interact with a customer, whether it is a phone call, an office visit, and so on, you want to track that in your database. That is building a contact history.

We will start out by building a simple contact table and form. We will discuss data integrity in your database. We will build a contact query. We will learn how to show records between two dates and how to set up explicit query parameters. We will spend a lot of time building a contact report.

We will learn about the different sections: the report header, the page header. We will learn about the Can Grow and Can Shrink properties, and lots more. We will learn a ton of miscellaneous tips and tricks, including string concatenation, page numbering, and lots more.

This class follows Microsoft Access 2010 Beginner Level 8. I strongly recommend you take Level 8 and all of the previous classes before beginning this course. In Level 8, we learned about combo boxes, list boxes, tab order, and lots more.

Today's class will be using Microsoft Access 2010. Most of what I am going to be showing you today works just fine in 2007. If you are using 2003 or earlier, you can probably figure everything from today's class out. Some of the menus will be different, obviously, but most of the material should be the same.

While I strongly encourage you to build a database that I build in class, if you are lazy like me, you can download the sample database from the website at accesslearningzone.com/databases.

My courses are broken up into four groups: Beginner, Expert, Advanced, and Developer. My beginner courses are for novice users who have little or no experience with Microsoft Access. They are designed to give you an overview of the basic features of Access and cover just what you need to know to be productive.

The expert classes are designed for more experienced users who are already comfortable with Access. Expert classes go into a lot more depth about each topic than the beginner classes do, and will cover more functions, features, tips, and techniques for power users.

Once you have mastered the expert classes, move up to the Advanced lessons. You will learn how to work with macros, automation, and many more advanced features that really add enhanced functionality and professionalism to your databases.

Finally, my Developer Level courses are designed to teach you how to program in Visual Basic for Microsoft Access. This will allow you to create the most advanced databases possible and unlock the true potential of Microsoft Access.

Each series is broken down into different numbered levels, starting with level one. Each subsequent level teaches you new and different topics in Microsoft Access, building on the lessons learned in the previous classes. Once you have finished all the beginner classes, move up to the expert series, then the Advanced, and finally the Developer lessons.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders, tracking accounts payable, learning the SQL programming language, and lots more. You can find complete details on all these seminars and more on my website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course using my custom video player software or the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class videos, as long as you have an active internet connection.

Here you will see all of the questions that other students have asked as well as my responses to them and comments that other students may have made. I encourage you to read through these questions and answers as you start each lesson and feel free to post your own questions and comments as well.

If you are not watching the lessons online, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend that you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually create the same database that I make in the video, step by step.

Do not try to apply what you are learning right now to other projects until you master the sample database from this class. If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum.

Most importantly, keep an open mind. Access might seem intimidating at first, but once you get the hang of it, you will see that it is real easy to use.

Now let us take a closer look at exactly what is covered in today's class.

In today's class, we are going to build a basic contact manager. In lesson 1, we will start by creating the contact table and form. In lesson 2, we will build a query to show contacts for a single date and we will learn about explicit query parameters.

In lesson 3, we are going to learn how to create a contact report. We are going to see how to build a report from scratch.

In lesson 4, we are continuing with the contact report. We are going to learn about the Can Grow, Can Shrink properties, and string concatenation.

In lesson 5, we are finishing up with our contact report. We are going to cover a bunch of miscellaneous topics, including line thickness, page numbering, section background colors, report headers and footers, and lots more.

Quiz

Q1. What is the main focus of Microsoft Access 2010 Beginner Level 9?
A. Building a contact history for customers
B. Designing advanced queries
C. Working with macros and automation
D. Learning Visual Basic programming

Q2. Which feature allows you to track every time you interact with a customer in your database?
A. Combo Box
B. Contact history
C. Tab order
D. Form header

Q3. In this course, which of the following will you learn to build from scratch to summarize contact records?
A. Combo box
B. Payment report
C. Customer invoice
D. Contact report

Q4. Which properties are discussed to handle expanding and contracting fields in reports?
A. Lock and Unlock
B. Multiline and Singleline
C. Can Grow and Can Shrink
D. Visible and Invisible

Q5. What is recommended before applying concepts to other projects?
A. Skip the lessons and work on your own
B. Watch only the first lesson
C. Master the sample database from class
D. Only read the documentation

Q6. If you are using Access 2003 or earlier, what should you be aware of?
A. None of the material will work
B. You will need to rewrite all VBA code
C. Some menus will be different but most material applies
D. The course is not suitable at all

Q7. Which topic is NOT mentioned as part of the Beginner Level 9 class content?
A. Explicit query parameters
B. Building web-based databases
C. String concatenation
D. Page numbering

Q8. What should you do if you have questions about the topics covered?
A. Email Microsoft support directly
B. Post questions in the student forums
C. Contact your local Access user group
D. Search online independently

Q9. What is the final level you can reach according to these AccessLearningZone.com courses?
A. Advanced
B. Expert
C. Developer
D. Proficient

Q10. What is the primary method suggested for best learning the course material?
A. Watch the videos multiple times without doing anything
B. Immediately apply concepts to a work database
C. Watch each lesson once, then follow along step by step
D. Only read the course transcripts

Answers: 1-A; 2-B; 3-D; 4-C; 5-C; 6-C; 7-B; 8-B; 9-C; 10-C

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone focuses on Microsoft Access 2010 Beginner Level 9. I am Richard Rost, your instructor, and in this lesson we will be working on building a contact history system for your customers. A contact history is an essential part of database management for keeping track of every interaction you have with your clients, such as phone calls, office visits, and any other communication.

We begin by creating a straightforward contact table along with a form to enter your data. I will explain principles of data integrity and why they matter in ensuring your data remains accurate and reliable. Then we move on to building a contact query. I will show you how to design queries that pull up records between two dates and how to use explicit query parameters to give you even more control over what information your queries display.

A good portion of this lesson is devoted to building a contact report. You will learn the different sections of a report, like the report header and page header. We will discuss properties such as Can Grow and Can Shrink, which help make your reports flexible to different amounts of data. Along the way, you will also pick up a variety of useful tips and tricks, including string concatenation, page numbering, and other helpful features.

This course builds on material covered in Microsoft Access 2010 Beginner Level 8. If you have not already completed Level 8 and the earlier sessions, I recommend doing so. In Level 8, we tackled topics like combo boxes, list boxes, and setting the tab order for controls. These concepts will help you follow along more easily with this lesson.

While the class is recorded in Microsoft Access 2010, almost everything shown here works in Access 2007 as well. If you are using Access 2003 or an earlier version, you might notice some differences in the menus, but the core concepts still apply.

I always encourage students to build the database I create in the class along with me. However, if you prefer, you can download the completed sample database from accesslearningzone.com/databases and use that as a reference.

My courses are arranged into four main groups: Beginner, Expert, Advanced, and Developer. The beginner series is for those who are new to Access, introducing the basics you need to start being productive right away. The expert series is for users who are comfortable with Access and want to dive deeper into its features and power-user techniques. After you finish the expert courses, the advanced lessons introduce you to more sophisticated tools like macros and automation, which add huge amounts of functionality and professionalism to your databases. Eventually, the developer-level courses teach you how to program in Visual Basic for Access, so you can unlock the most advanced features and create truly customized solutions.

Every learning path is split into numbered levels that progressively build on the previous materials. Once you work through all of the beginner classes, you should move to the expert courses, then on to advanced, and finally developer-level topics.

In addition to the standard classes, I also offer seminars focused on specific topics. Some of these include building web-based databases, designing forms and reports that look like calendars, adding security to your databases, managing images and attachments, writing work orders, tracking accounts payable, learning SQL, and much more. You can find details on all of these seminars at my website.

If you have any questions about the material in this lesson, I encourage you to post them in the student forums. If you are watching through my online player or theater, you will find the forum next to the video, provided you have an internet connection. There, you can read questions and answers from other students and leave your own questions or comments. You can also visit the forums any time at accesslearningzone.com/forums.

For best results, I suggest you first watch each lesson all the way through without trying to follow along in Access. Then, replay the video and do the steps yourself, reproducing the database I make in class. Master the sample database first, rather than trying to immediately apply these concepts to your own projects. If you get stuck or need clarification, rewatch the lesson or reach out to me in the forums. Most importantly, approach Access with an open mind. It may seem overwhelming at first, but with a bit of practice it becomes straightforward to use.

Now, let's take a closer look at what we will cover in today's class.

First, we will construct a basic contact manager. In the first lesson, we will create the contact table and form. In lesson two, we set up a query to display contacts from a specific date, and I will show you how to use explicit query parameters.

Lesson three will show you how to design a contact report from scratch, while lesson four digs into properties like Can Grow, Can Shrink, and teaches you about string concatenation.

Finally, lesson five will wrap up the contact report and introduce a range of miscellaneous topics, such as line thickness for your reports, page numbering, setting background colors for sections, working with report headers and footers, and more.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Building a contact history for customers
Creating a contact table
Designing a contact form
Understanding data integrity
Building a contact query
Filtering records between two dates
Setting up explicit query parameters
Creating a contact report from scratch
Understanding report header and page header
Using Can Grow and Can Shrink properties
String concatenation in reports
Setting up page numbering
Adjusting line thickness in reports
Changing section background colors
Working with report headers and footers

Article

If you are looking to get started with Microsoft Access 2010 and want to learn how to build a contact history system for your customers, this step-by-step tutorial will guide you through the process. Tracking every interaction with your customers, such as phone calls, office visits, or any form of communication, is an essential part of maintaining a strong relationship and building a history for each contact. This guide will walk you through creating a basic contact manager database from scratch, focusing on the practical steps you need to know as a beginner.

To start, you will need to create a simple table to store contact information. Open Microsoft Access and create a new database. Name it something like ContactManager. Next, create a new table called ContactHistory. The table should include fields such as ContactID (set as an AutoNumber and make it the primary key), CustomerID (to relate this contact to your customer table), ContactDate (Date/Time field), ContactType (to store the type of contact, like phone or visit), and Notes (for details about the interaction).

Once your table is set up, you will need a form to make it easy to enter new contact records. Create a new form based on the ContactHistory table. You can use the Form Wizard or build the form manually in Design View. Arrange the fields so users can easily input the date, type, and notes for each contact. If you have a separate Customers table, include a combo box on the form to allow users to select the customer for each contact. To do this, use the combo box wizard and let it show values from your Customers table, binding the combo box to the CustomerID field in your ContactHistory table.

Data integrity is important in your database, so make sure you use relationships to link your tables properly. Open the Relationships window and create a relationship between your Customers table (CustomerID) and your ContactHistory table (CustomerID), enforcing referential integrity. This ensures that you cannot accidentally add a contact without a valid customer associated with it.

With your forms and tables ready, you can now create queries to analyze your contact history. For example, you might want to see all contacts that occurred between two dates. To do this, create a new query in Design View based on the ContactHistory and Customers tables. Add the fields you want to see, such as Customer Name, ContactDate, ContactType, and Notes. In the criteria row under the ContactDate field, use this expression:

Between [Enter Start Date] And [Enter End Date]

When you run the query, Access will prompt you to enter the start and end dates, and it will display only the contacts within that range. These prompts are called explicit query parameters and are a useful way to create flexible reports.

Creating useful reports is an important part of any database. To build a report based on your query, use the Report Wizard or create one manually in Design View. Place your fields in the detail section, and add grouping or sorting as needed. You will notice that reports in Access have several sections, such as the Report Header (which prints once at the top of the entire report), the Page Header (which prints at the top of each page), Detail (where each contact record is printed), and Footers.

Sometimes the information in your Notes field may vary in length. To handle this, select the text box for Notes in your report and set its Can Grow and Can Shrink properties to Yes. This allows the text box to automatically adjust its height to fit the content, avoiding wasted space or cut-off text.

Access reports also offer other handy features. For instance, you can concatenate strings for more readable output. Suppose you want to display a full contact summary like this: "On 1/2/2023, you called the customer. Notes: Followed up about the order." You would use an expression in a text box on your report like:

="On " & [ContactDate] & ", you " & [ContactType] & " the customer. Notes: " & [Notes]

This approach lets you customize how your contact history appears to readers.

Page numbering is another common reporting need. To display page numbers, add a text box to your report footer or header and set its Control Source property to:

="Page " & [Page] & " of " & [Pages]

You can further enhance your reports by customizing line thickness, background colors in each section, and displaying headers or titles in the Report Header section.

After building your contact report, take time to experiment with various formatting options. Try adjusting line thicknesses to make your report more visually appealing or add section background colors for emphasis. If you want each report to start with a title or logo, use the Report Header section. For footers, include totals, dates, or other summary information if it is relevant.

As you work through building this basic contact manager, focus on mastering the core steps and concepts. Create and relate tables properly, build data entry forms with combo boxes for selections, set up queries with parameters for flexible searching, and develop clear, readable reports using formatting properties and expressions.

Once you are comfortable with these essentials, you can explore more advanced Access features over time, such as macros, automation, or even programming in Visual Basic for Applications (VBA) to enhance what your database can do. For instance, with VBA, you can automate sending emails to customers or updating fields based on user actions. Here is a simple example of marking a contact as followed up when a button is clicked on your form:

Private Sub btnMarkFollowup_Click()
Me.Followup = True
Me.FollowupDate = Date
Me.Dirty = False
End Sub

This code assumes you have Followup (Yes/No) and FollowupDate (Date/Time) fields in your table and adds a command button to run the marking action.

Remember to save your work frequently, test each new feature or modification, and if you run into questions, there are many online forums and communities dedicated to Access users. Practice by building the database described here, and do not hesitate to adjust it for your own needs as you learn.

Starting with these concepts, you will build a solid foundation in Access and be well on your way to managing and reporting your customer contact history effectively. Keep learning, stay curious, and you will find Access becomes a powerful tool in your daily workflow.