question

CurtisBryant-6297 avatar image
0 Votes"
CurtisBryant-6297 asked ·

MS Access 2016: Assign a single label out of many values

My data is college course sections that have many course-type codes. The goal is to give each section a single label that tells whether the course is taught online (ONLN), face-to-face (F2F), blended (BLL), or remote (REM). Most course type codes are not relevant to how the course is taught but serve other purposes.

In the sample data below, you can see that the course section ENG_101_WW has the course type ONLN. That means it's taught online and should be labeled "Online."

ENG_101_1A has the code "BLL" and should be labeled "Blended."
ENG_101_2B has "REM" and should be labeled "Remote."
Finally, ENG_101_3C has none of these codes. That means it's taught in the default, face-to-face manner and should get the "F2F" label.

Course Section Course Type
ENG_101_WW ONLN
ENG_101_WW XPF
ENG_101_WW DGJ
ENG_101_WW EDF
ENG_101_WW YKOP
ENG_101_1A BLL
ENG_101_1A XPF
ENG_101_1A EDF
ENG_101_2B REM
ENG_101_2B XPF
ENG_101_2B YKOP
ENG_101_3C XPF
ENG_101_3C EDF
ENG_101_3C YKOP
ENG_101_3C DGJ

My desired result is:
ENG_101_WW Online
ENG_101_1A Blended
ENG_101_2B Remote
ENG_101_3C F2F

What I've been doing is passing each course section ID (e.g. ENG_101_WW) to a VBA function that uses a DAO recordset to query all its course type codes, loop through the codes, and assign the appropriate label (Online, Blended, Remote, and F2F when none of the relevant codes are found). While this works, it's painfully slow, and I'm hoping you can suggest a more efficient way to get the desired result.

What would you recommend?


office-access-dev
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

CurtisBryant-6297 avatar image
0 Votes"
CurtisBryant-6297 answered ·

It turned out that the reason it was slow is that the VBA function was being run for every combination of course and course type code, when it really only needs to run on a per-course basis. When I put the unique course section IDs in a table and queried that, the function ran lickety split.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.