Relationships and Lookups

[Guest]

I recently transferred a large data-spreadsheet into Access. I created a
database that contains several tables and multiple relationships. I figured
out how to link primary keys from one table to another and use lookups in
forms to enter data quickly, but I'd like to be able to select an item from
the lookup list and have that value dictate other values in the record.

For example, if I select a familyID from the lookup combo box, I want the
family's head of household to automatically display in another column in that
same record.

Is that possible? Or is that bad practice?

Thanks,
Matthew Pfluger

 

[tina]

if you're going to *display* the looked-up data in the form, that's fine -
common practice. if you're actually wanting to copy the data from the
looked-up table and *store* it in another table, that's duplication of
data - which breaks normalization rules, and yes it's bad practice. rule of
thumb is the only "duplicate" data between tables should be the
primary/foreign key values which link records between two tables.

hth

 

[Jeff Boyce]

Matthew

In addition to Tina's comments...

Why would you force your user to remember/use a "familyID", when they could
be looking up familyName or familyHeadOfHousehold? You could still use a
combo box, and still bind it to the familyID field, but on your form you
could have THAT control display familyHeadOfHousehold instead of familyID.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Tina,

Could you tell me what that is called so I may search for it or tell me
where I can get more information? I need to learn more about that.

Thanks,
Matthew Pfluger

 

[tina]

Could you tell me what that is called so I may search for it or tell me

where I can get more information?

you've lost me, hon. what "that" are you referring to?

hth

 

[Guest]

I apologize. I meant "that" to be how to lookup a value from a related table
based on a value selected in a different column in the same record.

Example: I have three tables, tblStudents, tblClassTimes, and
tblClassRoster. The first contains students, the second contains a list of
classes and their offering times, and the third contains which classes each
student has selected. I think people refer to this table as a junction
table? I saw that term on Steve Schapel's website.

Each student can sign up for many classes, but there are always unique
students. Each class time can be registered many times. Within
tblClassRoster (or more accurately frmClassRoster, for each record, I want to
select a student from tblStudents and other column values within that record
automatically update (such as their family, age, etc.). I also want the same
to happen when I select a class (cost, etc.). I'm not sure what this is
called or how to do it.

I did find a example database at:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40821&highlight=manytomany

See the entry by Pat Hartman. I want to know how to do this while
minimizing file size and complexity. Eventually I'd like to query this table
for by class or by student.

Thanks!
Matthew Pfluger

 

[tina]

Within

tblClassRoster (or more accurately frmClassRoster, for each record, I want to
select a student from tblStudents and other column values within that record
automatically update (such as their family, age, etc.)

"update" implies that you want to store this student data in tblClassRoster.
as i said before, that breaks normalization rules and is bad practice.
student data such as family, date of birth, etc, belong in the student table
and nowhere else. store the student *primary key value* in tblClassRoster;
when you want to combine class roster data with student data, link the two
tables in a query.

hth