Building Microsoft Access Applications
This article is an excerpt from Building Microsoft Access Applications, from Microsoft Press (ISBN 0-7356-2039-3, copyright Microsoft Press 2005, all rights reserved). The author of the book is John Viescas.
John L. Viescas is the author of Microsoft Office Access 2003 Inside Out and the popular Running Microsoft Access books from Microsoft Press. He is president of Viescas Consulting, Inc., a respected provider of database application design and editorial consulting services. He has been recognized by Microsoft Product Support Services as a Most Valuable Professional (MVP) every year since 1993 for his contributions to the community of Access users.
No part of this chapter may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic;, electrostatic, mechanical, photocopying, recording, or otherwise—without; the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.
Chapter 6: Tracking Member Activities
- Logging Activities
- Reporting Activities
As you learned in Chapter 4, the Membership Tracking application is designed to not only save information about members, officers, meetings, and committees but also keep track of which members are active in the group—which; ones regularly attend meetings. To make this information useful, you need to create forms that make it easy to capture the data and reports that help you analyze activity over selected periods of time.
Logging Activities
In Chapter 3, you explored the frmMemberAttendance form that demonstrates an alternative way to look up values in a list by capturing characters as the user types in an unbound text box. It's this form that provides a simple way to capture member attendance as each member walks into a meeting. The form not only registers the member in the current meeting but also gives the person entering the data the opportunity to correct information about each member. As you'll learn in the next chapter, it also alerts the user when a member's dues are not current and provides a way to immediately accept a payment and extend the membership.
You can see how the form works by opening the Membership database (Membership.mdb). After the initial splash form closes (frmCopyright), open the frmMeetings form and move to the meeting record you want to work with. Click the Attendees button at the bottom of that form, select the Open the meeting sign-in form option in the Attendee Options window, and click Go. Figure 6-1 shows you the frmMemberAttendance form opened for the October 2004 general meeting and displaying the first attendee who has already signed in.
This form looks like it is primarily used to edit the member data. Except for the text box in the form header that lists the related meeting, it doesn't look like it has anything to do with members attending meetings at all. The secret is this form is actually based on a query that joins the tblMembers table with the tblMemberAttend table.
The SQL for the query is as follows:
SELECT tblMemberAttend.MeetingID, tblMemberAttend.MemberID, tblMemberAttend.DuesPaid,
tblMembers.Title, tblMembers.LastName, tblMembers.FirstName,
tblMembers.MiddleName, tblMembers.Suffix, tblMembers.Nickname,
tblMembers.DefaultAddress, tblMembers.WorkAddress,
tblMembers.WorkCity, tblMembers.WorkStateOrProvince, tblMembers.WorkPostalCode,
tblMembers.WorkCountry, tblMembers.WorkPhone, tblMembers.WorkExtension,
tblMembers.WorkFax, tblMembers.HomeAddress, tblMembers.HomeCity,
tblMembers.HomeStateOrProvince, tblMembers.HomePostalCode, tblMembers.HomeCountry,
tblMembers.HomePhone, tblMembers.MobilePhone, tblMembers.EmailName,
tblMembers.Birthdate, tblMembers.MembershipStatus,.DateJoined
FROM tblMembers
INNER JOIN tblMemberAttend
ON tblMembers.MemberID =
tblMemberAttend.MemberID
ORDER BY tblMembers.LastName, tblMembers.FirstName;
.gif)
Figure 6-1. The frmMemberAttendance form tracks members who attend a specific meeting (Click picture to view larger image)
For each row in tblMembers, many rows can exist in tblMemberAttend. The target table in any query for additions and deletions is the one on the many side of the relationship. But you don't see a control for either tblMemberAttend.MeetingID or tblMemberAttend.MemberID—so; how does this work? If you remember from Chapter 3, you can type a person's last name and first name in the Sign In, Please box, and code behind the form either finds the existing attendance record for the member name you entered or adds a new record.
The code to do this runs when you press Enter or Tab in the Sign In, Please box, indicating you accept the name that the form has found for you. It first attempts to find an existing member attendance record for the member ID for the current meeting that matches the name you entered. If it doesn't find that the member is already registered, it makes a copy of the form's recordset from the form's RecordsetClone property and inserts in a new row the member ID that it found and the meeting ID acquired from the companion frmMeetings form that is open. It then requeries the form so that the form's recordset now contains the new row and positions you to that row by finding the row in the form's recordset and using the Bookmark property to move to the new row. Below is the bit of code in the KeyDown event procedure for the txtSignIn text box control that inserts the new attendance row for you:
If (KeyCode = vbKeyTab) Or (KeyCode = vbKeyReturn) Then
' Assume they meant to update the control
' Set an error trap
On Error GoTo txtSignIn_Error :
UserAdded:
' If got a member ID
If Not IsNothing(lngMemberID) Then
' Get a copy of the recordset
Set rst = Me.RecordsetClone
' See if the row exists
rst.FindFirst "MemberID = " & lngMemberID
' If not found
If rst.NoMatch Then
' Save the memberID locally
lngNewMember = lngMemberID
' Insert a new row into the attendance table
rst.AddNew
' Set the Meeting ID
rst!MeetingID = varMeetingID
' Set the Member ID
rst!MemberID = lngNewMember
' Save it!
rst.Update
' Turn off painting
Application.Echo False
' Requery me
Me.Requery
' Get the recordset again
Set rst = Me.RecordsetClone
' Find the row we just added
rst.FindFirst "MemberID = " & lngNewMember
' Should not be nomatch
If rst.NoMatch Then
' Signal our own error
Err.Raise 3999, "Membership.Form_frmMemberAttendance", _
"Record just added was not found."
Else
' Move me to the added row
Me.Bookmark = rst.Bookmark
End If
' Turn on painting again
Application.Echo True
So, the user doesn't have to worry at all about selecting a member ID or making sure that the new record contains the meeting ID currently displayed in the frmMeetings form. Because Access lets you edit fields on the one side of a many-to-one relationship query, you can verify and correct all the member information displayed on the form.
Reporting Activities
After you make it easy to enter member attendance, you need to give the user the ability to analyze that activity. The chairman of the membership committee might be particularly interested in how many meetings each member has attended in the last six months or year and the date of the last meeting attended. If the data is sorted properly, it should be easy to identify which members should be contacted or marked inactive.
When you first look at the problem, you might consider building a totals query that joins the tblMembers, tblMemberAttend, and tblMeetings tables, finds out the last meeting date (using the Max aggregate function on MeetingDate) and the count of meetings attended. But if you do that, you'll get data only for members who have been to a meeting within the date range of interest. You really want to list all members and then find the statistics for any meetings attended. You might be tempted to define an outer join from tblMembers to tblMemberAttend so that you will see all members and any related rows from tblMemberAttend, as shown in Figure 6-2.
.gif)
Figure 6-2. Attempting to fetch data for all members and any related member attendance records (Click picture to view larger image)
There are two problems with this technique. First, you need to filter the meeting attendance data to look at meetings only for a specific time period. You cannot add a filter to rows on the "right" side of a "left" join and get the right answer because Access honors the join criteria first and then applies the filter. The join might fetch all members, but adding a filter on the MeetingDate field will exclude any members that did not attend any meeting in the selected time period.
Second, because you need a one-to-many and many-to-one join, the query engine will tell you that "The SQL statement could not be executed because it contains ambiguous outer joins." The error message also suggests that you create a query on the two tables that do not participate in the outer join, and then use that query in another query to perform the final outer join. Using separate queries is the correct way to solve both problems.
Tip Whenever you need an aggregate calculation (Min, Max, Avg, Sum, or Count) on only a few fields in a totals query that returns many grouped fields, you can obtain a more efficient result by breaking out the aggregate calculations into a separate query that is grouped on a primary key value. The query engine must build a temporary index to solve a Group By operation on many fields, so you're asking the engine to do far more work than necessary if you group on 10 or more fields, particularly if some of those fields are not indexed. When you perform the aggregate calculations in a separate query and then use that query in another query that fetches the remaining fields you need, your query will execute much faster, particularly in a client/server application.
In the sample application you can find the qryMemberActivity query that finds the last date any meeting was attended and counts the number of meetings. The query also includes parameters that point to controls on the fdlgDateReportParm form to filter the result to the time period of interest. Figure 6-3 shows you the query.
.gif)
Figure 6-3. A query to find the last meeting date for each member and count meetings (Click picture to view larger image)
Keep in mind that the MeetingDate field contains both a date and a time. To find all records within a date span using date parameter values, you must construct the criteria to include any meeting on the last date. The full criteria on the MeetingDate field is as follows:
>=[Forms]![fdlgReportDateParm]![txtFromDate] And
<([Forms]![fdlgReportDateParm]![txtToDate]+1)
Note that I added 1 to the "to" date parameter and used a less than comparison. This ensures that the result includes any record up to midnight of the end of the date range. If I were to compare less than or equal to the date, the query would not return any rows on the last date that also have a time component because that date/time value will be larger than just the date value.
The final query needed for the report is qryRptMemberActivity. This query fetches (and formats) the data needed from the tblMembers table and uses an outer join to the qryMemberActivity query to include any available statistics for each member. The SQL of the qryRptMemberActivity query is as follows:
PARAMETERS [Forms]![fdlgReportDateParm]![txtFromDate] DateTime,
[Forms]![fdlgReportDateParm]![txtToDate] DateTime;
SELECT ([Title]+" ") &
[FirstName] & " " & ([MiddleName]+"
") & [LastName] & (", "+[Suffix]) AS MemberName,
tblMembers.MembershipStatus, tblMembers.LastName, tblMembers.FirstName,
Replace(Mid([EmailName],InStr([EmailName],"MailTo:")+7),"#","") As Email,
tblMembers.DateJoined, qryMemberActivity.LastMeeting, qryMemberActivity.MeetingCount,
[Forms]![fdlgReportDateParm]![txtFromDate][From],
[Forms]![fdlgReportDateParm]![txtToDate] AS [To]
FROM tblMembers
LEFT JOIN qryMemberActivity
ON tblMembers.MemberID = qryMemberActivity.MemberID;
Tip Notice that I explicitly declared the parameters needed by the qryMemberActivity query. Although most parameter queries can run successfully without an explicit parameter declaration, including the declaration helps ensure that the supplied parameter value can be used successfully by the query. When you design a parameter query to prompt the user for values, the query won't continue until the user enters a value that matches the declared data type. Some types of parameter queries, such as a crosstab query, won't run at all without an explicit parameter declaration.
I assembled the parts of the MemberName field in the query, but I could have also done so using an expression in a report control. I included the separate LastName and FirstName fields so that the report could sort the names correctly for members that have the same last meeting date. I also stripped out the superfluous delimiters and the MailTo: prefix so that the e-mail address could be displayed in the report as plain text. Finally, I included the two parameter values as fields so that the report could display them.
To see the final result, you can start the application by opening the frmSplash form, clicking the Reports button on the main switchboard, and clicking the Member Activity button on the reports switchboard. (You can also open the frmReports form—the; reports switchboard form—directly; from the Database window.) You'll see a dialog box asking you for a date range for the Member Activity report. The sample database contains meeting records for September 2004 through January 2005, so you should select dates that include these dates. Click Print in the dialog box to see the report, as shown in Figure 6-4.
.gif)
Figure 6-4. The Member Activity report showing members who have missed the last several meetings (Click picture to view larger image)
The chairman of the membership committee might want to contact Marc Faeber, Bob Hohman, David Liu, and Peter Waxman to see if they still want to be active members. You can also see that some members have attended only one of the last several meetings. (Yes, I'm an honorary member of the Pacific Northwest Access Developers Group, but I live in Austin now, so don't often have a chance to attend a meeting!)