R
Redbeard
I'm building a database to help my school keep track of textbooks. We
label every textbook with an ID number, and each student is responsible
for turning in that textbook at the end of the school year. Based on
advice that I got in another thread, I'm starting over and redesigning
it to do a better job of following normalization rules. However, this
redesign might lead to forms that will seem "backwards" to my users.
Being able to create easy-to-use forms is the primary reason that I'm
using a database instead of a spreadsheet, so this is a potentially
fatal flaw.
Based on the advice I was given, I'll have an array of tables with
specialized data - student, teacher, courses, textbook, etc.. The most
important table for me will be the text assignment table. It will have
three major fields (and a couple other minor ones). The first major
field will hold the unique ID numbers for each of the textbooks owned
by the school. The second will hold the ISBN number for that textbook
which will link to a table with textbook data (i.e. ISBN, title,
author, etc.). And the third will hold the ID of the student that the
textbook is assigned to.
It would be easy to create a form that lists all of the textbooks, and
uses a combobox to insert the student's ID number. But that would seem
backwards to my users. They expect a form that lists their students,
and lets them select a textbook for each student.
But that would require putting data from the textbookID table into the
Student data table instead of vice-versa, which requires multiple
textbook fields, which violates the normalization rules (which is what
I was doing before).
Can the following be done?
Assume that I have my form showing the list of students, and my
combobox showing the list of available textbooks. When the text number
is selected, can I take the Student ID and store it in the record being
displayed in the combobox?
Just to be clear, I do NOT mean add a new record to the combobox. I
know how to do that. I mean, have the combobox show the available
textbooks by ID number(filtering out those with blank student ID
fields), and fill that student ID field when the combobox is updated.
label every textbook with an ID number, and each student is responsible
for turning in that textbook at the end of the school year. Based on
advice that I got in another thread, I'm starting over and redesigning
it to do a better job of following normalization rules. However, this
redesign might lead to forms that will seem "backwards" to my users.
Being able to create easy-to-use forms is the primary reason that I'm
using a database instead of a spreadsheet, so this is a potentially
fatal flaw.
Based on the advice I was given, I'll have an array of tables with
specialized data - student, teacher, courses, textbook, etc.. The most
important table for me will be the text assignment table. It will have
three major fields (and a couple other minor ones). The first major
field will hold the unique ID numbers for each of the textbooks owned
by the school. The second will hold the ISBN number for that textbook
which will link to a table with textbook data (i.e. ISBN, title,
author, etc.). And the third will hold the ID of the student that the
textbook is assigned to.
It would be easy to create a form that lists all of the textbooks, and
uses a combobox to insert the student's ID number. But that would seem
backwards to my users. They expect a form that lists their students,
and lets them select a textbook for each student.
But that would require putting data from the textbookID table into the
Student data table instead of vice-versa, which requires multiple
textbook fields, which violates the normalization rules (which is what
I was doing before).
Can the following be done?
Assume that I have my form showing the list of students, and my
combobox showing the list of available textbooks. When the text number
is selected, can I take the Student ID and store it in the record being
displayed in the combobox?
Just to be clear, I do NOT mean add a new record to the combobox. I
know how to do that. I mean, have the combobox show the available
textbooks by ID number(filtering out those with blank student ID
fields), and fill that student ID field when the combobox is updated.