Fields and Fieldnames in reports

[Guest]

I am working to revise a database program from dBase III+ to Access
2000. This is my first experience with writing a database. The dBase
III+ was wriiten years ago using a Timex computer and later revised.
Most everything is done but I have a question about reports.

The database is for a laboratory information system. The report I
want to create needs to do the following:

1. The query and table that contains the lab results has multiple
field names that may or may not have data. For example,

Protein result, protein status, protein min, protein max may or may
not have data that applies to each and every sample. Sample A may
require the protein test while sample B does not. Sample B may
require fat result, fat status, fat min, fat max, calcium result,
calcium status, calcium min, calcium max. The report needs to list
the applicable fieldnames and results for each sample but should not
list fieldnames if no results are found in the query. How do I do
this? We were able to do it in dBase III+, so that the only field
names and results printed for each specific sample. The results
always printed in the same area of the report but moved up and down
the report based on the number of results per sample. Some samples
may have 3 results others may have 8 or more.

Also,

2. I want to create a out of specification report using the same
query. Each test result has a minimum and maximum value associated
with it. I want to produce a report that will list each assay result
and when it is out of specification. For example, A sample may be
assayed for protein, fat, and calcium. Each assay has a in the query
a max and min value that is specific for the assay. I need report
that will treat each assy indepedent of the others and will print each
assay that exceeds the max and min values for each sample.

Thanks.

 

[Nikos Yannacopoulos]

If I understand this correctly, your Access database "inherited" a major
weakness from dBase III+, namely a flat table where you should be using two
joined ones with a 1-to-many relationship.
Your design should involve a master table for Samples and a secondary table
Tests, something like:

[tblSamples]
SampleID
Description
.....
.....

[tblTests]
SampleID
TestTypeID
Result
Status
Min
Max
.....
.....

[tblTestTypes]
TestTypeID
TestType
TestMin
TestMax
.....
.....

SampleID is the primary key in table tblSamples, a foreign key in table
tblTests (make sure the two are joined in relationships and that referential
integrity is enforced). Likewise, TestTypeID is the PK in tblTestTypes, and
a foreign key in tblTests (again with referential integrity enforced).

So, for each sample there are as many records in table tblTests as the
actual tests run on it. This way, not only will it show only existing tests
in your report, but, what's more important, your database design is
optimized. You will get the desired report by using all three tables in
query (the third one just to get the test names), and then running the
report wizard on it, at which time you will choose to group on samples.

You can also use tblTestTypes as the rowsource of a listbox or combo box for
your data entry form, so as to choose the test type from that rather than
type it.

To your second question: to report on out-of-range tests you will need a
separate query, in which you will join all three tables (tblTestTypes joined
to tblTests on the test type field), and compare the test result to the
range by means of criterion like:
<= TestMin Or >= TestMax
on the test result field from tblTests, where TestMin and TestMax are the
normal range boundaries from tblTestTypes.
Again, use the report wizard on this query to get your report.

HTH,
Nikos

 

[Allen Browne]

A1.
The real solution to this is to use a relational structure, with a couple of
fields like this:
ResultName
ResultValue

However, if you need to continue with your existing structure, you can make
the text boxes shrink:
In report design view, right-click a text box and choose Properties.
On the Format tab, set the Can Shrink property to Yes.
If there is no data for the field, and nothing else on the same line or
overlapping the control vertically, it will take no space.

You probably want to hide the label as well when that happens? Right-click
the label, and choose Change To | Textbox. If the field was named "Protein
Result", set the Control Source of this text box (acting as a label) to:
=IIf([Protein Result] Is Null, Null, "Protein Result:")
This text box then becomes Null when the other one does, and so it Can
Shrink also.

A2:
This also depends on your data structure.
If you have a lookup table that lists the possible results:
ResultName
ResultMin
ResultMax
then you can join this to your actual results table, and set Criteria under
the actual result of:
< [ResultMin] Or > [ResultMax]