Getting Started

[jb2photo]

I have created seven tables labeled:

Clients, Contact Hours, Employees, Notes, Program Code, Services, and SPED
Database. The primary key in all tables is the Student ID.

I've set the relationships for all of the tables to be based on Student ID's
stemming from the SPED Database.

I built a time tracking form, thinking I could work backwards, but the more I
look at it, I'm thinking I need to start from scratch. I have not figured
out how to link it to the database. I would like to have this form linked by
Student ID, to the Program Code table which is a check box and is vital to
make sure that the amount of Contact Hrs and Notes go into the right form.
Ultimately I'd like to have an input field for a student's name and the
Student ID would automatically fill in from the database.

I have very little ACCESS experience and am heading to the library to see if
the Book "Access for Dummies" is available. What suggestions do you have?

 

[John Vinson]

I have created seven tables labeled:

Clients, Contact Hours, Employees, Notes, Program Code, Services, and SPED
Database. The primary key in all tables is the Student ID.

I've set the relationships for all of the tables to be based on Student ID's
stemming from the SPED Database.

You misunderstand the purpose of a Primary Key and of a Foreign Key.

A primary key must be unique within the table for each value of that
key. That is, if the Program Code table has StudentID as its Primary
Key, then each distinct Student has one and only one Program Code.

Each table should have *ITS OWN* primary key, meaningful for that
table. The StudentID would be the primary key of the Students table;
an Employees table would have a different primary key (EmployeeID
let's say); the primary key of the Program Code table might be the
program code itself, if that is short, stable, and unique.

I have no notion from just your table names what the tables contain,
or how they are related... but joining on StudentID is certainly NOT
going to work.

I built a time tracking form, thinking I could work backwards, but the more I
look at it, I'm thinking I need to start from scratch.

Your realization is correct. Designing tables on the basis of a form
is *absolutely backwards*. You pour your foundations first; assembling
the windowframes comes later.

I have not figured
out how to link it to the database. I would like to have this form linked by
Student ID, to the Program Code table which is a check box and is vital to
make sure that the amount of Contact Hrs and Notes go into the right form.
Ultimately I'd like to have an input field for a student's name and the
Student ID would automatically fill in from the database.

And this is going even FURTHER afield. The student name should exist
in the Student table - AND NOPLACE ELSE. That's how a relational
database works; you store data *once*, and then use Queries to link it
to information in other tables.

I have very little ACCESS experience and am heading to the library to see if
the Book "Access for Dummies" is available. What suggestions do you have?

Check out the links at

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

particularly the Database Design 101 link. I fear you'll need to start
over completely.

John W. Vinson[MVP]

 

[jb2photo via AccessMonster.com]

I have created seven tables labeled:

[quoted text clipped - 3 lines]

I've set the relationships for all of the tables to be based on Student ID's
stemming from the SPED Database.

You misunderstand the purpose of a Primary Key and of a Foreign Key.

A primary key must be unique within the table for each value of that
key. That is, if the Program Code table has StudentID as its Primary
Key, then each distinct Student has one and only one Program Code.

Each table should have *ITS OWN* primary key, meaningful for that
table. The StudentID would be the primary key of the Students table;
an Employees table would have a different primary key (EmployeeID
let's say); the primary key of the Program Code table might be the
program code itself, if that is short, stable, and unique.

I have no notion from just your table names what the tables contain,
or how they are related... but joining on StudentID is certainly NOT
going to work.

I built a time tracking form, thinking I could work backwards, but the more I
look at it, I'm thinking I need to start from scratch.

Your realization is correct. Designing tables on the basis of a form
is *absolutely backwards*. You pour your foundations first; assembling
the windowframes comes later.

I have not figured
out how to link it to the database. I would like to have this form linked by
Student ID, to the Program Code table which is a check box and is vital to
make sure that the amount of Contact Hrs and Notes go into the right form.
Ultimately I'd like to have an input field for a student's name and the
Student ID would automatically fill in from the database.

And this is going even FURTHER afield. The student name should exist
in the Student table - AND NOPLACE ELSE. That's how a relational
database works; you store data *once*, and then use Queries to link it
to information in other tables.

I have very little ACCESS experience and am heading to the library to see if
the Book "Access for Dummies" is available. What suggestions do you have?

Check out the links at

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

particularly the Database Design 101 link. I fear you'll need to start
over completely.

John W. Vinson[MVP]

John, thanks for the reply and yeah, I know I need to start over. Thanks.