Multiple records from single form?

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Is there any way a form can create several records at
once? I want to create a database for lab results and
would like to have different data input forms for the
various sampling regimes. If I make the analysis name a
field (and another for it's value), then I would have to
have a form with a single field. If I list each analysis
as a field (to allow me to put several on a form) then I
will have lots of empty space in the database. I have
tried making a separate table for each regime, but then
have trouble querying as it is a many to many
relationship between these tables.
 
You should NOT have fields named after the analysis names, or separate
tables for each testing regime. Your database, like any other, needs to be
based on a set of properly normalised related tables, with each table
containing information about a single entity. Once you've sorted out the
table structure you can then think about the user interface - and this will
work much better when the tables are right.

For a database of lab test results you will probably need separate tables
for:

- Sample types
SampleTypeID (PK)
SampleDescription
SamplePoint

- Tests
TestID (PK)
TestDescription

- Sample Type Tests
SampleTypeID (FK)
TestID (FK)

- Samples
SampleID (PK)
SampleTypeID (FK)
Date/ time sampled

SampleResults
SampleID (FK)
TestID (FK)
Result

The sample types table will list all the various sample types that you could
test, and the testing schedule for each is listed in "sample type tests".

Each sample received has a record in the samples table that indicates the
type of sample and any other information you need about the sample, such as
when it was taken, when recieved by the lab, who took the sample etc. The
results from the sample are in another table - SampleResults that is related
to the samples table by the sampleID field. This table may only need three
fields - SampleID, TestID and the result, where the SampleID and TestID form
the primary key.

Note that the above is just intended to give you the general idea of how the
tables should be set up. You need to properly analyse your own requirments
and design the tables to suit your needs. You will proably need more tables
that I have indicated. The important thing is to ensure that you have
properly normalised your design - and if you don't know what that means then
find out before going any further!

I have designed a database for exactly the same purpose as you using a
similar table structure to the one described above. For the data entry of
sample results I have a simple form/ subform arrangement. The main form is
based on the samples table, and the subform is based on a temporary results
table. When a new sample is created I have some vb code that creates the
temporary table with a record for each test required by the particular
sample type. This table only has two fields for test ID and result. The
testID field is populated with the appropriate value and the result field is
left blank. The user sees the test description and an empty result field on
the screen. Once the results have been filled in I have code to run an
append query that adds the entered results to the SampleResults table.
 
Yes I knew it wasn't really the right design to start
with. Will create the necessary tables similar to your
suggestions. Can you give me any more info on the
subform, VB code and the append query? That will be the
more difficult part as I am new to this. Since it will
not be technicians entering data it will be important
that the form is almost identical to the paper copy.

Thanks.
 
Paul,

Firstly, please analyse your own requirements and don't copy what I've
suggested blindly.

I can't really supply you with all the details of how my database works -
this is just too much. I suggest you set up your tables, and post back with
specific problems you have.

Andy
 
My requirements are quite simple as there are minimal
types of analyses done. I have made some tables to cover
the results and the types and the samples as you
suggested. I don't understand what you meant about the
schedule being in the Sample Type Tests table though. It
appears just to be to relate two other keys.
I don't see why the schedule itself would even be a part
of the database, unless you need that to create the
subform or something.
 
OK, Let me explain the way I was thinking about this with an example. The
database that I set up was also for a limited number of analyses that are
carried out in a small lab on a plant. There are several types of sample
that are taken and recorded on this database, such as:

- final product lots
- intermediate product lots
- process effluent
- environmental scrubbers

Each different type of sample is listed in a SampleType table.

There are several different analyses that are performed, such as:

- water content
- chloride content
- product purity

Each of these is stored in a Components table (this is what I called Tests
in the original message, but components is a better name as you can measure
several components in one analysis/ test - you could also have a TestMethods
table).

Each sample type can be tested for one or more components, and each
component can appear in one or more sample types - ie there is a many to
many relationship between sample types and components. This requires a third
table that is related to the SampleType table by the SampleTypeID, and to
the Component table by ComponentID. This table represents either the testing
schedule for each sample type, or the components that could be tested in
each sample type, or both. So, the product purity will be a test for the
final product, and chloride will be a test for a scrubber.

Its uses are:

- in my database, to create the records in the temporary table that is used
for data entry - this means that the user sees a list of the correct
components for the sample type being tested, and merely has to fill in the
results - there is no need to select the components first.
- it could be used to limit the components that the user can choose from to
just those that make sense for the product being tested (this would involve
using a query based on this table as the source for a combo box).
- you can add extra fields to this table showing the maximum and minimum
permissable values, and use these to validate the results entered.
- you can add a field to this table to control the order in which the
results records are displayed.
- you could add a yes/ no field to this table to indicate if a specific
result was compulsory or optional.

So, in my database the following process is used when a user enters a new
sample:

- The user selects a sample type from a drop down list.
- VBA code creates a record in the Samples table for the appropriate sample
type, and creates a temporary table containing one record for each test in
the SampleTypeComponents (testing schedule table). (I could have also
created these records directly in the SampleResults table, but I chose not
to. It would probably have made sense to create the Sample record in a
temporary table as well.)
- A results form is opened showing the sample record on the main form, and
the temporary table records on a subform.
- The user clicks a command button when all the results are entered, and
this runs an append query to add the temporary table to the SampleResults
table.
- There is also a "cancel" button on the form and this deletes the sample
record and closes the form.

A simpler, but less user friendly method, would be to just have the results
entry form as a main form/ subform arrangement with the subform based on the
SampleResults table. You could put a combo box on the subform that lists the
component name (but is bound to the component ID), and limit the list of
components presented to the ones specified for the sample type under test in
the testing schedule. If you don't have the testing schedule as part of the
database then the user will be presented with every component in your
components table - including ones that don't make sense for the sample they
are testing. The user will also need to know or have to look up the testing
schedule for the sample.

The fact that you only have a small number of types of analysis is not
really important to how you design the database. It's still nearly certain
that you will change the tests that you do over time, and your database
needs to be able to cope with this. If you were doing a large volume of
analyses on a huge number of sample types, then the basic database design
would still be the same as for a small number of samples, although you would
probably buy a proprietry LIMS in that case.
 
Yes that helps, I think I am understanding it now. Sample
Types for me might be better thought of as the Location
or Sampling Point since most of the measurements are done
in the field (cl residual, pH, conductivity). Although as
you say, in the future there may be more analyses added
that are done in the lab (and there currently are a
couple).

Only difference is whether a single "sample" is used for
multiple "components" or they are each taken in their own
bottle (as when preservatives are required), I think in
that case every bottle/container should have a separate
ID which would be the SampleTypeID. Soit could correspond
to a single compnent or multiple components, doesn't
matter because that is sorted out with the other tables.
Our lab has no procedures at all at the moment.

It will be important for me to have the input form like
the paper record. I am in the third world and it is
necessary to keep data entry error low. I think I can
figure out the subform part from my book, but have never
done VB so hope my IT guy can do that part for me. Thanks
again.
 
Back
Top