Getting one field from seven

[Redbeard]

The subject line stinks, but I couldn't think of anything better.

I have data that I need in two different forms, and I think I'm stuck
keeping it in two different tables and fighting to keep both
up-to-date. But I thought I'd put the problem out here in hopes that
someone will show me a better way.

I'm creating a database to help keep track of textbooks at my high
school. Some courses have more than one textbook - one actually has
seven. There is also at least one instance where two different courses
use the same text (though they teach different parts of it).

I have a table called CourseTextsTBL that has the following fields
(CourseName [key], Text1ISBN, Text2ISBN ... Text7ISBN) where ISBN is
the textbook's ISBN number. This links to a separate table which uses
the ISBN number for the key and has data about the text (i.e.
publisher, copyright, etc.). I use CourseTextsTBL for the majority of
the work done in the database, and for most courses, the only field
(other than the key field) that contains data is Text1ISBN.

But once in a while, I need the data listed with two fields (CourseName
& ISBN). Since some books get used in two different courses, and since
some courses use multiple books, both fields will have duplicates and
neither field could be a key. However, the combined fields can be the
key, since the combination would always be unique. Or I could probably
get by without a key since there would never be more than a few dozen
records.

I've been racking my brain trying to figure out if there is a way to
use a query to get the latter dataset from the first table. I've all
but concluded that it can't be done, and I'm stuck maintaining two
tables with the same data in them. But if anyone can help me see a
better way ...

 

[Redbeard]

Nothing like posting a question to trigger an epiphany.

I used a macro to run a make-table query with the CourseName and
Text1ISBN, then run an append queries with the CourseName and
Text2ISBN, etc. until I had all seven ISBN fields accounted for. When
I'm done, I have my data in a table in the form that I need it.

But if someone can think of a better way, I'd still love to hear it.

 

[George Nicholson]

You didn't really ask, but FWIW, the proper data structure for what you
describe would be something like the following. ISBN1, ISBN2 violates
normalization rules (i.e., is bad design) and is problematic to work with,
as you have discovered.

tblCourses
CourseID
CourseName
-unique index on CourseID (or name)

tblTexts
ISBN
TextName
-unique index on ISBN

tblCourseTexts
CourseID
ISBN
-Unique Index on Course + Text combination.
-Courses may appear more than once, since they may have multiple books.
-Texts may appear more than once, since they may be used in multiple
courses
-*But* the Course + Text combination will be unique and can be
enforced.

HTH,

 

[James A. Fortune]

Nothing like posting a question to trigger an epiphany.

I used a macro to run a make-table query with the CourseName and
Text1ISBN, then run an append queries with the CourseName and
Text2ISBN, etc. until I had all seven ISBN fields accounted for. When
I'm done, I have my data in a table in the form that I need it.

But if someone can think of a better way, I'd still love to hear it.

tblCourses
CourseID Autonumber PK
CourseName Text

tblBooks
ISBN Text PK
BookTitle Text

tblCourseBooks
CBID Autonumber PK
CourseID Long FK
ISBN Text FK

Sample Query:

SELECT tblCourses.CourseName, tblCourseBooks.ISBN FROM tblCourses INNER
JOIN tblCourseBooks ON tblCourses.CourseID = tblCourseBooks.CourseID
WHERE tblCourses.CourseID = 1;

James A. Fortune

 

[Redbeard]

You didn't really ask, but FWIW, the proper data structure for what you

describe would be something like the following. ISBN1, ISBN2 violates
normalization rules (i.e., is bad design) and is problematic to work with,
as you have discovered.

I took a month-long Access class about 6 or 7 years ago, so I'm not
surprised that my design isn't up to snuff. I can use all the help I
can get.

However, there was a reason for setting up the table that way. The
goal is to track the textbooks that we assign students. Student data
is stored by a combination of the StudentID and the CourseName. So
Bobby Smith in English class is a separate record from Bobby Smith in
Spanish or Algebra. Theoretically, each class can assign up to seven
different textbooks, so the student text assignment table has seven
fields to store textbook ID numbers.

