Second Post Any one out there Forms HElp

  • Thread starter Thread starter Dib
  • Start date Start date
D

Dib

Hi,

How can I do this

I need to create a form with 8 rows

ex. Uric
2 - B.U.N
3 - Sodium
etc.
I need to give the user columns to enter dates when testing has been done,
ex. Uric date1, date2 , date3 up to date40 this is for each Row
How do I set the table to work this way?

is there a way to add a control (Text Box) to the Form based on the number
of date textbox
fileds? and how do I maintain the 8 rows to show up when form is open if the
form is a Datasheet view. or ?

This is more like a flow sheet. or you could say a spreedsheet, but I don't
want to use Excel spreadsheet

Thanks
Dib
 
(reposting, as my first reply hasn't appeared)

Dib said:
Hi,

How can I do this

I need to create a form with 8 rows

ex. Uric
2 - B.U.N
3 - Sodium
etc.
I need to give the user columns to enter dates when testing has been
done, ex. Uric date1, date2 , date3 up to date40 this is for each Row
How do I set the table to work this way?

is there a way to add a control (Text Box) to the Form based on the
number of date textbox
fileds? and how do I maintain the 8 rows to show up when form is open
if the form is a Datasheet view. or ?

This is more like a flow sheet. or you could say a spreedsheet, but I
don't want to use Excel spreadsheet
Click to expand...

Dib -

Probably the reason you're not getting a quick answer to this question
is that it strikes most experienced people as a bad design. I don't
claim to speak for everyone but myself, but any time you have a long
list of fields differ only in a numeric suffix on the name, like
"date1", "date2", "date3", etc., it's a pretty good sign that your
database design is faulty. What you've described is both inefficient in
its use of storage, and cumbersome in its ability to be manipulated
using normal queries. Don't get me wrong. I'm not saying that you may
not want to *present* the data in this spreadsheet-like crosstab --
that's a matter of user-interface design (though Access doesn't make a
very good spreadhseet). I'm just saying that the data shouldn't be
stored that way if you want to be able to work with it easily using
queries.

A more normal and workable data design would have a table with 8 records
representing your 8 "rows" (chemicals? tests?), and another, related
table with multiple records for each "row", one for each test date. In
form design, it would be a lot easier to have 8 subforms to represent
the test dates for each of the "rows". How fixed and unchanging are
these 8 "rows" of yours?

Maybe if you explained the bigger picture, a better and more detailed
design would present itself.
 
Thanks for your reply,

What I am trying to do is create tables and a form or Forms for chemicals
testings, one set of testing has 8 different substance which is 8 Rows.
Every time a test has been done I need to be able to enter a date on that
row, if more than one test is made I need to be enter the dates for all.

I have not done anything yet I am looking for helpful advices from any one
who has done something similar. I have to do this for 5 different set of
chemicals, each set contains 8 rows.

This data need to be kept and available for 10 years. and if the user select
to view all the testing dates that has been done, I need to be able to show
all by set (meaning 8 Rows at a time will all date fields.) I have a copy of
the Flow Sheet design. but as requested the user need unlimited date fields
for each Row

any advice will be very help full, on how and where to start.


Thanks
Dib
 
Dib said:
Thanks for your reply,

What I am trying to do is create tables and a form or Forms for
chemicals testings, one set of testing has 8 different substance
which is 8 Rows. Every time a test has been done I need to be able to
enter a date on that row, if more than one test is made I need to be
enter the dates for all.

I have not done anything yet I am looking for helpful advices from
any one who has done something similar. I have to do this for 5
different set of chemicals, each set contains 8 rows.

This data need to be kept and available for 10 years. and if the user
select to view all the testing dates that has been done, I need to be
able to show all by set (meaning 8 Rows at a time will all date
fields.) I have a copy of the Flow Sheet design. but as requested the
user need unlimited date fields for each Row

any advice will be very help full, on how and where to start.
Click to expand...

Okay, here goes. It seems to me you've described three levels of data:
(1) test sets, (2) substances tested -- 8 per set -- and (3) test
dates -- multiple per substance per test set. I don't know whether that
bottom level will have other results recorded besides the dates
themselves; that's certainly a possibility. Based on this structure, I
can see the following table structure:

Table TestSets
---------------
Field TestSetID : primary key
(other fields to describe the test set)

Table TestSubstances
----------------------
Field SubstanceID : primary key
Field SubstanceName : text
(possibly other fields to describe the substance)
(this table has, initially, 8 records)

Table TestsPerformed
-----------------------
Field TestSetID : (foreign key to TestSets)
Field SubstanceID : (foreign key to TestSubstances)
Field TestDate : date
(I'd suggest a compound primary key composed
of all three of the above fields)
(possibly other fields recording test results)

These tables would have a 1 to many to 1 relationship:

TestSets:1<-->M:TestsPerformed:M <--> 1:TestSubstances

The *simplest* and most flexible, test-set oriented way to represent
these relationships is with a main form in single-form view based on
TestSets, and a subform in continuous-forms view based on
TestsPerformed, using a combo box to select and display the
TestSubstance on each subform record. But that may not be the most
effective user interface for people who want to clearly see the test
results clearly segregated by substance. You can build a clearer, but
less flexible form by using 8 separate subforms, one for each substance.
These could be arranged vertically on the main form (as "rows"),
horizontally (as "columns"), or on separate pages of a tab control. The
subforms would be set up with their Link Master Fields set as invisible,
calculated text boxes on the main form with controlsources like

=1
=2
=3

etc. up to 8, where the values 1 through 8 are the SubstanceIDs from
table TestSubstances.

You should be aware that all of these multiple-subform designs, are less
flexible than the single-subform approach, because you're building a
fixed number of test substances into the user interface. However, if
these are really not subject to change very much, and if you'll be
available to make design changes if another substance should be added to
the list, the increased clarity of the UI design is probably worth it.
 
Thank you again for the helpful information. I will give it a try and let
the group know of the result

Thanks
Dib
 
Back
Top