Data transfer between tables

[FordsAngel]

I have three tables, Table1, Table2, and an intersection table to join the
other tables. My problem is this: A primary key is auto generated in Table 1,
and I want to port that number directly into a field in Table 2 to display
with Table 2's records. Is there some way to do this easily?

 

[Beetle]

Why?

The intersection table should store the PK values from both tables.

What would be the benefit of redundantly storing Table1's PK value
in Table2?

_________

Sean Bailey

 

[FordsAngel]

It seems that with two auto generated primary keys, that referential
integrity would be better understood if the records were visually linked
within the table. If I explain to someone that record 11204 from Table 1 is
linked to records 11204 AND 11205 in Table 2, it just seems like it would be
a little easier to understand if there was a field specifically supporting
that correlation.

I am really green at this, so maybe I am way off...

 

[Douglas J. Steele]

But having an intersection table implies that you've got a many-to-many
relationship between Table1 and Table2. Which PK value from Table1 would you
put into Table2 when there are multiple to choose from?

 

[Beetle]

There a couple of things that need to be addressed here.

First, Autonumber fields should be used only for the purpose
of allowing the application itself to uniquely identify each record.
They are typically not even displayed to the users because they
have no identifiable meaning to the average user. If the number
you are concerned with is something that the users need to
see because it has a *meaningful, identifiable purpose*, then
Autonumber is not a good data type to use because there is
no guarantee that the numbers will be consecutive. Plus, once
a number is used (even if that record is ultimately not saved), it
will never be used again. This leads to gaps in the number sequence.

The second thing to address is the nature of your relationships. Based
on your last post, it *sounds* like you are describing a one-to-many
relationship between Table1 and Table2. If that is the case, then what
is the purpose of the intersection table you mentioned in your first post?
It may help if you can describe the nature of the relationships between
the tables.

_________

Sean Bailey

 

[FordsAngel]

I had asked another Access user for assistance, and their suggestion was to
create an intersection table despite the one- to- many relationship between
table 1 and table 2. In thinking about it, not a great idea...

I guess it is the auto- generated primary key that is throwing me off, it is
not as comforting as having set values that can be entered into queries from
the memory that they exist in the table, if that makes sense.

As always, thanks for everyone's time.

 

[FordsAngel]

I am creating a database that allows students to enter information relating
to future projects. As these projects are not yet established, the primary
key is auto- instantiated. One project will have multiple volunteer groups,
but volunteer groups are unique and will not participate in multiple
projects. As one student can have many potential projects, or none at all, it
is impossible for me to generate a unique primary key using the student
number. The projects and groups are theoretical until approved, so no unique
number exists to catalogue either.

As such, I have a "Projects table", and a "Volunteer Groups" table, and had
been advised to create a "Project Groups" table... but regardless, my
problems with auto- generation are evident throught.

Thanks for your help!

 

[Beetle]

Based on what you've described, it sounds like the intersection
table should be between Groups and Students, not Projects and
Groups. From your description it sounds like a Student can belong
to more than one Group, but a Group can only work on one Project.

Here is a possible table structure;

tblProjects
********
ProjectID (Autonumber PK)
ProjectDescription
other attributes of the Project

tblGroups
********
GroupID (PK)
ProjectID (FK to tblProjects)
other attributes of the Group

tblStudents
********
StudentID (PK)
FirstName
LastName
other attributes of the Student

tblStudentGroups (the intersection table)
*************
GroupID (FK to tblGroups)
StudentID (FK to tblStudents)
any attributes that are specific to the relationship of Student/Group

tblStudentGroups would typically use GroupID and StudentID as a
combined PK, although you could use a surrogate PK field.

_________

Sean Bailey

 

[FordsAngel]

Thanks so much for your help!

Based on what you've described, it sounds like the intersection
table should be between Groups and Students, not Projects and
Groups. From your description it sounds like a Student can belong
to more than one Group, but a Group can only work on one Project.

Here is a possible table structure;

tblProjects
********
ProjectID (Autonumber PK)
ProjectDescription
other attributes of the Project

tblGroups
********
GroupID (PK)
ProjectID (FK to tblProjects)
other attributes of the Group

tblStudents
********
StudentID (PK)
FirstName
LastName
other attributes of the Student

tblStudentGroups (the intersection table)
*************
GroupID (FK to tblGroups)
StudentID (FK to tblStudents)
any attributes that are specific to the relationship of Student/Group

tblStudentGroups would typically use GroupID and StudentID as a
combined PK, although you could use a surrogate PK field.

_________

Sean Bailey