Making the same data in one table, with same name, appear automatically in another table and form for record integrity and adding extra information

[Sami]

Please bear with me, and if you answer this question, please do it
step by step. I am new at Access, not at all sophisticated. I am
using Office XP. This will need to be read in Access for Office 2000.

I am creating a database to track student athletes. I have created
the following tables. The table title is to the far left, with fields
under each. The common field will be the StudentID field, which is
their student number assigned by the college, or their SS#.

Tbl_Student_Information
Key: StudentID
LastName
FirstName
MIddleInitial
Title
Freshman
Sophomore
Male
Female

Tbl_AA_Checklist_Completed
Key: AAID
StudentID
Date
Year
Fall
Spring

I have set up a relationship (1 to 1) between the StudentID in both
tables, enforcing referential integrity, only include rows from both
tables which are equal. ( I cannot set up a 1-to-many in this system,
which seems what I should be able to do.)

What I want to do is the following:

1) I want the StudentID from Tbl_Student_Information to automatically
be copied to Tbl_AA_Checklist_Completed, as well as to my form for
this latter, Frm_AA_Checklist_Completed. (If possible, I would like
to be able to also copy LastName, FirstName, and MiddleInitital to the
Tbl_AA_Checklist_Completed and Frm_AA_Checklist_Completed.

2) I would like to be able to have all this information automatically
copied to all other tables I have made, and then the information to
automatically appear in the forms that I create in the Wizard, and
then manipulate and move around to make them prettier.

I have tried to create one table, rather than several, for all
information, but Access says it cannot create the form using the
Wizard because such a table has probably 100+ fields.

Please answer me here, and if you are willing to correspond privately,
I will then give you my email address.

Thanks for your help.

[Robert]

Sami,

The short answer to your question is, I think you need a one-to-many
relationship between your tables, and the reason Access is not letting you
make one is that your KEY field in your STUDENTS table has to be an
AUTONUMBER, PRIMARY KEY, and the KEY field in your CHECKLIST table has to be
a NUMBER (LONG) INDEXED, DUPLICATES OK. If you make these changes and then
re-create your relationship, I think you will find that Access will allow
you to create a one-to-many relationship.
Now here is the long answer:

A good database design will typically gravitate to more tables, not less.
So several tables is not necessarily a bad thing. The reason for this is
based on a simple principle: Each piece of information (such as Name or
Grade Level) in a well-designed database is store once, and only once
(except for keys).

The way you accomplish this is to split your "entities" into separate
tables. An entity is one member of a collection of like objects (such as
Students or Classes). So for example, one of your tables is Students,
student being an entity. Each record in your table corresponds to one
student.

Every entity has a set of "attributes," or characteristics. The Student
table can store more than one (different) piece of information (or
"attribute") about each student. These pieces of information are called
fields. LastName, FirstName, MiddleInitial, etc. If you think of your
table as a little spreadsheet, then each row corresponds to one student, and
each column corresponds to an "attribute" of that student. Notice that
every student has the same set of attributes, but may have different
"values" for these attributes (e.g. each student has a different name).

Now here's where it gets interesting. If the pieces of information about an
entity are different "attributes," then they are stored in the same record
as the other "entity" information. However, if the pieces of information
you are trying to store are multiple different values for the SAME ATTRIBUTE
for the SAME ENTITY, then you put them in a separate table. This is the
reason Access would not let you put all of the information in the same
record.

Here is an example. Each student has only ONE name. Therefore NAME is a
FIELD in the STUDENT table. However, each student can go to class for more
than one year. Therefore the checklist CANNOT be stored in the STUDENT
table, because you cannot predict how many years the student will go to
school. The checklists must be stored in another table, with a ONE TO MANY
RELATIONSHIP between the two tables.

Each record in STUDENT_CHECKLIST contains a pointer field back to ONE
STUDENT RECORD. Here is what this would look like:

STUDENT TABLE

StudentID Autonumber
LastName Text
FirstName Text
MiddleInitial Text
GradeLevel Number (Integer)

STUDENT_CHECKLIST

ChecklistID Autonumber <---- NOTE: Each table always has
its own Primary Key.
StudentID Number <---- This is the pointer back
to the Student's Record in the STUDENT TABLE
Date Number
FallCompleted Yes/No
SpringCompleted Yes/No
Notice that StudentID is in both tables. These two fields are the basis for
the ONE-TO-MANY relationship. The StudentID in the STUDENT_TABLE must be a
PRIMARY KEY, and the StudentID in the STUDENT_CHECKLIST table must be a
NUMBER (LONG), INDEXED, DUPLICATES OK.

Finally, this is not the only possible arrangement of these fields and
records. Depending on what it is that your trying to do, there may be a
better possible design.

