Question multi-value field disrupting forms and queries

[cs1016]

Hello,

A bit of background:
I run a database of students across 2 programs. We track which semester & which program they're part of, but we need returning students' information to display across both programs and multiple semesters with information accurate to each semester (ie test scores and attendance need to show up in the correct semester)

I changed the "program" and "semester" columns from either/or dropdown boxes to multiple checkboxes (ie, being able to select "spring 2016" AND "fall 2016") so that we don't have to make duplicate entries for returning students.

Now when I try to open the relevant forms and queries I get messages that multi-value fields cannot be used in an ORDER BY clause, or that they can't be used in a WHERE or HAVING clause.

Most of the forms and queries are pre-set to sort by program and semester, so I thought these would continue to function normally.

Any suggestions for how to update my forms and queries without having to start from scratch? Is there a better way to track this data?

 

[The_Doc_Man]

Avoiding duplicate manual entries is a good goal, but using multi-value fields as storage methods sometimes lead to bad situations - and you found one. What has happened, in essence, is that you created "invisible infrastructure" under a table but didn't tell the reports and forms about it.

Any infrastructure change necessitates a revisitation of every other database element that uses that infrastructure. There is no cheap way to do this other than to undo what you did and then go back at treat the new features explicitly. Add the tables needed to support your options, then add code/controls/relationships so that your reports and forms know how to use the new data you are providing.

As you have seen, treating the extra data implicitly with multi-value fields has dire consequences on things that rely on the old data layout.

 

[cs1016]

Thanks for this.

Do you think there's a way to track returning students without creating duplicate entries or using multi-value fields? Trying to find a solution that doesn't have these cascading errors has been difficult...

 

[The_Doc_Man]

This is an issue in normalization. The student and the student's attendance/enrollment must be in different tables with the "student" table as the parent and the "enrollment" entry being the child in a parent/child (more formally, a one-to-many) relationship.

Then you duplicate the enrollment as it occurs but only update student data as needed. Keeping the data as you described it is what we call "flat-file" thinking, which usually occurs for persons taking an Excel solution and trying to upgrade it to Access.

If you are in doubt about the path I just described, you need to read up on the topic of "database normalization." A web search for "normalization" will also turn up articles on mathematical normalization and political or diplomatic normalization, so I would be specific if doing a web search. If you want to read a few articles. look carefully at the source of each reference. The best references will be college sites and vendor sites, but the Access World forums and Wikipedia also have good articles that might bring you into a more "relational" perspective.