I’m using Access 2002. My database is in Access 2000 format.

I’m entering into a table some information about mental health,
substance abuse, workforce development, and mentoring services
delivered to youth who are on probation. I plan the format of the
service log table to look something like the following. (I plan to
also give each record an Autonumber field that will act as the primary
key.)

YouthID   Name       Service   Date         Hours
1001      Adam A.    MH        11/1/02      1.25
1002      Betty B.   SA        11/1/02       .50
1001      Adam A.    SA        11/3/02       .50
1003      Curt C.    MH        11/15/02     1.50
1002      Betty B.   SA        11/16/02     1.00
1008      Sue S.     MH        11/16/02     1.45
1002      Betty B.   SA        11/30/02      .50
1002      Bettie B.  WD        11/30/02     2.50

(My entering Betty and Bettie is intentional. In this Service Log, I
expect different spellings of the names because different people will
be entering data. I may get “Sue,” “Susan,” and “Seu” from these busy
people. That’s why I want to depend on the Youth ID number. I’ll have
a table tblYdpYouthRoster that will include one entry for each youth,
with the official Youth ID and spelling of the name.)

I’ll be producing a number of reports based on this information. One
item that is stumping me is, “How do I have Access count the number of
different individuals who received mental health services during a
specified period of time?” and the corresponding items for SA, for WD,
and for MR services. For example, based on the data above, I’d want
Access to tell me that three different people received MH services,
two different people received SA services, one person received WD
services, and no one received MR services during the period 11/1/02
through 11/30/02.

I assume that to accomplish this I need to do the following:

1. Set up a parameter query in which I can enter the beginning date of
the reporting period and the ending date of the reporting period.

2. Nest this query into four queries, one that selects records in
which the service is MH, one that selects the SA records, one that
selects WD records, and one that selects the MR records.

3. Set up four reports, one based on each query, that groups the
records by Youth ID.

So far, so good. In my sample runs, I can get a report that includes a
sum of the time spent for each youth during that reporting period for
that type of service.

4. Place a text box control on each of my reports and then enter, for
the Control Source, an expression or code that counts the number of
unique values of Youth ID in the query.

Here are my questions:
1. Is what I described a good way to get what I want?

2. If so, what expression or code do I use as the Control Source? When
I set the control to count the number of instances of the Youth ID
code, I get a total of the number of detail records, not a count of
the number of different Youth ID codes. This is true even though I’ve
set the report to print only the summary totals. When I set the
control to count the number of times the “Sum” label appears, I find
that each time I request the report I am prompted not just for the
starting and ending dates of the reporting period, but also for a
value for the Sum parameter.

3. If what I’ve described is not a good way to get what I want, what
is a good way to do it?

I’ve some more questions, too. If I follow my plan, I end up with four
separate reports. The different professionals overseeing and providing
services to the youth want to be able to enter a Youth ID number,
starting date for a period of time, and ending date for a period of
time and have Access provide a report of how many hours of each of the
different types of services that youth has received during that period
of time.

For instance, based on the data above, the Probation Officer could
enter 1002, 11/1/02, and 11/30/02 in response to parameter prompts and
be presented a screen that reports Betty B. received no MH services,
2.00 hours of SA, 2.50 hours of WD, and no MR services.

My plan is to do this by creating a “shell” report that includes
subreport controls with the Control Source for each being the tally
calculated on each of the four reports for that youth. In setting this
up with sample records, I’m encountering enough little wrinkles that
I’m losing some confidence that I’ll get everything as perfect as I
need it.

4. Is the shell report with four subreports a good approach? If not,
could you describe a better approach?

5. How do I get the right tally from each report for that Youth ID to
appear in the right subreport on the shell report?

6. How do I get the subreport to say something like “0.00 hours” or
“none” for instances in which there is no record in the record source
query of that type of service being provided to that youth during that
time period?

7. I’d like to set it up so that, although the staff member enters the
Youth ID number, not the youth’s name, in response to the parameter
prompt, the report includes the youth’s name, which is retrieved from
the youth roster table, tblYdpYouthRoster. I assume that I insert a
text box and use a Control Source to get the right name based on the
Youth ID number. What do I use as the expression?

Thank you.

One thing I wonder is, if you want the users to input Youth ID #s and
not names, how will they know which ID # corresponds to which youth? I
mean, pretend you're sitting there looking at the data entry form, and
it reads, "Youth ID," while a child stands there. How do they know
what ID# to enter?  Are you thinking that if they enter the name, it
automatically inserts the corresponding youth ID? What if they
missspell the name? What if no name exists (first-time child)?

Also, usually there are 2 separate fields for LastName and FirstName,
to allow queries by last name.

The summation reports you describe are much more efficient to do with
VBA code than with the built-in expression builder in Access. Are you
familiar with VBA at all?

I believe to have the professional database application which you are
striving for, you will need to go beyond the standard features of
Accesss and utilize the power of VBA. I tried setting up the same
structure myself, and I also came up with 4 separate queries. I can
combine these queries  into 1 report using VBA code, but not by using
the expression builder or simply electing Control Sources.

~omniscientbeing-ga

Thanks for your comment, omniscientbeing-ga. Yes, it is clear that I
must master VBA. I’m working my way through “Microsoft Access 2002
Visual Basic for Applications Step by Step” by Evan Callahan. Sitting
on my bookshelf to be tackled next is “Office XP Development with VBA”
by Peter G. Aitken.

I find it particularly helpful to see examples of code with comments
as to what the code is doing. Perhaps my Google Answer question should
be, “Can you provide me an example of VBA code that counts the number
of unique values in a field of an Access table?,” and use that example
to learn from and write what I need.

Regarding your comments about names and numbers: Your points are good
ones. The system I’ve designed, based on user preferences and
management needs, depends on all staff at each contact using the Youth
ID number that is assigned when the youth is referred to this special
multi-agency program. However, I will keep your ideas in mind.