I hope this helps. Let me know if you have any further questions.
Robert
ro**********@nospam-unforgettable.com
To email me, remove the "nospam" (and the dash) from the above email
address.
<Sami> wrote in message news:92********************************@4ax.com...

Please bear with me, and if you answer this question, please do it
step by step. I am new at Access, not at all sophisticated. I am
using Office XP. This will need to be read in Access for Office 2000.

I am creating a database to track student athletes. I have created
the following tables. The table title is to the far left, with fields
under each. The common field will be the StudentID field, which is
their student number assigned by the college, or their SS#.

Tbl_Student_Information
Key: StudentID
LastName
FirstName
MIddleInitial
Title
Freshman
Sophomore
Male
Female

Tbl_AA_Checklist_Completed
Key: AAID
StudentID
Date
Year
Fall
Spring

I have set up a relationship (1 to 1) between the StudentID in both
tables, enforcing referential integrity, only include rows from both
tables which are equal. ( I cannot set up a 1-to-many in this system,
which seems what I should be able to do.)

What I want to do is the following:

1) I want the StudentID from Tbl_Student_Information to automatically
be copied to Tbl_AA_Checklist_Completed, as well as to my form for
this latter, Frm_AA_Checklist_Completed. (If possible, I would like
to be able to also copy LastName, FirstName, and MiddleInitital to the
Tbl_AA_Checklist_Completed and Frm_AA_Checklist_Completed.

2) I would like to be able to have all this information automatically
copied to all other tables I have made, and then the information to
automatically appear in the forms that I create in the Wizard, and
then manipulate and move around to make them prettier.

I have tried to create one table, rather than several, for all
information, but Access says it cannot create the form using the
Wizard because such a table has probably 100+ fields.

Please answer me here, and if you are willing to correspond privately,
I will then give you my email address.

Thanks for your help.

[DFS]

<Sami> wrote in message news:92********************************@4ax.com...

Please bear with me, and if you answer this question, please do it
step by step. I am new at Access, not at all sophisticated. I am
using Office XP. This will need to be read in Access for Office 2000.

I am creating a database to track student athletes. I have created
the following tables. The table title is to the far left, with fields
under each. The common field will be the StudentID field, which is
their student number assigned by the college, or their SS#.

Tbl_Student_Information
Key: StudentID
LastName
FirstName
MIddleInitial
Title
Freshman
Sophomore
Male
Female

Tbl_AA_Checklist_Completed
Key: AAID
StudentID
Date
Year
Fall
Spring

What's the AA checklist? Can it be completed only once? If so, make it a
part of the student table.

Tbl_Student_Information
Key: StudentID
LastName
FirstName
MIddleInitial
Title
Freshman
Sophomore
Male
Female
AAChecklistCompletedDate

From the date you can derive the Year, and whether it was Fall or Spring.

I have set up a relationship (1 to 1) between the StudentID in both
tables, enforcing referential integrity, only include rows from both
tables which are equal. ( I cannot set up a 1-to-many in this system,
which seems what I should be able to do.)

What I want to do is the following:

1) I want the StudentID from Tbl_Student_Information to automatically
be copied to Tbl_AA_Checklist_Completed, as well as to my form for
this latter, Frm_AA_Checklist_Completed. (If possible, I would like
to be able to also copy LastName, FirstName, and MiddleInitital to the
Tbl_AA_Checklist_Completed and Frm_AA_Checklist_Completed.

2) I would like to be able to have all this information automatically
copied to all other tables I have made, and then the information to
automatically appear in the forms that I create in the Wizard, and
then manipulate and move around to make them prettier.
Except for StudentID, you don't want to be copying student data from one
table to another

I have tried to create one table, rather than several, for all
information, but Access says it cannot create the form using the
Wizard because such a table has probably 100+ fields.

Please answer me here, and if you are willing to correspond privately,
I will then give you my email address.

Thanks for your help.

[Sami]

Hi Robert,

First, thanks for the detailed answer.

My first problem is that in Access XP, any time I set up a
relationship, it always, ALWAYS defaults to one-to-one. That seems to
be the first issue I need to clear up. Using the relationships page,
I drag and drop from the table wherein the StudentID resides as the
primary key, and drag it to StudentID in another table where it is NOT
the primary key.

For my DB, the StudentID is either the SSN or a number, also 9 digits
and divided as xxx-xx-xxxx to parallel the SSN, as assigned by the
school.

Therefore, if I have read you correctly, I must make these numbers
long integers for them to work as one-to-many? Let's start with this
question!

Thanks!

On Sun, 18 Jan 2004 06:56:49 GMT, "Robert"
<ro**********@nospam-unforgettable.com> wrote:

Sami,

