Common ID for several tables

[Apprentice]

I'm trying to get my head around this:

I have 3 tables. I have normalized these tables to the best of my
knowledge. These are the field names I am stuggling with:

Table 1 [Charter_ID] (Primary Key - No Duplicates) in main table.

Table 2 [Charter_ID] & [Activity_ID] (Dual Primary Keys- Duplicates OK)

Table 3 [Charter_ID] & [Activity_ID] & [Data_ID] (Three Primary Keys
-Duplicates OK)

Discussion: All three tables are tied together with the [Charter_ID] as the
common link. In building my Forms, each form pulls from their respective
table. When the user adds a new record, I want to run an append query to
copy the [Charter_ID] to the other tables, as the user moves to the next
form, the [Charter_ID] is already there.

The User starts a Charter (Table 1) and a [Charter_ID] is automatically
produced for them.

Next they add an Activity (Table 2), several Activity Records can be added,
but all must be under (1) Charter Record in Table(1) and all Activity Records
are tied to (Table 1) via [Charter_ID].

Next the User adds the Data (Table 3). Several Data Records can be added,
but all must be under (1) Activity Record in Table(2) and (1) Charter Record
in Table 1.

Now all records in Table (3) and table (2) are tied to (Table 1) via
[Charter_ID].

I have one - many relationships for [Charter_ID] in table (1) to (2 and 3).

Is the Append query the way to go? Or should I only use the [Charter_ID]
field once in Table (1)

When using a Tab Form, How can I ensure that the [Charter_ID] is replicated
from table to table and Form to Form.

Thanks

 

[pietlinden]

Okay, first things first. Instead of using "table1", "table2", etc.
tell us what real world entitities these tables are meant to
describe. Once you tell everyone that, sorting this mess out should
be easy. It sounds like a few tables with relationships between them,
and if you do that, the foreign keys (the field that "relates" the
child record back to the parent) will be managed for you.

So, could you describe your design in terms of noun-verb-noun? e.g.
"Customers have Invoices". "Invoices have LineItems"...?

 

[Apprentice]

Sorry, here are the table descriptions:

Charter!
[Charter_ID] - Primary Key No Dups
.... and several other fields "Leader, subject, Division, due date etc".

Activity!
[Activity_ID] - Primary Key Dups ok
[Charter_ID] - Primary Key Dups ok
.... and several other fields "Activitiy Responsibility, assignment date, due
date etc."

Data!
[Data_ID] - Primary Key Dups ok
[Activity_ID] - Primary Key Dups ok
[Charter_ID] - Primary Key Dups ok
.... and several other fields "Data type, collection method, presentation
type, etc."

 

[pietlinden]

Sorry, here are the table descriptions:

Charter!
[Charter_ID] - Primary Key No Dups
... and several other fields "Leader, subject, Division, due date etc".

Activity!
[Activity_ID] - Primary Key Dups ok
[Charter_ID] - Primary Key Dups ok
... and several other fields "Activitiy Responsibility, assignment date, due
date etc."

Data!
[Data_ID] - Primary Key Dups ok
[Activity_ID] - Primary Key Dups ok
[Charter_ID] - Primary Key Dups ok
... and several other fields "Data type, collection method, presentation
type, etc."

Okay, now we're getting somewhere. How are Charter, Activity and Data
related? Think in terms of [noun] [verb] [noun] for example...
Each Charter consists of one or more Activities.
Each Activity can belong to only one Charter.

Sounds like I'm being nitpicky, I'm sure, but if you can't articulate
what your database is about, you can't tell if it is structured
correctly. And if it's not, you can't get answers out of it... and
what's the use in that?

 

[Apprentice]

A Charter is the project. The project has many different aspects and
elements that differ from each other yet are still part of the Charter.

The Charter! Table has basic information about the Charter and is only (1)
record in the Charter! Table. It is identified by [Charter_ID].

Each Charter has several Activities that relate directly to the Charter.
Each Activitiy is different but is still part of only (1) Charter. Each
Activity is identified by the [Activity_ID] and relates directly to the
[Charter_ID].

Each Activity can have several Data requirements that relate directly to an
Activity and the Charter. Each Data requirement is different and is part of
an Activity which is part of the Charter. Each Data requirment is identified
by [Data_ID] that relates to [Activitiy_ID] and both [Data_ID] and
[Activity_ID] are part of the Charter and directly related to [Charter_ID].

A Charter (the project) consists of:

Charter basic information: [Charter_ID] = only (1) record
Several Activities: [Activity_ID] = many records
Several Data requirements: [Data_ID] = many records under only (1)
[Activity_ID]

On the Tabbed From there are (3) Different Forms, Charter, Activity and
Data. Once the user enters the new Charter basic information and continues
to the Activities, they should be able to retain the new Charter_ID and Add
several Activities. They should then be able to Add new Data requirements
(records) under a specific Activity that relates to a specific Charter.

Thanks
--
Your guidance is greatly appreciated!

Sorry, here are the table descriptions:

Charter!
[Charter_ID] - Primary Key No Dups
... and several other fields "Leader, subject, Division, due date etc".

Activity!
[Activity_ID] - Primary Key Dups ok
[Charter_ID] - Primary Key Dups ok
... and several other fields "Activitiy Responsibility, assignment date, due
date etc."

Data!
[Data_ID] - Primary Key Dups ok
[Activity_ID] - Primary Key Dups ok
[Charter_ID] - Primary Key Dups ok
... and several other fields "Data type, collection method, presentation
type, etc."

Okay, now we're getting somewhere. How are Charter, Activity and Data
related? Think in terms of [noun] [verb] [noun] for example...
Each Charter consists of one or more Activities.
Each Activity can belong to only one Charter.

Sounds like I'm being nitpicky, I'm sure, but if you can't articulate
what your database is about, you can't tell if it is structured
correctly. And if it's not, you can't get answers out of it... and
what's the use in that?