I imagine a db copy would answer a whole lot of questions. As I follow along in this thread, I can't help but wonder about something. To take DB Guy's notion a bit further, it would appear that there is not much interest in building a screwdriver for driving this screw because a hammer seems just fine. I'd bet money that the classic Excel to Access migration errors and thinking is at play. Be that as it may, I also don't see any way around making each calculated field call the function by manually writing an alias and function call that passes the field name itself. There is no other way, AFAIC. The fact that it has to be done in several fields just to retrieve different values of the same basic thing means Excelitis is at play here. As soon as some other measure gets added to the mix, the query has to have a field added, and we all know what that usually points to.
Thank you for those observations, I appreciate the engagement.
I don't think I'm at liberty to post a db copy, sorry.
I inherited this application so I don't know whether it started life as an Excel worksheet and got migrated to Access.
You're quite right though to say that I'm bringing an Excel perspective to it. Right down at the detail level, passing the field name (column heading) to a called function would of course present no difficulty in Excel.
I absolutely take the point of the hammer/screwdriver analogy but I think in this case it's a bit of a caricature given that (it seems to me) the single-table design will still be quite fit for purpose.