The short answer to your question is, I think you need a one-to-many
relationship between your tables, and the reason Access is not letting you
make one is that your KEY field in your STUDENTS table has to be an
AUTONUMBER, PRIMARY KEY, and the KEY field in your CHECKLIST table has to be
a NUMBER (LONG) INDEXED, DUPLICATES OK. If you make these changes and then
re-create your relationship, I think you will find that Access will allow
you to create a one-to-many relationship.
Now here is the long answer:

A good database design will typically gravitate to more tables, not less.
So several tables is not necessarily a bad thing. The reason for this is
based on a simple principle: Each piece of information (such as Name or
Grade Level) in a well-designed database is store once, and only once
(except for keys).

The way you accomplish this is to split your "entities" into separate
tables. An entity is one member of a collection of like objects (such as
Students or Classes). So for example, one of your tables is Students,
student being an entity. Each record in your table corresponds to one
student.

Every entity has a set of "attributes," or characteristics. The Student
table can store more than one (different) piece of information (or
"attribute") about each student. These pieces of information are called
fields. LastName, FirstName, MiddleInitial, etc. If you think of your
table as a little spreadsheet, then each row corresponds to one student, and
each column corresponds to an "attribute" of that student. Notice that
every student has the same set of attributes, but may have different
"values" for these attributes (e.g. each student has a different name).

Now here's where it gets interesting. If the pieces of information about an
entity are different "attributes," then they are stored in the same record
as the other "entity" information. However, if the pieces of information
you are trying to store are multiple different values for the SAME ATTRIBUTE
for the SAME ENTITY, then you put them in a separate table. This is the
reason Access would not let you put all of the information in the same
record.

Here is an example. Each student has only ONE name. Therefore NAME is a
FIELD in the STUDENT table. However, each student can go to class for more
than one year. Therefore the checklist CANNOT be stored in the STUDENT
table, because you cannot predict how many years the student will go to
school. The checklists must be stored in another table, with a ONE TO MANY
RELATIONSHIP between the two tables.

Each record in STUDENT_CHECKLIST contains a pointer field back to ONE
STUDENT RECORD. Here is what this would look like:

STUDENT TABLE

StudentID Autonumber
LastName Text
FirstName Text
MiddleInitial Text
GradeLevel Number (Integer)

STUDENT_CHECKLIST

ChecklistID Autonumber <---- NOTE: Each table always has
its own Primary Key.
StudentID Number <---- This is the pointer back
to the Student's Record in the STUDENT TABLE
Date Number
FallCompleted Yes/No
SpringCompleted Yes/No
Notice that StudentID is in both tables. These two fields are the basis for
the ONE-TO-MANY relationship. The StudentID in the STUDENT_TABLE must be a
PRIMARY KEY, and the StudentID in the STUDENT_CHECKLIST table must be a
NUMBER (LONG), INDEXED, DUPLICATES OK.

Finally, this is not the only possible arrangement of these fields and
records. Depending on what it is that your trying to do, there may be a
better possible design.

I hope this helps. Let me know if you have any further questions.
Robert
ro**********@nospam-unforgettable.com
To email me, remove the "nospam" (and the dash) from the above email
address.
<Sami> wrote in message news:92********************************@4ax.com...

Please bear with me, and if you answer this question, please do it
step by step. I am new at Access, not at all sophisticated. I am
using Office XP. This will need to be read in Access for Office 2000.

I am creating a database to track student athletes. I have created
the following tables. The table title is to the far left, with fields
under each. The common field will be the StudentID field, which is
their student number assigned by the college, or their SS#.

Tbl_Student_Information
Key: StudentID
LastName
FirstName
MIddleInitial
Title
Freshman
Sophomore
Male
Female

Tbl_AA_Checklist_Completed
Key: AAID
StudentID
Date
Year
Fall
Spring

I have set up a relationship (1 to 1) between the StudentID in both
tables, enforcing referential integrity, only include rows from both
tables which are equal. ( I cannot set up a 1-to-many in this system,
which seems what I should be able to do.)

What I want to do is the following:

1) I want the StudentID from Tbl_Student_Information to automatically
be copied to Tbl_AA_Checklist_Completed, as well as to my form for
this latter, Frm_AA_Checklist_Completed. (If possible, I would like
to be able to also copy LastName, FirstName, and MiddleInitital to the
Tbl_AA_Checklist_Completed and Frm_AA_Checklist_Completed.

2) I would like to be able to have all this information automatically
copied to all other tables I have made, and then the information to
automatically appear in the forms that I create in the Wizard, and
then manipulate and move around to make them prettier.

I have tried to create one table, rather than several, for all
information, but Access says it cannot create the form using the
Wizard because such a table has probably 100+ fields.

Please answer me here, and if you are willing to correspond privately,
I will then give you my email address.

Thanks for your help.

