Using a combobox in reverse

[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.

 

[Allen Browne]

You will have at least the 3 tables below:

Textbook table (one record for each book instance):
TextbookID AutoNumber primary key
Title Text book name.
...

Student table (one record for each student):
StudentID AutoNumber primary key
Surname Text student's family name.
...

BookAssign table (one record for each book assigned to a student):
BookAssignID AutoNumber primary key
TextBookID Number which book is assigned.
StudentID Number who the book is assigned to.
AssignDate Date/Time when this book was assigned to this
student.
ReturnDate Date/Time blank until the student returns the book.

Now you can create a main form bound to the student table, with a subform
bound to the BookAssign table. In the subform, you will have a combo box
that lets you choose the book instance. (I say 'book instance' because the
school will have multiple instances of the same book titles, and it is a
particular instance of a book that gets assigned to a student.)

 

[Redbeard]

Thanks. I can see the advantage of this over the approach that I was
going to use.

However, am I correct is assuming that since I'd have to use a subform,
that will effectively limit me to Single Form view? I had hoped to use
continuous forms so the form would appear as a spreadsheet-like class
list, as that would greatly simplify data entry. But it looks like
that isn't going to be possible.

 

[Allen Browne]

Subform can be in Continuous view (or even datasheet if you wish.)

 

[Redbeard]

True, but that will show a list of textbooks, instead of a list of
students. Ideally, I'd like the form to look like a student list with
comboboxes to select the textbook instance, but I don't think that is
going to be possible with normalized tables. As I understand things,
the main form can't be a continuous form if it contains a subform.

Sometimes you can't have your cake, and eat it too. :^(

 

[tina]

PMFJI. i think you can use the linking table approach that Allen suggested -
you just need to be a little creative in your approach to the data entry.

first of all, who will be assigning specific textbook copies to specific
students? will each teacher have a stack of textbooks for a given class, and
hand them out to the students in her class at the beginning of the term? if
you have data in your tables that link specific textbooks to specific
classes, you should be able to isolate the textbook records that belong to a
specific class taught by a specific teacher - and thus identify a roster of
the students in that class. you can use code or macros to "pre-fill" the
BookAssign table, that Allen outlined, with the StudentIDs of students in a
specific class, pull those records in a form (not necessarily a subform),
and then use a combobox control on the TextBookID field to give you your
list of textbooks to be assigned.

hard to be more specific than that, without a clearer picture of your
tables/relationships design.

hth

 

[Allen Browne]

Tom, once you have those 3 tables in place, you can create main form for the
textbooks, with students in the subform, or you can create the main form for
students with text books in the subform. You can't go wrong.

 

[Redbeard]

Thanks for the input. I see where you are coming from, but in my case,
it wouldn't be practical. If the books were pre-assigned to the
students, teachers would have to go hunting for those specific
textbooks instead of simply grabbing the correct number of textbooks
out of the storeroom before classes start and handing them out row by
row. That won't happen.

The way this is usually done is that teachers will hand out the books
and then pass around the text assignment sheet (which our school
requires) so each student can list which textbook they received.

The main problem with the "Single Form" approach is that the students
won't be in any logical order on this assignment sheet so teachers
would have to constantly go back and forth in any form. If the form
was set so it looks like a spreadsheet, that type of non-sequential
data entry is much easier.

When I designed the database without normalized tables, I was able to
accomplish the type of data entry form that I want. But, of course, I
ran into other problems, which is why I'm trying to reengineer it.

I might have to choose between proper design, and ease of data entry.
If the data entry is too hard, it won't get used (use is voluntary) and
my work will have been for nothing. If I don't use normalized tables,
I can't include some peripheral features and may find future features
out of reach too.

 

[tina]

the scenario i painted might not work, but the point is that you rarely have
to sacrifice normalized design due to user interface issues. within the
parameters of normalized, linked tables, there is almost always a way to
support data entry needs - again, it just takes creativity, and a certain
skill level with the software.

if you would like me to look at your db and see if i can come up with a
workable solution within a normalized design framework, post back with your
email address and i'll email you. make sure you disguise your email address
(and tell me how to "decode" it), so you don't get spammed to death.

hth

 

[Redbeard]

I'll keep playing with it.

We're halfway through the first grading period and the rest of the
teachers are wanting to enter their text data. What I have now will do
what HAS to be done, so I may just go with it for this year.

Every year we'll have to "throw out" the old data and start from
scratch anyway, so I can wait until then to unveil any "new and
improved" version without having to worry about backward compatibility.

Thanks for your help.

 

[Redbeard]

Tina,
Thanks for the offer, but the database includes data imported from
exterior tables. That data, by law, is confidential. I had to get
special permission from the principal before the IT dept. could even
send it to me, and I teach at the school! Sending it elsewhere is out
of the question.

You are right that the problem is going to come down to skill level
with the software, and that is where I come up short. I took a month
long Access class back in the Access 95 or 97 days and haven't used it
since. I also do a little programming in Java. Sadly, that makes me
the most qualified person here to create this database. But whatever I
come up with will be better than we have now - which involves index
cards being passed from teacher to teacher across a big meeting table
at the beginning of the second semester. (due to scheduling conflicts,
over 80% of our kids have different teachers for first semester than
they do during second semester - which is why keeping track of the
textbooks is such a problem)

 

[tina]

you're welcome, re the offer, sorry i couldn't be more help. if you decide
it's "safe" to delete all the data from a copy of your db and send the copy
with a few records of completely fake data, the offer remains open - as long
as i have this thread flagged (probably for another week or two).

good luck with your project!

 

[Redbeard]

I went ahead and put the non-normalized db into use for this year.
Since I have to start with all new data every year, I can reengineer it
at my leisure without worrying about backward compatibility.
Basically, my deadline has been postponed until next August.

However, I can see one problem already - that has nothing to do with
data entry - which I don't see how to tackle without using separate
fields. Students can be assigned up to seven different textbooks in a
single class. Right now, I'm using one field for each textbook - which
allows me to ensure that each student get one of each book, and not two
of one and none of another.

Obviously, a normalized table, like what Allen describes, would prevent
two of the same text from being assigned to one student, but I'm not
sure how I'd make sure one of each type was assigned, much less create
an easy to use form that would assist the assignment process.

Any ideas?

Thanks

 

[tina]

well, that sounds like it really *is* a data entry issue, or perhaps it's
more precise to call it a user interface issue. again, you start with
normalized tables/relationships, and then come up with a creative way to
support accurate, easy data entry by the user. one trick that i employ
occasionally is to "pre-enter" records into a table (via the user interface)
with an Append query, and then present the user with existing records in a
form, that s/he can (or must) complete - rather than depending on the user
to create all the appropriate records.
i don't know if i'd use that particular strategy in your case, because i
don't know enough about the database design or the process it needs to
support. whenever you need to go beyond the "standard" data entry forms
setups, it requires thinking outside the box, thoroughly understanding the
real-world process that needs to be supported, and usually going through
some trial-and-error (sometimes a great deal of it) to come up with a unique
solution that fits your unique needs. it's just not the sort of thing i'd
try to solve in a newsgroup thread.

hth

 

[Redbeard]

I'm glad that I decided to run with what I have for now. Your comments
seem to confirm my suspicions that trying to go to normalized tables
will not be very easy in my case, and I need something that works now.
The normalized tables can wait until next years version. (But I'll
work on that in the meantime.)

Thanks again for your help.

 

[tina]

you're welcome