How to design reporting?

[DerekD]

I am developing a database (using Access 2003; file is Access 2000 format)
that is used to schedule and track completion of compliance courses for our
employees. I have completed forms for scheduling, editing scheduled courses,
and marking courses completed. I want to be able to report unscheduled
courses, scheduled but not completed courses, and completed courses. I would
also like to have the results be able to be sorted by 1 of 5 criteria:
1. Manager
2. Location
3. Department
4. Course Title
5. Hire Date
In addition, would it be possible to allow the user to specify search
criteria to limit results? I am a novice user with some experience in VBA
and SQL. I am just unsure the best way to go about designing this.

 

[Jeff Boyce]

Derek

This, like the other public.access.xxxx newsgroups, is 'staffed' by
volunteers.

When you post this many copies of the same question/issue in such short
order, you are unlikely to encourage folks to lend a hand.

If you are under a major time constraint, perhaps you need to find someone
to hire, rather than rely on volunteer assistance?

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DerekD]

Jeff,

It was certainly not my intention to post multiple times, and it also was
not my intention to discourage any help. Each time I had submitted my
question, I was presented with an error message, so I retried my question.
Then, I after I logged in 15 mins later I was surprised to see they had all
been posted. for my impatience I apologize.

Time is not a big factor here, and I didn't mean to imply that. I am just
interested in some input as to an efficient way to develop the reporting
functionality I am after while still allowing the user some ability to limit
search results. Thank you.

 

[Marshall Barton]

I am developing a database (using Access 2003; file is Access 2000 format)
that is used to schedule and track completion of compliance courses for our
employees. I have completed forms for scheduling, editing scheduled courses,
and marking courses completed. I want to be able to report unscheduled
courses, scheduled but not completed courses, and completed courses. I would
also like to have the results be able to be sorted by 1 of 5 criteria:
1. Manager
2. Location
3. Department
4. Course Title
5. Hire Date
In addition, would it be possible to allow the user to specify search
criteria to limit results? I am a novice user with some experience in VBA
and SQL. I am just unsure the best way to go about designing this.

That's too vague/broad a question to get a specific
response. In general, I can say that all reports start by
creating a query that selects the needed fields in the
desired data records. The criteria for the query can be
applied in a couple of ways. It's usually best to use a
form for user specified values and the OpenReport method's
WhereCondition argument to filter the data records.

Report sorting is specified in the Sorting and Grouping
window (View menu) and can be modified (not added or
deleted) by using code in the report's Open event to set a
GroupLevel's ControlSource property.

 

[Jeff Boyce]

Derek

One approach I've used to add semi-custom reporting is:

* build the reports (I base mine on queries)
* make sure the reports (and their underlying queries) have the fields
needed for "customizing" (i.e., selection criteria)
* add the reports 'user-friendly names' and Access names to a new
tblReports table
* create a new form (frmOrderReports) that has a combobox that is filled
by the 'user-friendly names' (see previous step)
* add fields for the selection criteria you want your users to use re:
the reports
* add a command button that opens the report (Access Name) corresponding
to the 'user-friendly name' ... and that dynamically generates a WHERE
clause using the selection criteria fields/values.

NOTE: this is easier to outline than to do ... plan on spending some time
on this!

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DerekD]

That's too vague/broad a question to get a specific
response. In general, I can say that all reports start by
creating a query that selects the needed fields in the
desired data records. The criteria for the query can be
applied in a couple of ways. It's usually best to use a
form for user specified values and the OpenReport method's
WhereCondition argument to filter the data records.

Report sorting is specified in the Sorting and Grouping
window (View menu) and can be modified (not added or
deleted) by using code in the report's Open event to set a
GroupLevel's ControlSource property.

I will try to be a bit more specific. I have the 3 queries I need for each
report. A query that finds unscheduled courses, another than finds the
scheduled but not completed courses and a 3rd for completed courses. All 3
queries are pulling the same fields, 5 of which (listed in original post) I
want to allow both search parameters and sorting

Ideally, I think I would envision a form that has option buttons to first
select if they want to search for unscheduled, scheduled, or finished. Then,
I would think that combo boxes containing bound lists of search critieria for
each of the 5 fields of interest.

For example, if a user want to find all scheduled courses for employees
under manager John. He/she would select "scheduled" as the report type (the
Scheduled query I have built), choose to sort by Manager and select "John"
from the Manager combo box. If the user doesn't choose a manager, will the
report return all managers?

Is this the best way to achieve what I want to get out of this?

 

[Marshall Barton]

I will try to be a bit more specific. I have the 3 queries I need for each
report. A query that finds unscheduled courses, another than finds the
scheduled but not completed courses and a 3rd for completed courses. All 3
queries are pulling the same fields, 5 of which (listed in original post) I
want to allow both search parameters and sorting

Ideally, I think I would envision a form that has option buttons to first
select if they want to search for unscheduled, scheduled, or finished. Then,
I would think that combo boxes containing bound lists of search critieria for
each of the 5 fields of interest.

For example, if a user want to find all scheduled courses for employees
under manager John. He/she would select "scheduled" as the report type (the
Scheduled query I have built), choose to sort by Manager and select "John"
from the Manager combo box. If the user doesn't choose a manager, will the
report return all managers?

Is this the best way to achieve what I want to get out of this?

That sound very resonable. How you do it depends on further
details, but the form and the code to construct the
OpenReport method's Where condition argument will probably
require code similar to http://allenbrowne.com/ser-62.html

Since the course status can be another criteria, it sounds
like you only need one report and one query.

Remember that sorting the report will require code in the
report's Open event procedure. I suggest that you let that
go for now and get the form squared away first.

 