[Sami]

The AA Checklist is to see if a student has fulfilled requirements for
receiving an AA or AS degree. Required of student athletes in a
community college setting.

On Sun, 18 Jan 2004 10:04:26 -0500, "DFS" <no****@nospam.com> wrote:

<Sami> wrote in message news:92********************************@4ax.com...

Please bear with me, and if you answer this question, please do it
step by step. I am new at Access, not at all sophisticated. I am
using Office XP. This will need to be read in Access for Office 2000.

I am creating a database to track student athletes. I have created
the following tables. The table title is to the far left, with fields
under each. The common field will be the StudentID field, which is
their student number assigned by the college, or their SS#.

Tbl_Student_Information
Key: StudentID
LastName
FirstName
MIddleInitial
Title
Freshman
Sophomore
Male
Female

Tbl_AA_Checklist_Completed
Key: AAID
StudentID
Date
Year
Fall
Spring

What's the AA checklist? Can it be completed only once? If so, make it a
part of the student table.

Tbl_Student_Information
Key: StudentID
LastName
FirstName
MIddleInitial
Title
Freshman
Sophomore
Male
Female
AAChecklistCompletedDate

From the date you can derive the Year, and whether it was Fall or Spring.

I have set up a relationship (1 to 1) between the StudentID in both
tables, enforcing referential integrity, only include rows from both
tables which are equal. ( I cannot set up a 1-to-many in this system,
which seems what I should be able to do.)

What I want to do is the following:

1) I want the StudentID from Tbl_Student_Information to automatically
be copied to Tbl_AA_Checklist_Completed, as well as to my form for
this latter, Frm_AA_Checklist_Completed. (If possible, I would like
to be able to also copy LastName, FirstName, and MiddleInitital to the
Tbl_AA_Checklist_Completed and Frm_AA_Checklist_Completed.

2) I would like to be able to have all this information automatically
copied to all other tables I have made, and then the information to
automatically appear in the forms that I create in the Wizard, and
then manipulate and move around to make them prettier.

Except for StudentID, you don't want to be copying student data from one
table to another

I have tried to create one table, rather than several, for all
information, but Access says it cannot create the form using the
Wizard because such a table has probably 100+ fields.

Please answer me here, and if you are willing to correspond privately,
I will then give you my email address.

Thanks for your help.

[DFS]

<Sami> wrote in message news:8q********************************@4ax.com...

The AA Checklist is to see if a student has fulfilled requirements for
receiving an AA or AS degree. Required of student athletes in a
community college setting.
If so, it seems like an attribute of the student, and can safely be placed
within your Student information table.
On Sun, 18 Jan 2004 10:04:26 -0500, "DFS" <no****@nospam.com> wrote:

<Sami> wrote in message news:92********************************@4ax.com...

Please bear with me, and if you answer this question, please do it
step by step. I am new at Access, not at all sophisticated. I am
using Office XP. This will need to be read in Access for Office 2000.

I am creating a database to track student athletes. I have created
the following tables. The table title is to the far left, with fields
under each. The common field will be the StudentID field, which is
their student number assigned by the college, or their SS#.

Tbl_Student_Information
Key: StudentID
LastName
FirstName
MIddleInitial
Title
Freshman
Sophomore
Male
Female

Tbl_AA_Checklist_Completed
Key: AAID
StudentID
Date
Year
Fall
Spring

What's the AA checklist? Can it be completed only once? If so, make it apart of the student table.

Tbl_Student_Information
Key: StudentID
LastName
FirstName
MIddleInitial
Title
Freshman
Sophomore
Male
Female
AAChecklistCompletedDate

From the date you can derive the Year, and whether it was Fall or Spring.

I have set up a relationship (1 to 1) between the StudentID in both
tables, enforcing referential integrity, only include rows from both
tables which are equal. ( I cannot set up a 1-to-many in this system,
which seems what I should be able to do.)

What I want to do is the following:

1) I want the StudentID from Tbl_Student_Information to automatically
be copied to Tbl_AA_Checklist_Completed, as well as to my form for
this latter, Frm_AA_Checklist_Completed. (If possible, I would like
to be able to also copy LastName, FirstName, and MiddleInitital to the
Tbl_AA_Checklist_Completed and Frm_AA_Checklist_Completed.

2) I would like to be able to have all this information automatically
copied to all other tables I have made, and then the information to
automatically appear in the forms that I create in the Wizard, and
then manipulate and move around to make them prettier.

Except for StudentID, you don't want to be copying student data from one
table to another

I have tried to create one table, rather than several, for all
information, but Access says it cannot create the form using the
Wizard because such a table has probably 100+ fields.

Please answer me here, and if you are willing to correspond privately,
I will then give you my email address.

Thanks for your help.