connecting fields and subforms

[aks]

Hello, I'm trying to find out what's the best way to approach developing this
database.

I'm trying to develop a database that has some data in it already that
includes a description for selection and in addition would have additional
data entered in it by the entry person. The entry person would be selecting
choices and then in some instances select a choice and then also enter data
in another field that goes along with that line item. There are at least 15
groups of data with 15 to 30 line items to choose from per group and the user
could be selecting data from all of the groups or just one group with only
selecting one or several line items from each group or just one.

For instance the structure is as follows;
The form starts with an ID Number and Name.
Then, the Group fields have a yes/no field and a group description field.

The line item fields include a yes/no field, description field that would
have data in it already, text field that would be filled in by the user.

The default value for the yes/no field would be no. The user would only
select yes if they were going to use the description that's already listed in
the field, and if need be enter any data in the text field, if that line item
has the option of any entry. Not all line item will have an entry text field.

Now to explain the entry process.
The entry person would first enter an ID number and name.
Then they would select a group and then select one or several line items in
that group and if so also select other groups and line items in those groups.
The last part of entry is a comment field for the entry person to add any
comments.

The report would be the ID and name followed by the group description(s)
that were selected and the line items that were selected. For instance;

ID - NAME
GROUP1 DESCRIPTION
LINE ITEMS DESCRIPTION and TEXT ENTRY
GROUP2 DESCRIPTION
LINE ITEMS DESCRIPTION and TEXT ENTRY
GROUP3 DESCRIPTION
LINE ITEMS DESCRIPTION and TEXT ENTRY
COMMENTS


What approach I so far looked at is to create a form using two tables. The
main form has the key and ID information of the main record that references
one table. Then I would like to have several subforms that are actually
referencing the the other table. These subforms would be representing the
different groups and also would be on the main form and seperated in a tab
view. So the user can select which subform or multiple subforms to enter data
for that record. Also on those subforms the fields need to be connected by
line item. Meaning that the first field is a radio button with a yes/no value
and the next field that's somehow connected to the radio button has a
description in it and then sometimes there would be a third field on that
line that would be a text field that the user would enter data in it. The
radio button is for a filter that would be used in a report to only print
those items that where selected and omit all other items that were not
selected.

If I'm on the right track how would I actually connect the line items and
how do I make the subforms unique and finally how do approach this in the
report.

If I'm not on the right track what would be the best way to approach this?

Sincerely,
Andrew

 

[Larry Daugherty]

We don't know what you're trying to do so we can't tell if you're
using the right hammer.

Your best bet toward getting meaningful solutions is to back up and
post again with an explanation of the real world problem you're trying
to solve. In addition, please give your table structures as they now
exist listing the field names and datatypes and purposes

I have the impression that you're using an awkward mechanism to make
some of your choices; If there are several or many "descriptions" that
are repetitive then they belong in tables of their own. Those tables
are referred to as "lookup tables", don't confuse them with "Lookup
Fiends in tables" which are to be avoided always. Each lookup table
would hold a list of values of a single attribute of the records in a
more "main" table. Typically you'd have a combobox based on a query
on the lookup table on the form where you need the data. Simply
finding the information you need and selecting it will get the job
done quickly and relatively error-free.

Another area of concern: You mention an ID on a form. If that ID is
an autonumber ID in the underlying table then I recommend that you
get it off the form. If you need to track and present some numeric
identifier then I recommend that you generate it yourself.
Autonumbers obey their own rules and many a developer has spent a lot
of time trying to make them behave differently.

HTH