Those textbook ID numbers are unique within a given series, but there
are probably a ton of different textbooks with the same ID. For
example, only one Algebra book will have an ID number of '7', but there
is certainly a Biology book numbered '7', a Spanish book numbered '7'
and so on. In the class that assigns 7 textbooks, it is theoretically
possible that a kid could get seven different books with the same ID
number. I can't change the way things are numbered, so I set up the
fields (and forms) so only certain texts can be entered in certain
fields. That way I can tell which book is which by what field the ID
number is entered into. So I could tell that Bobby Smith in English
should have Grammar book 7 and Literature book 9 because 7 is in the
first field and 9 is in the second.

But when I'm going in reverse - my current task - I know the book and
and ID number, but not the student. Which is why I needed to be able
to put the textbook data in the more conventional form.

Again, if you can see a better way to solve my original problem, I'm
all ears. Thanks for the help.

 

[John Vinson]

Again, if you can see a better way to solve my original problem, I'm
all ears. Thanks for the help.

Well, yes, there is a better way: properly normalizing your tables.

The first step is to identify the "Entities" - real-life persons,
things, or events - of importance to your application. Each type of
Entity should get its own table, and that table should contain
information about that Entity - and NOTHING ELSE.

In your case, I can see the need for Students, Courses, and Textbooks;
may well be some more. The Students table should have the Student's
last and first names, contact information, etc. - but NOTHING about
courses. Similarly the Courses table should have the name of the
course, the instructor's name (or, better, a link to an Instructors
table) - but NO textbook information or student information.

For the multiple many-to-many relationships here you'll need some
"resolver" tables related one-to-many to each "one" side. For
textbooks, for example, you'ld have a CourseText table with a field
for the CourseID, and a field for the TextbookID; if a course needs
three texts, there would be three records in this table (NOT three
fields!)

Off the top of my head, consider a structure like:

Students
StudentID Primary Key <autonumber or manually maintained>
LastName
FirstName
FamilyID
<any other needed biographical info>

Families
FamilyID
FamilyName <e.g. "The Robertson Family", for addressing>
Address
City
State
PostCode
HomePhone
WorkPhone <<< or you may want a Phones table with FamilyID

Courses
CourseNumber Primary Key, unique manually assigned course
CourseName

Faculty
TeacherID
LastName
FirstName
<contact information, e.g. phone extension>

Enrollment
StudentID << link to Students
CourseNumber << link to Courses

Textbooks
TextbookID Primary Key, ISBN or other unique ID
Title

CourseText
CourseNumber
TextbookID


This isn't complete or all worked out - but I think you'll find that
it's an improvement over your current partially "spreadsheet" design!

John W. Vinson[MVP]

 

[Redbeard]

Thanks for your advice. Actually, seeing this makes me feel pretty
good. When I first sat down and designed this project, I set it up
almost exactly as you suggest - with the obvious exception of the
textbook field issue. I was forced to make some adjustments because my
student data, teacher data and enrollment data comes from an outside
source that basically combines those three tables into one. I don't
have control of how that data is sent, so I worked around it. Maybe I
need to look into creating some Make-Table queries that can split out
the data into separate tables so I can go with what you are suggesting.

My big worry is that I wonder if I can still create data entry forms
the way that I wanted them. Right now, the data entry form
automatically comes up with combo boxes for each textbook that the
course uses next to each student's name. The teacher then merely has
to select the textbook ID number to assign the text to the student. If
I don't make it ultra-easy for the teachers to enter the data, it won't
get used and there won't be any reason to have it.

This isn't complete or all worked out - but I think you'll find that
it's an improvement over your current partially "spreadsheet" design!

Actually, as far as data entry is concerned, a spreadsheet-like design
is what I was aiming for. That is what we use now, but it is
overwhelming with all of the students and some teachers just don't
"get" how to use filters. My first goal was to use the forms in Access
to simplify data entry and to protect the data from computer illiterate
users.