A simpler way to generate a report

[Pat Dools]

Hello,

I am developing a research 'form tracking' database. What the end user
wants to track is 3 things for EACH form:

1) Whether or not the research form has been received (Yes or No)
2) Whether data entry person #1 has entered it into the proper database
3) Whether data entry person #2 has entered it into the proper database

The problem is that the user literally wants to see these 3 pieces of info.
under each form and have each form name on the report from left to right and
there are almost 40 forms. The user wants to be able to quickly scan what
has been received and what has been entered.

For each form: My initial thinking is to say if 'Received' field is
checked, then show 'x', else show an 'o' for #1 above.

Then, for #'s 2 & 3 above if 'Data Entry Initials' are filled in (i.e., Not
Null) then show a '1', else, show a '0'.

So, ultimately, the report resembles a spreadsheet that shows a form name in
the column header, and these 3 pieces of info. directly underneath for each
form. The user has actually been doing this in a spreadsheet, but the
process has been totally manual, and now wants to use Access to automatically
generate this report.

But, let's say I have 40 forms, then I have 120 calculated fields in my
query that will supply the data for my report!! Is this simply beyond the
scope of Access reporting?

Is there any reasonable way around doing this massive amount of calculating
each time the report is refreshed? Does anyone know the max. number of
fields that can be used in a query?

Any help on simplifying this would be greatly appreciated!

Thanks.

 

[Allen Browne]

Yes, that sounds like a nightmare to build and maintain. For example, you'll
just get it finished, and they will find another form that needs to be
handled, so you're back at it again.

Instead, create a table that contains a *record* for each kind of form there
is to fill out.
You already have a table with a record for each issue(?) that needs to be
handled. Now you need a related table with fields like this:
IssueID relates to the primary key of your issues table.
FormID relates to the primary key of your forms table.
EntryDate Date/Time when this form was filled out for this
issue.
You will then create a subform on your Issues form.
It will be in continuous view, so the user can enter multiple rows.
It will contain a combo where the user selects which form was filled in.
The EntryDate can have its DefaultValue set to: =Date()

 

[Larry Linson]

You discuss a great deal about what you want to see in the report; you
discuss (almost?) not at all the table data that will be the basis of the
report.

If you had only a few records (one per each of your "forms" -- Forms in
Access has a specific meaning and I don't want to confuse the issue), then
you might do what you want in a generic manner using a CrossTab Query, which
you'd use as RecordSource for a Report.

But, unless the name/identification of each of your "forms" is very, very
short, fitting that information across a Report Page will be a problem...
Report Pages are limited to 22 inches in width. You are highly unlikely to
squeeze 40 of them on a single line, and line-wrap doesn't work the same in
Access Reports as in word processors.

Assuming that the user understands it will require multiple lines, you may
be able to do what you want with a narrow column, choosing either the
"Across then Down" (which would simulate word processor line-wrap) or "Down
then Across" format. Set the number of Columns in Page Setup. (It could also
be done with multiple pages, provided the total width does not exceed 22
inches, which I'd think it likely would.)

But, it will certainly depend on the way you have the data stored. But, you
will not "have 40 calculations" in the Query on which you'd base the Report,
you'd only have three... assuming you have one Record for each "form". It
sounds as if one of the fields of interest is a Yes/No field -- why not just
use the CheckBox control to show it, instead of calculating as you describe?
The calculation for the "initials" Fields is simple: Entry1: Not
IsNull([Initials1]) will yield True if initials have been entered, False if
they have not.

If you have anything other than a Record per Form in the Tables, as Allen
says, maintaining such a Report could be a nightmare.

Larry Linson
Microsoft Access MVP

 

[Pat Dools]

Hi Larry,

Thanks for your response. I do have a 'lookup table' in which each clinical
form has its own 'clinical form record'. I also have another 'lookup table'
which contains basic Patient ID info. in those records. I then have a
'status' form onto which the both the 'Patient ID' info. and the 'Clinical
Form' info. is inherited from a 'startup' form that is used when the user
opens the application. This 'status' form (and its underlying table) then
records whether the clinical form for that particular patient has been
received, and whether or not it has then been assigned to a data entry
person, and then if it has been assigned to a second data entry person for
double-entry. The basic question this report will then answer is: For a
given patient, has a given clinical form been received (a 'Yes/No' field),
and, if so, has it been entered and then double-entered?

How do I use the actual checkbox control on the report to show that a
clinical form has been received? If I can do that, then I won't have to
calculate something like: 'If 'Yes', then show 'x', else show 'o'. Second,
I will definitely check for Nulls in the 'Data Entry Initials' field to make
life easier for checking whether or not the clincal form has been assigned to
a data entry person.

In terms of setting up the underlying query for the report, would I just do
a Left Outer Join of the 'clinical form lookup table' to the 'status' table
so that I can get ALL clinical form records returned, and then match them up
with existing 'clinical form status' records to see whether they've been
received/entered?

Thanks again.
--
Pat Dools

You discuss a great deal about what you want to see in the report; you
discuss (almost?) not at all the table data that will be the basis of the
report.

If you had only a few records (one per each of your "forms" -- Forms in
Access has a specific meaning and I don't want to confuse the issue), then
you might do what you want in a generic manner using a CrossTab Query, which
you'd use as RecordSource for a Report.

But, unless the name/identification of each of your "forms" is very, very
short, fitting that information across a Report Page will be a problem...
Report Pages are limited to 22 inches in width. You are highly unlikely to
squeeze 40 of them on a single line, and line-wrap doesn't work the same in
Access Reports as in word processors.

Assuming that the user understands it will require multiple lines, you may
be able to do what you want with a narrow column, choosing either the
"Across then Down" (which would simulate word processor line-wrap) or "Down
then Across" format. Set the number of Columns in Page Setup. (It could also
be done with multiple pages, provided the total width does not exceed 22
inches, which I'd think it likely would.)

But, it will certainly depend on the way you have the data stored. But, you
will not "have 40 calculations" in the Query on which you'd base the Report,
you'd only have three... assuming you have one Record for each "form". It
sounds as if one of the fields of interest is a Yes/No field -- why not just
use the CheckBox control to show it, instead of calculating as you describe?
The calculation for the "initials" Fields is simple: Entry1: Not
IsNull([Initials1]) will yield True if initials have been entered, False if
they have not.

If you have anything other than a Record per Form in the Tables, as Allen
says, maintaining such a Report could be a nightmare.

Larry Linson
Microsoft Access MVP

Hello,

I am developing a research 'form tracking' database. What the end user
wants to track is 3 things for EACH form:

1) Whether or not the research form has been received (Yes or No)
2) Whether data entry person #1 has entered it into the proper database
3) Whether data entry person #2 has entered it into the proper database

