linked fields in tables

[sherry]

I have a fairly complex database containing many areas of
interest connected only by a single project number. I am
trying to figure out a way to type in the project number
for new entries only once and have that project number
link to other tables without having to retype it in. Can
this be done? I was looking at possibly creating a multi-
page form containing each area that would then send data
back to each table when filled in. I am learning Access
as I go, I'm wondering if I made the table(s) too
separated or if I should have created one large table
containing all of the main information and break it down
from there. Any help would be greatly appreciated!

Thank you in advance!

 

[PC Datasheet]

Assuming you have a primary table where project number is the primary key and
many secondary tables where project number is the foreign key in each table ----

Create a main form based on your primary table and include the project number.
Make the project number field on the main form the first field and enforce some
way that nothing can be entered in the other fields until project number is
entered. Add a tab control. Create as many pages on the tab control as you have
secondary tables. Create a subform based on each secondary table and include the
project number field. Lock the project number field from data entry. Put a
subform on each page of the tab control. For each subform, make sure the Link
Master and Link Child properties are Project Number. Now when you make an entry
in any field on any subform, the project number on the main form will be
automatically filled in in the project number field on the subform.

 

[Ed Warren]

There are as many ways to 'skin this cat' as there are people working on the
problem.
Solution 1:
build a form using your project table (FORM 1)
put a button for each subtable in the button build wizard, tell it
to get open a form based on a related table (FORM 2) and only get related
data projectID-->projectID. Make the related from a popup/modal form. Set
the default value for projectID on FORM2 =forms!form1!projectID.

Solution 2 (this is the one I normally use to solve this type problem.
build a list form (FORM 1) that has one visible column (project
description) and one hidden column (projectID)
build a set of forms that I can syncronize with the first using some
vba code.
now when I navigate the first form any of the related forms I
currently have open update to show the detailed information related to the
projectID in the first form and when I enter a new record in one of these
forms it automatically adds the projectID currently selected in FORM 1.
Solution 3:
Use one form with linked subforms. ( I find this great for one/two
subforms but quickly run out of visual work space if any more subforms are
required). :>


Best of luck, hope this helps

Ed Warren

 

[Rolls]

A basic thing that beginners frequently "don't get" is the difference
between one type of table used to store a) unique instances of Entities, and
another type of table used to store b) unique Relationships between the
entities. "Project" is an entity. Each unique project is an instance of
that entity. If you assign people to work on projects, you have another
entity, People, with unique instances; individuals, so you need a separate
table for them also.

We have

tblProject
tblPeople

Now we want to describe which people are assigned to which projects.
Because there is a many:many relationship -- many people can work on many
projects -- we need to resolve the ambiguity. We do this with the second
type of table:

tblProject-Person

This is the table with Foreign keys copied from the Primary keys in
tblProject and tblPeople. Action of the comboboxes in the subform does this
for us. We're selecting from the rowsource tables (Entities) and storing
the result (the relationship between them) into the third table. This table
may consist entirely of index values. The main form is the "Parent" side,
its subform contains the "Child" side of the relationship. On one screen we
see projects and in its subform we see all people assigned to each project.
When the project changes, so does the list of people assigned to the
project.

Addition of a new person or a new project adds one record to the relevant
Entity table. We may then need to add multiple relationships once we add
instances of entities. But we'll never duplicate records anywhere.

If there are additional Entities like "Equipment" that means we set up
tblEquipment. Then we can assign people to equipment or equipment to
probects, ad infinitum by adding relationship type tables to store oue links
between entities.

One form can contain one or more embedded subforms. If we need more than
one parent form (its likely that we will) we might want to use multiple
forms or the tabbed form with multiple pages to include everything needed to
maintain the Entities & Relationships in the database.

Did you "get it" ?

The key thing is to understand the E-R model. The only thing that gets
duplicated in storage is pairs of keys, a PK and an FK used to join tables.
Complexity can expand while storage requirements do not expand
porportionally, which makes the model efficient and fast in operation.