Table Relationships

[S4Aero]

I'm new to Access, but having fun with a project. I have a database that has
several tables with related data. Each Table has 1 or more common fields. I
want to be able to enter the data into one form (for the ContactInfo Table)
and have it propogate to the proper fields in the remaining tables. I know
it's easy for most of you, but not me - yet... Please help a novice. TIA

 

[Jeanette Cunningham]

Hi S4Aero,
Glad to hear you are having some fun with learning Access. From your
description I get the impression that your tables will need some tweaking to
set up the relationships in a more appropriate way.

Here is Crystal's answer to another poster with a similar problem.
-->
It would be best, however, before you try to set up forms to make sure your
data
is structured correctly. Do you have your relationships set up? It is
important to get your foundation set before you build your walls

For better understanding of the basics of Access, read this:

Access Basics by Crystal (Access MVP site)
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access

then, when you are ready to do forms -- look at the main form/subform
section of Access Basics for some guidelines <smile>

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John W. Vinson]

I'm new to Access, but having fun with a project. I have a database that has
several tables with related data. Each Table has 1 or more common fields. I
want to be able to enter the data into one form (for the ContactInfo Table)
and have it propogate to the proper fields in the remaining tables. I know
it's easy for most of you, but not me - yet... Please help a novice. TIA

You're perhaps misunderstanding how relationships work.

The *ONLY* common field between related tables should be the Primary Key of
the "one" side table, and a field of the same datatype - Long Integer if the
primary key is an Autonumber - in the "many" side table; this field is called
the "foreign key".

If you're trying to copy other fields from one table to another - or assuming
that a relationship will do so - you're on the wrong track. Each piece of
information should be in *ONLY ONE* table. The relationship doesn't put data
into any other table; it just prevents you from putting "orphan", unmatched
data into a table!

If you want to see ContactInfo data in conjunction with fields from another
table, you would create a Query joining the two tables; pull the contactinfo
fields from the contactinfo table, and other fields from the other table.

Perhaps you could describe your current table structure and what you expect to
have happen.

 

[Philip Herlihy]

I'm new to Access, but having fun with a project. I have a database that has
several tables with related data. Each Table has 1 or more common fields. I
want to be able to enter the data into one form (for the ContactInfo Table)
and have it propogate to the proper fields in the remaining tables. I know
it's easy for most of you, but not me - yet... Please help a novice. TIA

In a situation where you have two (or more) correctly related tables,
you can create a Query which draws related data from all the tables,
"joining" them. If you set up your tables correctly, and "tell" Access
about the relationships via the Relationships window, Access will make
creating the query very easy.

[With some exceptions] If you then base a form on that Query (easiest
way is to use the Form Builder Wizard) then you can normally enter new
information which will be distributed, when you save the record, to the
correct tables.

Try it out with a test database! The key is to get your table design
right first, and build on that. See a couple of favourite sites:
http://tinyurl.com/ms-table-design-tutorial
http://office.microsoft.com/en-us/training/CR101582831033.aspx

1) Tables, tables, tables...
2) Relationships window
3) Query to join tables
4) Form based on query.

Phil, London.

 

[S4Aero]

Thanks to all who have responded!

Some clarification for everyone who is helping me:

I have several tables - ContactInfo - EventInfo - FoodandBevCosting (and
several others. As I have setup the tables, 2 or 3 fields exist in each
table - ContactName, EventName, EventDate. These fields will be reported and
printed on several different reports. Of course, other fields will be
reported, too.

I thought that if I entered those fields into my ContactInfo form, that they
would propogate into the other tables to eliminate duplication of data
entry(and potential errors associated with that duplication). I then thought
that a relationship or a query would fill those fields in
(semi)automatically. To date, no such luck.

In reality, multiple fields from multiple tables will be used to produce a
variety of reports and documents for internal use, as well as for customer
use. I am still fairly early in the design process and have only been using
sample data to test various fuctionalities. Anything can still be done to
make this what it should be. I am open to any suggestions, and once again,
thanks to everyone for their time and help!

Ernie

 

[Bre-x]

I have learn MS Access on my own, one of the best ways to learn it,
was to check how other people do it.

Open the Northwind Database Sample Database (Help --> Sample Databases -->

Check how tables are relate to another

Bre-x

 

[John W. Vinson]

Thanks to all who have responded!

Some clarification for everyone who is helping me:

I have several tables - ContactInfo - EventInfo - FoodandBevCosting (and
several others. As I have setup the tables, 2 or 3 fields exist in each
table - ContactName, EventName, EventDate.

That was your mistake. The ContactName field should exist *ONLY* in the
ContactInfo table; the EventName and EventDate should exist *ONLY* in the
EventInfo table.

Think about it: if a record in the EventInfo table refers to a single event,
which of the 38 ContactName values for the 38 people involved in that event
should go into that record? I don't know if that precise issue pertains, but
it's an example of the way you'll tie yourself in knots with an improper
design.

These fields will be reported and
printed on several different reports. Of course, other fields will be
reported, too.

The Report will be based on a Query joining multiple tables.

I thought that if I entered those fields into my ContactInfo form, that they
would propogate into the other tables to eliminate duplication of data
entry(and potential errors associated with that duplication). I then thought
that a relationship or a query would fill those fields in
(semi)automatically. To date, no such luck.

As I say... the fields simply *should not exist at all*, other than in the
single table to which they pertain. A Contact has a LastName property. An
event, or a FoodBevCosting, *DOESN'T* have a LastName property, and doesn't
need one.

In reality, multiple fields from multiple tables will be used to produce a
variety of reports and documents for internal use, as well as for customer
use.

Of course. But it is not necessary to store all of that information
redundantly in multiple tables in order to include that information on your
report! The Report will be based on a multitable query, pulling the
ContactName from the ContactData table, the EventDate from the EventInfo
table, and so on.

I am still fairly early in the design process and have only been using
sample data to test various fuctionalities. Anything can still be done to
make this what it should be. I am open to any suggestions, and once again,
thanks to everyone for their time and help!

Check the tutorials I posted earlier. Crystal's video and tutorial page would
be a good start.