How to populate fields from records in sep. table

[robins76jd]

Obviously, I'm not great with Access. I think this should be somewhat simple, yet can't figure it out. I have a table with several records in it. I want to create "form" (maybe??) to make it easier for my secretary to update student information. On the header of the form I'd like to have the student's firstname, lastname, & gradelevel fields pulled and automatically filled in from the records in the table. In the details of the form, I would like my secretary to simply enter completion dates and grades for certain tasks (other fields in the aforementioned) table which would in turn update the fields in the table.



Am I making sense, or am I way off?

Thanks for any help!

[NoellaG]

Hi,

reading your post I think you should have more then one table, for example:

one with the student information:

tblStudents
--------------
stuID (autonumber) = PK
stuFirstName (text)
stuLastName (text)
stuBirthdate (date/time)
.............

one with the tasks information:

tblTasks
---------
taskID (autonumber) = PK
taskdescription (text)
...............

one that combines the two:
--------------------------

tblStudentTasks
-------------------
sttID (autonumber) = PK
sttStudent (long integer) = FK to stuID
sttTask (long integer) = FK to taskID
sttCompletedOn (date/time)
.....

Then you can create a form for frmStudents :Record Source =tblStudents, and a subform fsfrStudentTasks: RecordSource = tblStudentTasks.

If you have defined the relationships right, then once you place the subform on the form, the link field properties should be set automatically.

succes
NG

[robins76jd]

but what is PK and FK? I just feel like I'm soooo close. I've already had the multiple tables created....I think I might be having the problem with linking/ creating the relationships. I guess this is what I get for always relying on Excel to do an Access job.

[NoellaG]

Hi

PK = Primary key.
This is a unique field that cannot be empty (Null) and defines the whole record. For instance your social security number. Once the police enters this number in their computer they know your name, where you live ect... . Only for database purposes this number defines the record for the computer, not humans, so most of the time this is a simple autonumber field. You can set the PK in the design view of an Access table.

FK = Foreign key.
This is the linked field in a related table that refers to a PK field of the table on the 1 side of the one to many relationship. For instance you have customer nr. 5 in the table tblStudents. In the table tblStudentsTasks all you have to do is fill in the number 5 in the field sttStudent. If there is a correct relationship defined between the two tables, the database will be able to look up the data in tblStudents for the student that has number 5 to retrieve the correct name, birthdate ect.

To know how to define those relationships, just buy a basic book or look up an online course.

regards
Noëlla