[DerekD]

That sound very resonable. How you do it depends on further
details, but the form and the code to construct the
OpenReport method's Where condition argument will probably
require code similar to http://allenbrowne.com/ser-62.html

Since the course status can be another criteria, it sounds
like you only need one report and one query.

Remember that sorting the report will require code in the
report's Open event procedure. I suggest that you let that
go for now and get the form squared away first.

Thanks for the additional input.

The "Course Status" is not a field. I have 3 tables currently, one that is
derived from a multiplicative query that lists a record for every exam and
employee (tblCourses). i.e. If we have 5 compliance tests this year, there
are 5 records for employee John. A second table (tblScheduled) has the
scheduled courses along with course title, schedule date, etc. The 3rd table
(tblCompleted) has the completed courses, completed date, title, etc.

2 of the queries are essentially unmatched queries to determine Unscheduled
courses and Scheduled but not completed courses. The 3rd query simply
queries the completed course table. This is the reason I had thought I would
need multiple reports and had already created the 3 queries to get those
results. Perhaps it is the construction of my tables and queries that is
making this more of a task than it should've been...

 

[DerekD]

Thanks for the input Jeff, I appreciate it.

In my case, I would then create 3 reports (3 queries - unscheduled,
scheduled, and completed) and build a form such as the one you described have
combo boxes for the 5 fields I wish the user to be able to search on? If
they don't select a parameter, will the query return all results for that
particular field?

 

[Jeff Boyce]

Derek

What you left out is the part where YOU tell Access how to dynamically build
the WHERE clause to add to the command button when you open the report. If
the user doesn't put anything in a field, you'd leave that part of the WHERE
clause out.

If the user didn't put any values in any of the fields, there'd be nothing
to put in a WHERE clause.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dale Fye]

Jeff,

I've noticed over the past couple of days when I am using the Microsoft web
based news reader, that when I respond, and check the "notify me of
responses" checkbox, I get an error message as well. It says something
about your submission not being accepted. It doesn't look like anyone has
mentioned this to the web developers, because it has been doing this for at
least 2 days.

I know, then don't use the Microsoft web based news reader.

;-)
Dale

 

[Marshall Barton]

The "Course Status" is not a field. I have 3 tables currently, one that is
derived from a multiplicative query that lists a record for every exam and
employee (tblCourses). i.e. If we have 5 compliance tests this year, there
are 5 records for employee John. A second table (tblScheduled) has the
scheduled courses along with course title, schedule date, etc. The 3rd table
(tblCompleted) has the completed courses, completed date, title, etc.

2 of the queries are essentially unmatched queries to determine Unscheduled
courses and Scheduled but not completed courses. The 3rd query simply
queries the completed course table. This is the reason I had thought I would
need multiple reports and had already created the 3 queries to get those
results. Perhaps it is the construction of my tables and queries that is
making this more of a task than it should've been...

It could be, but if you want help analyzing your table
structure, I suggest that you post the table, field and
relationship details to the Table Design newsgroup.

 

[DerekD]

Jeff,

Thanks again, all very good information. I am going to proceed with
creating 3 forms (each using one of the 3 course search queries I created as
a record source) and build in the Where clauses as part of the OpenReport
method. If I run into any trouble with the coding I will seek more help.
Thanks again.

 

[Jeff Boyce]

Derek

It may be that you can make this work with a single form, rather than "one
per report". Just a thought...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Dale

I don't use the web-based news reader at all, so I have no first hand
experience with that.

Have you tried contacting the folks who work for Microsoft? (I just
volunteer some time here...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DerekD]

I think that is a good thought, I may be able to have option buttons for
"unscheduled", "scheduled" and "completed" and use an if statement to
determine which report to run in the OpenReport method.

Also, I am running into one issue with the searching, one of my combo boxes
causes the report to not generate:

Private Function BuildWhereString() As String
Dim strWhere As String

On Error Resume Next

strWhere = ""

' ... build "TM" criterion expression
If Len(Me.TMselection.Value & "") > 0 Then _
strWhere = strWhere & "TM='" & Me.TMselection.Value & "' And "

' ... build "POD" criterion expression
If Len(Me.PODselection.Value & "") > 0 Then _
strWhere = strWhere & "POD='" & Me.PODselection.Value & "' And "

' ... build "Course Title" criterion expression
If Len(Me.CourseSelection.Value & "") > 0 Then _
strWhere = strWhere & "Course Title='" & Me.CourseSelection.Value & "'
And "

' ... build "Location" criterion expression
If Len(Me.LocationSelection.Value & "") > 0 Then _
strWhere = strWhere & "Location='" & Me.LocationSelection.Value & "' And "

' Strip off the trailing " And " text string
If Len(strWhere) > 0 Then strWhere = Left(strWhere, Len(strWhere) - _
Len(" And "))

When I select a course on the form (CourseSelection combo box), nothing
occurs on the click event for my "Search" command button. The other 3 combo
boxes work great in any combination. I can't seem to figure out the error.

 

[Jeff Boyce]

Derek

One approach might be to embed (temporarily) a Msgbox() command to see what
strWhere is equal to at each step of your code.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dale Fye]

No, I'm just a volunteer as well, and don't have know any "insiders" at MS.
I occassionally send a note to the Access Teams via their blog site, but this
is a web issue, not an Access team issue.

I prefer Outlook Express as my newsreader, but cannot use it at work.

Outside of that, I don't particularly care for any of the web based news
readers, so I've just stuck with the one MS has provided. But this error is
getting a little old. As you can see, a lot of people are sending their
messages twice or more because of this error message.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.