Data Entry Into Multiple Tables Using One Form

[Linda]

Hi:
Is it possible to have a single form populate more than one table (with
common fields) in a database?
If so, can anyone tell me how to do it?

I imported a table into the backend database ( which houses the
actual data) , and imported a corresponding form, that populates the
imported table, into the frontend database (which houses only
queries, forms and reports and links to the backend for the actual
data). I linked the imported table with the two other tables in the
original database using a common field.
I would like to have all the common fields populate automatically
in the different tables when I input the data into either the
original or imported input form. Is this possible? Is this clear
as mud????

Thank you in advance for any help you can offer.
Linda

 

[Jeff Boyce]

Linda

If I understand your situation, you are trying to save the same facts in
more than one table. If your database is well-normalized, this should not
be necessary. If you were working in a spreadsheet, you might find
situations requiring this approach. But Access is a relational database,
and to use its strengths, you need a design that minimizes/eliminates
redundant data entry.

Perhaps if you provided an example of some of the types of data you are
trying to store in multiple tables, the 'group could offer alternative
approaches...

Good luck

Jeff Boyce
<Access MVP>

 

[Arvin Meyer [MVP]]

If you can build an updateable query with both tables and base your form on
that query, you can do what you wish.

An even more common method is to use subforms. With subforms connected by a
related field (key) you can add a main record like a customer record and
have a subform for the customer's multiple locations, and another subform
nested in the first subform for the the multiple contact numbers (phone,
fax, email, etc.) at each location. You can also have a subform for the
customer's orders. See the Northwind sample database that came with Access
for some examples.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Linda]

Thank you for your response.
I think your first suggestion might do the trick.
I will give it a try when I return to work.
Gratefully,
Linda

 

[Linda]

Thank you very much for responding.

The database "evolved" over a number of years under different managers.
It is not well laid out at all and has alot of redundancy (identical
fields with different names etc.). I am cautious about changing too
much due to the extensive number of already established queries and
forms actively being used, and also because of my limited understanding
of Access (I have only an introductory Access course under my belt.) So
I am moving slowly and with care.

Hopefully creating a new form based on an updateable query from the two
tables will solve the problem.

Thank you again for taking the time to respond. It is appreciated.

Linda

 

[Guest]

Hi--
I found this thread while trying to solve a similar problem. I maintain
demographic and a multitude of other information in a relational database
that is, like Linda's, poorly designed and has some problems I have been
trying to find and solve.

One such problem is that to enter a new client (the DB uses the SS# to link
the main table with 10 other tables (one-to-one) that use the SS# as the
primary key), I must enter the SS# in all of the linked tables before all of
the reports will show that client's record. This is tedious, so I designed a
form with the main table (including the client name, address, etc.) SS# and
added subforms with the SS# field from each of the related tables. I thought
I was a genius--I thought that the main table's primary key entry was
populating all of the related child fields, because they all were filled out
in the form.

BUT--when I checked the tables, the SS# that I had added for a new client
did not exist. There was a sort of "ghost" relational entry so the database
wouldn't freak out because there was no matching record in the "child" tables.

I'm thinking that this has something to do with cascade updates in the
relationships or that I should make the primary key in the "child" tables an
autonumber and replace (or reconfigure) the current primary key field (an
identical SS# field to the main table's primary key) into a foreign key.
Wouldn't this make these tables "true child tables" to the main table parent?
could I then cascade updates?

Or do I copy the database to test if I can just go in and enforce
referential integrity and cascade updates (not deletions) in all of these
one-to-one relationships, not worrying if they are tied to the child tables
with a foreign key. (I think the SS# field in each table serves as a virtual
foreign key, right?)

Feel free to answer as many or as few questions as you wish. I've had
little luck finding something useful to help me set up a foreign key (if that
would even solve my problem).