subforms

  • Thread starter Thread starter mb
  • Start date Start date
M

mb

Hi,

I would like to make a form that enters data into 8
tables. The data collection sheet fits all the data on
one sheet, so I would like to have 1 form, not eight.

Some fields would be common to all tables: date, site
number... and I would like this to be the main form. Can
I create 8 subforms which will farm out the
individualized data to each table? Is this the proper
approach?

Thanks in advance for your advice!
 
Some fields would be common to all tables: date, site
number...
Click to expand...

Ummm... not in a properly normalized database, they wouldn't! Storing
the same data redundantly in eight tables would probably be bad design
(unless you have a three or four field Primary Key consisting of these
fields). How are the tables related?

Note that designing your tables to fit the Form - especially a paper
Form - is a SURE way to get a non-normalized convoluted table
structure that will fit the form and NOTHING ELSE. Design your tables
*first* and fit the form to them.
and I would like this to be the main form. Can
I create 8 subforms which will farm out the
individualized data to each table? Is this the proper
approach?
Click to expand...

If the logical structure of the tables is in fact one main table
related one-to-many to eight other tables, yes; just for screen real
estate reasons, you may want to use a Tab Control with one or two
subforms on each tab page and the common fields on the mainform.
 
This is turning into a database design question, but it
is, as you say, important to design the table structure
first.

I designed this databse to have 9 tables. One table is
the permanent campsite information(information which
does not change). Another table contains the Social
components of each campsite. Another table contains the
Vegetation components of each campsite. Another table
contains the Cleanliness components of each campsite.

This data started out as a flat file with three hundred
records all in one excel table and I am trying to migrate
it into Access. For example, I had several fields
related to cleanliness components (litter etc...) and I
wanted to put them all in one field titled Type. Then I
would have a record for each type rather than many
fields. I thought a normalized databse would store
different types of info in different tables, rather than
in one flat file in Access. When the campsite is
inventoried, a date and site number is referenced.

Could you assess this design and let me know if you think
it is well-designed, or if it could use a restructure?

Thank you very much!
 
I designed this databse to have 9 tables. One table is
the permanent campsite information(information which
does not change). Another table contains the Social
components of each campsite. Another table contains the
Vegetation components of each campsite. Another table
contains the Cleanliness components of each campsite.

This data started out as a flat file with three hundred
records all in one excel table and I am trying to migrate
it into Access. For example, I had several fields
related to cleanliness components (litter etc...) and I
wanted to put them all in one field titled Type. Then I
would have a record for each type rather than many
fields. I thought a normalized databse would store
different types of info in different tables, rather than
in one flat file in Access. When the campsite is
inventoried, a date and site number is referenced.

Could you assess this design and let me know if you think
it is well-designed, or if it could use a restructure?
Click to expand...

This sounds pretty good to me. If you still have your 300-column
spreadsheet and would like to move data from it into your normalized
tables, there is a "Normalizing Union" query you can use.

I think you're very much on the right track, and that a Form with
eight Subforms is a perfectly reasonable way to go.
 
Thank you very much for your response and for thinking
about the database design.

I read in a post on the same page about another way to
create my form:

Create a query that uses both tables, and join them on
the appropriate
fields. Then use the query as the recordsource for the
form.

--
Ken Snell
<MS ACCESS MVP>

Would this work for my form? It seems like it would have
fewer steps that creating a form with 8 subforms. I am
not that familiar with creating subforms, but I am very
familiar with creating forms and queries? Is there a
reason to create my form with one method or the other?

(Also, I will be using normalizing union queries in
Access to migrate the Excel data into the tables.)
 
I read in a post on the same page about another way to
create my form:

Create a query that uses both tables, and join them on
the appropriate
fields. Then use the query as the recordsource for the
form.
Click to expand...

Well... for two tables this can work, although with the usual
one-to-many relationships it can be tricky figuring out which record
in which table is getting updated. With nine tables a) the chance that
the query will be updatable is nil, and b) you'll have a horrendous
Cartesian join query in which every "many" side record of each table
will be paired with every "many" side record in EVERY OTHER table.

Subforms are MUCH easier, and if you use the Toolbox on the Form
design window, you can create new subforms very easily.
 
Back
Top