How to get lookup field to display more than one field

[ACFerguson via AccessMonster.com]

I have a table called "Students" in this table are several fields including
First name, Last Name, Student Number, Phone Number, and so on. I have
another table called "Assignment Details" with includes many fields,
including a lookup to the "Students" table. I have set as the primary sort
the Last Name field from the "Students" table. This is the only field that
displays information in the Assignment Details table. I would like to display
that data from several fields from each record in the "Students" table, but I
can't figure out how this might be done. any help would be much appriciated!

Thanks

Tony

 

[Jeff Boyce]

Tony

If I'm understanding your description, you are trying to store and display
redundant data in more than one table. There are several reasons why this
is not a good idea.

First, redundant data means you have to come up with a way to ensure data
integrity ... which table holds the correct data?!

Next, if you are working directly in the tables, you've mistaken Access for
a spreadsheet. Access tables store data, Access forms (and reports) display
it. You are much better off using the rich event model available in Access
forms to help you display data.

Finally, it may be that your data structure is not well-normalized. If
"normalization" and "relational database" are not familiar, plan to spend
some time brushing up on them. You won't get the best use of Access'
relationally-oriented features/functions if you don't feed it
relationally-organized data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ACFerguson via AccessMonster.com]

Hi Jeff,

I am certainly a neophite when it comes to using Access. I believe that I
have set this up as a relational database (one record in the Assignment
Details table is related to many records in the Student's table).

I am trying (ultimately) to get the information to the reports.

Essentially, what I want to do is to create a student information list
(including first and last names) and allow teachers to select a student from
a drop-down box and then enter in any outstanding assignment details. From
this, I want to generate a number of reports. The first one will outline the
student name (first and last) and some basic contact information, allong with
the assignment details. The next report I want to generate will (I think)
have to come from a querry that checks for any due dates over 7 days old and
then reports all of the students and assignment details that are overdue.

Any of the databases that I've created in the past could have easily been
done using a spreadsheet, so this is certainly more complicated for me than
this; however, I usually a quick learner and hope I can muddle my way through
this with a bit of steering in the right direction from folks who know a lot
more than I do!

thanks

Tony

 

[Jeff Boyce]

My confusion comes from the fact that you posted in a "forms" newsgroup but
describe what you're doing in terms of tables and reports (different objects
in Access).

If this were mine, I'd first get the tables right (structure), then create
queries to use to feed forms (data entry/display on screen). Finally, I'd
build queries that return the data I wish to print out, via reports, and
create the reports that use that data.

Take a look at "combo boxes" for ideas on that drop-down list of students.

Feel free to post back specific questions in the various newsgroups ...
that's what they're here for!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ACFerguson via AccessMonster.com]

Thanks, Jeff. I will read up on Combo Boxes and likely have a number of
questions after...

Cheers

Tony

My confusion comes from the fact that you posted in a "forms" newsgroup but
describe what you're doing in terms of tables and reports (different objects
in Access).

If this were mine, I'd first get the tables right (structure), then create
queries to use to feed forms (data entry/display on screen). Finally, I'd
build queries that return the data I wish to print out, via reports, and
create the reports that use that data.

Take a look at "combo boxes" for ideas on that drop-down list of students.

Feel free to post back specific questions in the various newsgroups ...
that's what they're here for!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,

[quoted text clipped - 29 lines]

Tony