Presenting selected fields in a report

[Leathem]

I have a table which contains the choices students have made from a list of 40 courses. Each record consists of a student name followed by 40 fields, each designated by a course code. Each of these 40 fields in the record contains either a preference number from 1 to 6 or a blank. I'd like to make a report which will show the courses chosen by the student. The brute force method would be to have 40 fields on the report, most of which would be blank, but there has to be a more elegant solution in which only those courses containing a number would be shown. Is it possible and how can I do this?

On a related issue, is there a way to substitute on the report the actual name of the course rather than the code?

 

[jzwp22]

Before you go spending a lot of time on reports, you need to fix your table structure. Having 40 fields that will each hold a course code is an example of repeating groups which violates normalization rules. Please see this site for more on normalization. WHat would happen if you had to add a 41st course? You would have to add a new field to your table and modify every form, query and report that references that table (yikes)!

Since a student can have many course preferences, that describes a one(student)-to-many(course preferences) relationship. You also have another relationship is that a course can be chosen by many students (another one-to-many relationship). When you have 2 one-to-many relationships between the same two entities, you need 3 tables.

First, a table to hold the info about the people/students

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

Second, a table to hold all available courses. Each course is a record in the table, not a field

tblCourses
-pkCourseID primary key, autonumber
-CourseNumber
-txtCourseName

Now a table to join the student with their choice of courses; each student will have six associated records

tblPeopleCourseChoice
-pkPeopleCourseChoiceID primary key, autonumber
-fkPeopleID foreign key relating to tblPeople
-fkCourseID foreign key relating to tblCourses
-rankfield (a field to hold a number; the value of the number should be between 1 and 6 per you post)

 

[Leathem]

Thanks for the quick reply. Clearly I have some learning to do. It'll take some time. Back later. Oh, one thing, though: the table People is actually outside the database and accessed via a link to the original Excel file. When I tried call the first column a primary key I wasn't allowed to. Then when I tried to link to other tables the type of link came up as "undefined".

 

[jzwp22]

Since you are using a linked table, you won't be able to use the same structure as I proposed. Access will see the Excel spreadsheet as just another table. In most cases, an Excel spreadsheet does not present data in a normalized fashion. So, you will need to determine whether to migrate all your data into an appropriate relational database structure or keep the Excel file as is and try to manage using it in Access.

 

[Leathem]

Ok, I migrated the external table into my database and successfully made and linked the tables as per your suggestion. But now how do I enter the data? I used to use a form which used text boxes linked to fields in the large enrollments table, one text box for each of the course fields and one for the student name, which was drawn from a combo box using the master list of students as its source. Now if I set the Record Source for the form to the StudentCourseChoice table I'm only given the option for linking each entry text box to Preference, but can't distinguish between courses.

 

[jzwp22]

You will have to migrate any current data into the new table structure. You will need some append queries. You will need to probably create an append query for each course field in your original table. If you need help, you'll need to provide more info on your table and field names of the existing tables as well as the new tables (unless you have set them up exactly as I showed in my example).

 

[Leathem]

Well, I set up the three tables more or less as you suggested. MemberList (your "tblPeople") has an autonumber primary key (StudentID) followed by names and some other relevant data on the students. CourseData (your "tblCourses") has a CourseID (an autonumber primary key) followed by the CourseCode and some other relevant data (times, location, etc); the table StudentCourseChoices (your "tblPeopleCourseChoice") has four fields: CourseChoiceID (the primary key), StudentID, CourseID and Preference. I made a one-to-many link between StudentID in the MemberList table and the StudentID field in the StudentCourseChoices table, and a one-to-many link between CourseID in the CourseData table and CourseID in the StudentCourseChoices table.

The old Enrollments table had one field for the student name and fourty fields for the courses ("AL1", "AL2"...) which contained the priority number that the student had given for that course. Thus for any given record in the table there were at most six data entries plus the name.

I don't need to migrate the old data: it's a new semester. But I do need to know how to use my form (or a new one if need be) to enter the data (names and associated course choices) in the new table. I'll need to be able to make a report for each course listing names by priority.

 

[jzwp22]

...it's a new semester.

So you are going to be doing this each semester? You will need to incorporate that and any other requirements into your table structure before worrying about forms and reports. The table structure is absolutely critical for a successful database application, so that has to be done first!

So what are your other requirements for this application?

 

[Leathem]

As to requirements:
a data enty form which uses the names of students drawn from a large list of students (which has names, but also addresses, etc.) and allows the user to choose a student and then enter the preference number given by that student for each of up to six courses. My form now lists the codes for all courses, with text boxes next to each course for the user to enter the preference number, if any. (The list of course codes doesn't change much from semester to semester, only the names of the courses and other information such as location, time, etc.)
A report for each class listing the students who have opted for that course grouped by the preference number and randomized within each group. This is used to cull the list if there are too many students opting for a class.
A final report listing all the students in a course along with some additional data on each student. This report includes the course information as well.
A report made for each student listing the courses (as few as one or as many as six) in which they have been enrolled. (This is the one that has been giving me trouble.) It should include the name of the course as well as additional data such as location and time.

 

[jzwp22]

What you describe are the functional requirements of the database with respect to the user. We need to get the requirements of the application with respect to the data in order to construct the tables properly. I was able to get the following from your post:

Student information (this will be a table)
Courses (this will be a table)

The list of course codes doesn't change much from semester to semester, only the names of the courses and other information such as location, time, etc.)

The above statement has some key requirements and helps flush out some relationships & generates some new questions.

The name associated with course code can change. Do you need to keep a history of those name changes over time?

A course can be offered at different locations/times from one semester to another.

We will need a table to hold the possible locations.
We will need a table to hold the semester designations.

We will need a table to associate the course/location/time relative to the semester.

Can a course be offered at many locations/times during 1 semester?

We need to associate the combination of course/location/time/semester to the student.

You basically have the students requesting classes, but they are not officially part of their new schedule until all details are worked out (i.e.... is used to cull the list if there are too many students opting for a class.)

You'll need some way of distinguishing a requested course from a granted/enrolled course.

Those are just some of my initial thoughts.

 

[Leathem]

I don't need to keep a history of name changes associated with codes from one semester to the next.
You are correct that a course code can refer to different courses with different times and locations at different semesters, but during a given semester the name, time and location associated with a code will not change. There will be only one time (day, start time) and one location associated with each class.
As to distinguishing between requests and actual enrollment, it seems the easiest way is to go back to the original data entry form and enter a zero or an null value for the preference, so that subsequent queries and reports would simply not pick up that student.

 

[jzwp22]

As to distinguishing between requests and actual enrollment, it seems the easiest way is to go back to the original data entry form and enter a zero or an null value for the preference, so that subsequent queries and reports would simply not pick up that student.

Actually it might be better to have a separate field (perhaps a yes/no field) and you would mark it to yes to signify that the student is enrolled. That would be very easy to then reference in a query/reports.

I think you have enough to start laying out your tables (as long as you have read up on normalization). I would not even bother with forms, queries or reports until you have fully laid out the table structure.

 

[Leathem]

Ok, now I'm really frustrated. I've spent the last few days restructuring the database and applying the normalization rules. None of it gets me where I need to be. Basically all I need is some kind of query that can do what I can do so easily by eye: work down the records in a table, looking across each record and reporting out only those fields in the record that have a number in them, ignoring those fields that are empty. How can this be so difficult?!

 

[jzwp22]

For each student you would only enter the courses for which they had a preference, you would not add all 40 some odd courses to each student. All the records in the StudentCourseChoices table would have their preference number.