Linking Tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pht1991 - 14 Jan 2008 00:43 GMT

I need some advice on basic table set-up / linking

I have a student record table with a unique identifier (student ID)
Contains student name and address
I have an enrollment record table with the same unique identifier (student
ID) Contains term and year attended
Now I would like to create a third table to record transcript orders. The
unique identifier would be student ID. However, I would also like the
student name to appear in this table (it is stored in the student record
table). How do I get the name to show up in this new table without
duplicating the information?

Reply to this Message

Olduke - 14 Jan 2008 01:07 GMT

Create a query that contains all 3 tables. Set your relationship to
StudentID in all three tables. Add those fields from each table that you
require.

> I need some advice on basic table set-up / linking
>
[quoted text clipped - 7 lines]
> table). How do I get the name to show up in this new table without
> duplicating the information?

Reply to this Message

pht1991 - 14 Jan 2008 01:13 GMT

How do I "set a relationship"?
Do I need to create a "name" field in my transcript table? How do I get it
to populate with the name from the student record table?

> Create a query that contains all 3 tables. Set your relationship to
> StudentID in all three tables. Add those fields from each table that you
[quoted text clipped - 11 lines]
> > table). How do I get the name to show up in this new table without
> > duplicating the information?

Reply to this Message

John W. Vinson - 14 Jan 2008 01:12 GMT

>I need some advice on basic table set-up / linking
>
[quoted text clipped - 7 lines]
>table). How do I get the name to show up in this new table without
>duplicating the information?

Don't.

Tables are for STORING data, not presenting it or editing it. The student name
should exist in the student record table *and noplace else*.

If you need to see the name in conjunction with data in the transcript order
table, use a Query joining the two tables on StudentID.

Also... it is probably not appropriate to use StudentID as the primary key.
Since the PK is, by definition, unique within the table, this would restrict
each student to only one transcript order. I would presume some students might
want to order two or more transcripts! StudentID should be a non-unique
foreign key (Long Integer if the student record StudentID is an Autonumber),
and the transcripts table should have its own, distinct primary key.

If you haven't already done so, the same principles would apply to the
enrollment table.

John W. Vinson [MVP]

Reply to this Message