The problem is that the user literally wants to see these 3 pieces of
info.
under each form and have each form name on the report from left to right
and
there are almost 40 forms. The user wants to be able to quickly scan what
has been received and what has been entered.

For each form: My initial thinking is to say if 'Received' field is
checked, then show 'x', else show an 'o' for #1 above.

Then, for #'s 2 & 3 above if 'Data Entry Initials' are filled in (i.e.,
Not
Null) then show a '1', else, show a '0'.

So, ultimately, the report resembles a spreadsheet that shows a form name
in
the column header, and these 3 pieces of info. directly underneath for
each
form. The user has actually been doing this in a spreadsheet, but the
process has been totally manual, and now wants to use Access to
automatically
generate this report.

But, let's say I have 40 forms, then I have 120 calculated fields in my
query that will supply the data for my report!! Is this simply beyond the
scope of Access reporting?

Is there any reasonable way around doing this massive amount of
calculating
each time the report is refreshed? Does anyone know the max. number of
fields that can be used in a query?

Any help on simplifying this would be greatly appreciated!

Thanks.

 

[Larry Linson]

How do I use the actual checkbox control
on the report to show that a clinical form
has been received? If I can do that, then I
won't have to calculate something like:
'If 'Yes', then show 'x', else show 'o'.

I suggested an expression to use to create a Calculated Control that could
be used as Control Source for a Check Box.

I will definitely check for Nulls in the 'Data
Entry Initials' field to make life easier for
checking whether or not the clincal form
has been assigned to a data entry person.

In terms of setting up the underlying query
for the report, would I just do a Left Outer
Join of the 'clinical form lookup table' to the
'status' table so that I can get ALL clinical
form records returned, and then match them
up with existing 'clinical form status' records
to see whether they've been
received/entered?

Yes, that would seem to be what you should do.

 

[Pat Dools]

Hi Larry,

Thanks for the pointers-- especially in regards to the Page Setup to divide
my report up into columns, so I could avoid having a separate, calculated
field for each clinical form. I write Crystal reports for a living, and have
bumped up against Access report writing limitations pretty quickly. Do you
consider Access a good report writing tool, or is it really better for the
most basic reporting? It just seemed that my reporting ideas were so much
easier to envision using Crystal, but i am fairly new to using the reporting
piece of Access.

Thanks again-- my report is MUCH further along due to your pointers!

 

[Larry Linson]

I write Crystal reports for a living, and have
bumped up against Access report writing
limitations pretty quickly. Do you consider
Access a good report writing tool, or is it
really better for the most basic reporting?
It just seemed that my reporting ideas were
so much easier to envision using Crystal, but
i am fairly new to using the reporting
piece of Access.

I've seen some impressive work done with Crystal by colleagues, in instances
where Access Reporting could not be used or would have been difficult to use
(e.g., generating reports in a web application). I haven't, however, seen
Crystal used to accomplish anything that I thought could not be accomplished
with Access Reporting -- which, of course, does not mean there aren't things
of that nature, or that Access can't do things that Crystal cannot.

Yes, I have used a good many Report Generators, dating back to the one in
COBOL, and I consider Access Reporting to be the best Reporting facility
that I have ever seen included in a development product. (Caveat: Crystal is
a third-party product that ships with a number of development products, but
is not "included in" the product itself.)

I suspect your familiarity with Crystal leads you to want to do what you
visualize in the same manner you would do it in Crystal, and that is a
prescription for frustration. Crystal Reports is, from its popularity, a
good tool; Access Reports are, from their popularity, a good tool -- but
they are not the _same_ tool, even if you can produce the same results.

A similar phenomenon often occurs when someone who's got long experience in
some other database development environment switches to Access -- they curse
Access as an infernal tool designed for their torture because they run into
difficulty doing some of the simplest operations (because their natural
tendency is to do that simple operation 'the old way' that is so familiar,
and Access just _isn't _ <whatever database they have used for so long>).
Sometimes they are abashed and ashamed when they find out how simple it is
to accomplish what they want "the Access way".

Larry Linson
Microsoft Access MVP